关于数据事实表汇总的模拟实现——脚本实现

本文主要是针对数据仓库中的事实表汇总或者聚集进行模拟实现的

实现的环境：

数据仓库数据库：SQLServer2000

创建表脚本

CREATE TABLE t_fact_RunStatus ( factProcName VARCHAR(40), factDesc VARCHAR(100), factType INT, LastLogTime DATETIME, BeginTime DATETIME, EndTime DATETIME, Status VARCHAR(20), LogLimit INT, Step INT, CancelFlag INT, CurTime DATETIME ) GO CREATE TABLE t_org_table ( LogDate DATETIME, Dim1 INT, Dim2 INT, Value1 NUMERIC(10,0), Value2 NUMERIC(10,0) ) GO CREATE TABLE t_fact_table ( LogDate DATETIME, Dim1 INT, Dim2 INT, Measure1 NUMERIC(10,0), Measure2 NUMERIC(10,0) ) GO

创建存储过程脚本

CREATE PROCEDURE p_fact_xxx AS DECLARE @step INTEGER --获取本次处理距离当前最大时间间隔 DECLARE @LogLimit INTEGER --获取本次处理最大时间间隔 DECLARE @BeginTime DATETIME --获取本次处理开始时间 DECLARE @EndTime DATETIME --获取本次处理结束时间 DECLARE @Num INTEGER --获取是否存在此次任务 DECLARE @TimeTemp VARCHAR(20) --时间临时中间变量 DECLARE @StatSecTime INTEGER --每次处理的步增情况，默认为1，即1小时 DECLARE @LastLogTime DATETIME --每次处理的当前时间点 DECLARE @Status VARCHAR(20) --本次处理的状态，EXCEPTION和FINISHED两种情况 DECLARE @CancelFlag INTEGER --取消情况，1和0 BEGIN SELECT @StatSecTime = 1 --获取上次处理的最后时间，状态和取消情况 SELECT @LastLogTime = LastLogTime,@Status = Status,@CancelFlag = CancelFlag FROM t_fact_RunStatus WHERE UPPER(factProcName) = UPPER('p_fact_table') --假如为取消该任务，则直接退出 IF @CancelFlag = 1 OR @CancelFlag IS NULL RETURN --判断是否存在此次任务 SELECT @Num = COUNT(factProcName) FROM t_fact_RunStatus WHERE UPPER(factProcName) = UPPER('p_fact_table') --存在更新任务状态，没有则新增一个任务 IF @Num = 1 UPDATE t_fact_RunStatus set Status = 'RUNNING',CurTime=GETDATE() WHERE UPPER(factProcName) = UPPER('p_fact_table') ELSE BEGIN IF @Num > 1 DELETE FROM t_fact_RunStatus WHERE UPPER(factProcName) = UPPER('p_fact_table') INSERT INTO t_fact_RunStatus(factProcName, factDesc, factType,Status, LogLimit, Step, CancelFlag,CurTime) VALUES(UPPER('p_fact_table'), 'XXXXXX', 60,'RUNNING', 24, 120, 0,GETDATE()) END --获取本次任务开始的开始时间和结束时间 BEGIN --如果任务没有正常终结，则从事实表中获取 --否则获取通过配置表获取，不过此处存在一定风险，既没有考虑到事实上的时间盲点 IF @Status != 'FINISHED' SELECT @BeginTime = DATEADD(hh,@StatSecTime,max(LogDate)) FROM t_fact_table ELSE SELECT @BeginTime = DATEADD(hh,@StatSecTime,@LastLogTime) IF @BeginTime IS NULL SELECT @BeginTime = MIN(LogDate) FROM t_org_table IF @BeginTime IS NULL BEGIN UPDATE t_fact_RunStatus SET Status = 'EXCEPTION' WHERE UPPER(factProcName) = UPPER('p_fact_table') RETURN END --时间取整，此处为整点小时 SELECT @TimeTemp = CONVERT(VARCHAR, @BeginTime,112) SELECT @TimeTemp = @TimeTemp + ' ' + DATENAME(hour, @BeginTime) SELECT @TimeTemp = @TimeTemp + ':' + '00:00' SELECT @BeginTime = CONVERT(DATETIME, @TimeTemp) --获取距离当前时间最大间隔和当前处理的最大间隔 SELECT @Step = ISNULL(Step, 120),@LogLimit = ISNULL(LogLimit, 24) FROM t_fact_RunStatus WHERE UPPER(factProcName) = UPPER('p_fact_table') SELECT @EndTime = DATEADD(minute, -@step, getdate()) --如果要处理的时间间隔大于指定间隔，则从原始记录表中获取开始时间和结束时间 --否则直接对结束时间进行截取 IF DATEDIFF(hour, @BeginTime, @EndTime) > @LogLimit BEGIN SELECT @BeginTime = MIN(LogDate) FROM t_org_table WHERE LogDate >= @BeginTime SELECT @TimeTemp = CONVERT(VARCHAR, @BeginTime,112) SELECT @TimeTemp = @TimeTemp + ' ' + DATENAME(hour, @BeginTime) SELECT @TimeTemp = @TimeTemp + ':' + '00:00' SELECT @BeginTime = CONVERT(DATETIME, @TimeTemp) SELECT @EndTime = DATEADD(HOUR, @LogLimit, @BeginTime) END ELSE BEGIN SELECT @TimeTemp = CONVERT(VARCHAR, @EndTime,112) SELECT @TimeTemp = @TimeTemp + ' ' + DATENAME(hour, @EndTime) SELECT @TimeTemp = @TimeTemp + ':' + '00:00' SELECT @EndTime = CONVERT(DATETIME, @TimeTemp) END END --更新开始和结束时间 UPDATE t_fact_RunStatus SET BeginTime = @BeginTime, EndTime = @EndTime WHERE UPPER(factProcName) = UPPER('p_fact_table') --为了减少系统压力，循环进行数据处理 WHILE @BeginTime < @EndTime BEGIN BEGIN TRAN Tran_Fact INSERT INTO t_fact_table ( LogDate, Dim1 , Dim2 , Measure1 , Measure2 ) SELECT @BeginTime, Dim1, Dim2, SUM(Value1), SUM(Value2) FROM t_org_table a WHERE LogDate >= @BeginTime AND LogDate < DATEADD(hour,@StatSecTime, @BeginTime) GROUP BY Dim1,Dim2 IF @@ERROR <> 0 GOTO FAIL --每次处理完，必须更新当前的时间标志 UPDATE t_fact_RunStatus SET LastLogTime=@BeginTime, CurTime = GETDATE(), Status = 'FINISHED' WHERE UPPER(factProcName) = UPPER('p_fact_table') COMMIT TRAN Tran_Fact SELECT @BeginTime = DATEADD(hour, @StatSecTime, @BeginTime) END RETURN FAIL: ROLLBACK TRAN Tran_Fact UPDATE t_fact_RunStatus SET Status = 'EXCEPTION', LastLogTime=@BeginTime WHERE UPPER(factProcName) = UPPER('p_fact_table') RETURN 0 END

创建模拟数据脚本

--创建测试数据 DECLARE @i INT SELECT @i = 1 WHILE @i<=365 BEGIN INSERT INTO t_org_table(LogDate,Dim1,Dim2,Value1,Value2) VALUES(DATEADD(hour,24*(@i-1),CONVERT(DATETIME,'2006-01-01')),@i,@i,@i,@i); INSERT INTO t_org_table(LogDate,Dim1,Dim2,Value1,Value2) VALUES(DATEADD(hour,24*(@i-1),CONVERT(DATETIME,'2006-01-01 12:00:00')),@i,@i,@i,@i); SELECT @i = @i+1 END

调用脚本

EXEC p_fact_xxx SELECT * FROM dbo.t_fact_RunStatus SELECT * FROM dbo.t_fact_table

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6517/viewspace-145460/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6517/viewspace-145460/