[SQL].備份和加總比較舊的不重要的Log

user 有一個需求, 希望可以看到每一個商品, 在每一個小時的流量, 所以log table 長大的速度會很快, 以"小時"為單位, 和以"日"為單為 log 的筆數會差到 24倍.

今天一早, 有一個營運中的網站掛掉, 剛好掛到去 log 取資料, 挑戰改善 log table 的資料筆數, 使用到的 SQL 指令如下:


--// 查詢, 目前的資料量.
select count(*) from DemoHitCount;


--// create 2009 log backup table.
CREATE TABLE [dbo].[DemoHitCountLogBak_2009](
[DOC_ID] [int] NOT NULL,
[hitDate] [smalldatetime] NOT NULL,
[hitCount] [int] NOT NULL
);

insert into DemoHitCountLogBak_2009
select DOC_ID, hitDate, hitCount from DemoHitCount
where year(hitDate)=2009;


--// create 2010 log backup table.
CREATE TABLE [dbo].[DemoHitCountLogBak_2010](
[DOC_ID] [int] NOT NULL,
[hitDate] [smalldatetime] NOT NULL,
[hitCount] [int] NOT NULL
);

insert into DemoHitCountLogBak_2010
select DOC_ID, hitDate, hitCount from DemoHitCount
where year(hitDate)=2010;


--// create temp table.
CREATE TABLE [dbo].[DemoHitCount_tmp](
[DOC_ID] [int] NOT NULL,
[hitDate] [smalldatetime] NOT NULL,
[hitCount] [int] NOT NULL CONSTRAINT [DF_DemoHitCount_hitCount_tmp] DEFAULT ((0)),
CONSTRAINT [PK_DemoHitCount_tmp] PRIMARY KEY CLUSTERED
(
[DOC_ID] ASC,
[hitDate] ASC
)
) ON [PRIMARY]


--// summary 2009 data.
insert into DemoHitCount_tmp
select DOC_ID, '2009/12/31' as hitDate, sum(hitCount) as hitCount from DemoHitCount
where year(hitDate)=2009
group by DOC_ID;


--// summary 2010 data.
insert into DemoHitCount_tmp
select DOC_ID, '2010/12/31' as hitDate, sum(hitCount) as hitCount from DemoHitCount
where year(hitDate)=2010
group by DOC_ID;


--// delete current log
delete from DemoHitCount
where year(hitDate)=2009;

delete from DemoHitCount
where year(hitDate)=2010;

insert into DemoHitCount
select DOC_ID, hitDate, hitCount from DemoHitCount_tmp;


--// drop temp table.
drop table DemoHitCount_tmp;


--// 測試加總的結果:
SELECT sum(hitCount) AS hCount FROM DemoHitCount WHERE DOC_ID=1



附註1: 備份 log用的 table, 由於只是放著之後備查用, 所以建議不要建 index, 因為 index 在大量筆數的情況, 是很占空間的.

附註2: 在測試機改好後, 連到客戶的主機上執行, 才發現是客戶的database 系統發生異常.

附註3: 應該有更好的解法, 我是遜卡, 什麼證照都沒有.