本文主要是針對資料倉儲中的事實表彙總或者聚集進行模擬實現的

實現的環境：

資料倉儲資料庫：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

關於資料事實表彙總的模擬實現——指令碼實現

相關文章