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

原创于2007年01月08日，2009年10月15日迁移至此。

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

实现的环境：

数据仓库数据库：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