SCD緩慢變化維拉鏈表SQL實作
1 緩慢變化維概述
SCD英文Slow Changing Dimensions(SCD 緩慢變化維)，它是資料倉庫建模程序中一個非常重要的概念，眾所周知資料倉庫是基于歷史資料的，而歷史資料的變化依賴于維度的定義，緩慢變化維就是用來跟蹤和表現維度表變化的一種方法，
注: 1 當前代碼演示環境是SQL Server，基于Merge語法，其它資料庫類似，
2 SCD緩慢變換維Kettle實作
常見有3中分別記作Type1、Type2、Type3，這里假如我們有用戶維度表customer客戶(cust_id用戶編號、name姓名、age年齡)，如果在某個時間將某個用戶的age更新成40，我們看下三種維度處理的方法的情況，
- Type1(不會記錄維度里關鍵欄位值的變化歷史情況)：
cust_id name age
1 張三 30
新的customer表為:
cust_id name age
1 張三 40
- Type2：
針對1的情況則，這里假設更新時間是2020-10-21
cust_id name age start_date end_date is_current
1 張三 30 2020-10-10 2020-10-21 0
1 張三 40 2020-10-21 9999-10-21 1
- Type3
直接記錄當前最新的值和上一次變化前的值
cust_id name age pre_age
1 張三 30 40
綜上所述不難發現type1和type2不能很好的記錄維度的變化情況，type1沒有，type3只能記錄最新的一次變化，
2 代碼與注釋
2.1 表結構與資料
-- step1 準備表和資料，當前運行在SQL Server里，
-- 業務系統(OLTP)的客戶表
CREATE TABLE Customer(
ID int IDENTITY(1,1) NOT NULL,
FullName nvarchar(50) NULL,
City nvarchar(50) NULL,
Occupation nvarchar(50) NULL)
-- 資料倉庫的(OLAP)的客戶維度表
CREATE TABLE DimCustomer(
CustomerID int IDENTITY(1,1) NOT NULL,
CustomerAlternateKey int NULL,
FullName nvarchar(50) NULL,
City nvarchar(50) NULL,
Occupation nvarchar(50) NULL,
StartDate datetime NULL,
EndDate datetime NULL,
IsCurrent bit NULL,
PRIMARY KEY CLUSTERED
(
CustomerID ASC
)
)
GO
ALTER TABLE DimCustomer ADD DEFAULT ((1)) FOR IsCurrent
INSERT INTO Customer(FullName,City,Occupation)
SELECT 'BIWORK','Beijing','CEO' UNION ALL
SELECT 'ZhangSan','Shanghai','Education' UNION ALL
SELECT 'Lisi','Guangzhou','IT' UNION ALL
SELECT 'Wangwu','Beijing','Finance'
2.2 緩慢變換維代碼
-- step2 SCD 模塊
-- 1 修改狀態
MERGE INTO dbo.DimCustomer AS Dim
USING dbo.Customer AS Src
ON Dim.CustomerAlternateKey = Src.ID
WHEN NOT MATCHED BY TARGET
THEN INSERT VALUES(Src.ID,Src.FullName,Src.City,Src.Occupation,GETDATE(),NULL,1)
WHEN MATCHED AND (Dim.City <> Src.City OR Dim.Occupation <> Src.Occupation) AND Dim.IsCurrent=1
THEN UPDATE SET Dim.EndDate =CASE WHEN Dim.EndDate IS NULL THEN GETDATE() ELSE Dim.EndDate END,Dim.IsCurrent = 0;
-- 2 修改資料
MERGE INTO dbo.DimCustomer AS Dim
USING dbo.Customer AS Src
ON Dim.CustomerAlternateKey = Src.ID
AND Dim.City = Src.City AND Dim.Occupation = Src.Occupation
WHEN NOT MATCHED BY TARGET
THEN INSERT VALUES(Src.ID,Src.FullName,Src.City,Src.Occupation,getDATE(),NULL,1);
2.3 修改資料驗證
-- Step3 驗證
-- 新插入一條
INSERT INTO Customer(FullName,City,Occupation) VALUES
('qinliu','Beijing','Finance')
-- Case1: 執行如下更新后執行SCD模塊,這里的ID依賴于自增序列生成的序號
UPDATE Customer
SET Occupation = 'IT'
WHERE ID = 6
-- Case2: 執行如下更新后執行SCD模塊,這里的ID依賴于自增序列生成的序號
UPDATE Customer
SET Occupation = 'Publisher',
City = 'Hangzhou'
WHERE ID = 6
-- 每次修改后對照查看DimCustomer表的變化，查看是否追蹤到資料的歷史變更資訊，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/263898.html
標籤：其他
上一篇：寫SQL經驗，多次加班教訓總結
下一篇：資料庫事務詳解