0.引言
缓慢变化维技术（Slow Changing Dimension，简称SCD）是维度建模中几乎无时无刻不在使用的技术，它为维度提供了基于历史数据进行切片、切块的能力。
1.SCD类型
以kimball老爷子的理论，共有7种SCD类型，但在日常的数仓开发中，最常使用的是SCD2。所谓SCD2类型，即在维度表中增加新行记录最新维度。
2.举个栗子
假设我们有一张地域维度表，记录了行政区号、地名信息。建表语句如下：
--基于HQL方言
CREATE TABLE DIM_ADD(
ADD_NO STRING COMMENT '地域编码',
ADD_NAME STRING COMMENT '地域名称'
)
其中有一行数据：
| ADD_NO | ADD_NAME |
|---|---|
| 010 | 北京 |
我们知道，在100年前，北京还不叫“北京”，叫做“北平”，如果我们要基于100年前的数据进行分析，那彼时的地名字段应写做“北平”。可是我们的维表中没有“北平”，该怎么办呢？
3.SCD2
3.1 什么是SCD2
为了解决第二节的问题，SCD2就是一种非常优雅的办法。
SCD2，Kimball老爷子只总结了两个字——“新增”。
说白了，就是在维度表中保留历史数据，新增最新数据，并以数据的生效时间段来约束数据的有效性。
一张简单的SCD2类型维度表建表语句如下：
--基于HQL方言
CREATE TABLE DIM_ADD_SCD2(
ADD_NO STRING COMMENT '地域编码',
ADD_NAME STRING COMMENT '地域名称',
FROM_DATE STRING COMMENT '起始时间',
TO_DATE STRING COMMENT '结束时间'
)
接着，北平的数据就可以维护进来
| ADD_NO | ADD_NAME | FROM_DATE | TO_DATE |
|---|---|---|---|
| 010 | 北平 | 1368-09-12 | 1947-09-27 |
| 010 | 北京 | 1947-09-28 | 9999-12-31 |
我们可以看到，在SCD2类型中，维护了FROM_DATE和TO_DATE，代表了起始时间、结束时间，业务时间落在其中时就叫生效时间段。
注意
1.自然键相同的维度值，各行之起止时间跨度，不得有交集，从业务上讲不合逻辑，同时会导致数据翻倍。
2.最新的数据，结束时间写“9999-12-31”即可。
3.若基于HQL开发，起始时间、结束时间可直接使用STRING类型，与STRING、DATE类型变量都可以直接进行比较。
3.2 如何使用SCD2
在使用SCD2类型的维度表时也非常简单，只需在WHERE条件或JOIN条件中使用业务时间去限制起始时间、结束时间即可。
如现在有一分析需求，欲从户口事实表中计算1942年各个城市登记入籍的人数。
户口事实表建表语句如下：
--基于HQL方言
CREATE TABLE FACT_HOUSEHOLD(
HOUSEHOLD_NO STRING COMMENT '户口编码',
ADD_NO STRING COMMENT '地域编码',
HOUSEHOLD_CNT INT COMMENT '在籍人数',
HOUSEHOLD_RECORD_DATE STRING COMMENT '入籍时间'
)
数据示例：
| HOUSEHOLD_NO | ADD_NO | HOUSEHOLD_CNT | HOUSEHOLD_RECORD_DAY |
|---|---|---|---|
| 0000000001 | 010 | 2 | 1937-09-27 |
| 0000000003 | 029 | 3 | 2013-09-01 |
| 0000000004 | 0533 | 3 | 2010-09-01 |
那么需求指标计算SQL为：
--基于HQL方言
SELECT T2.ADD_NAME AS ADD_NAME
,SUM(NVL(T1.HOUSEHOLD_CNT,0)) AS ADD_POPULATION
FROM FACT_HOUSEHOLD T1
JOIN DIM_ADD_SCD2 T2
ON T1.ADD_NO = T2.ADD_NO
AND SUBSTR(T2.TO_DATE,1,4) >= '1942'
AND SUBSTR(T2.TO_DATE,1,4) <= '1942'
WHERE SUBSTR(HOUSEHOLD_RECORD_DAY,1,4) = '1942'
;
--一般而言，传参会写占位符基于业务时间生成，此为示例，故写为固定值。
以上便是一个简易的demo，介绍了如何在计算指标时使用SCD2类型维度表。
3.3 如何实现SCD2
仍以3.2节中的DIM_ADD_SCD2为例，要对其进行更新，更新的依据是通过ETL获取的最新地域维度信息表——DIM_ADD_NEW。
其建表语句如下：
--基于HQL方言
CREATE TABLE DIM_ADD_NEW(
ADD_NO STRING COMMENT '地域编码',
ADD_NAME STRING COMMENT '地域名称'
)
设其中数据如下：
| ADD_NO | ADD_NAME |
|---|---|
| 010 | 北京 |
| 002 | A3 |
| 003 | B1 |
| 004 | C1 |
我们对DIM_ADD_NEW中的数据分而治之：
情况一，在自然键对应时，DIM_ADD_NEW中与DIM_ADD_SCD2最新一条数据无差异，则不做操作。
情况二，在自然键对应时，DIM_ADD_NEW中与DIM_ADD_SCD2最新一条数据有差异，则将此自然键DIM_ADD_SCD2中最新一条数据结束时间设置为业务时间（或业务时间-1d，视具体业务而定），并插入最新维度数据，起始时间设置为业务时间+1d（或业务时间，视具体业务而定），结束时间设置为’9999-12-31’。
情况三，之于某自然键，DIM_ADD_NEW中存在而DIM_ADD_SCD2中不存在，新增之，起始时间设置为业务时间+1d（或业务时间，视具体业务而定），结束时间设置为’9999-12-31’。
下面我们以此命题进行一个小demo代码的撰写：
3.3.1 获取维度基准
设维表有数据如下：
| ADD_NO | ADD_NAME | FROM_DATE | TO_DATE |
|---|---|---|---|
| 010 | 北平 | 1368-09-12 | 1947-09-27 |
| 010 | 北京 | 1947-09-28 | 9999-12-31 |
| 002 | A1 | 1234-01-01 | 1949-10-01 |
| 002 | A2 | 1949-10-02 | 9999-12-31 |
| 003 | B1 | 1234-01-01 | 9999-12-31 |
获取DIM_ADD_SCD2结束日期为9999-12-31的数据作为基准。
--基于HQL方言
CREATE TABLE DIM_ADD_SCD2_NEWEST AS
SELECT T1.ADD_NO
,T1.ADD_NAME
FROM DIM_ADD_SCD2 T1
WHERE T1.TO_DATE = '9999-12-31'
;
处理过后数据如下：
| ADD_NO | ADD_NAME |
|---|---|
| 010 | 北京 |
| 002 | A2 |
| 003 | B1 |
3.3.2 按情况分治打标
对 3.3 中提到的三种情况，对最新数据进行打标，打标字段命名为FLAG，0对应情况一，1对应情况2,2对应情况三。
建表语句如下：
--基于HQL方言
CREATE TABLE DIM_ADD_NEW_FLAG(
ADD_NO STRING COMMENT '地域编码',
ADD_NAME STRING COMMENT '地域名称',
FLAG STRING COMMENT '情况标识'
)
对应情况一，有代码：
--基于HQL方言
INSERT INTO TABLE DIM_ADD_NEW_FLAG
SELECT T1.ADD_NO AS ADD_NO
,T1.ADD_NAME AS ADD_NAME
,'1' AS FLAG
FROM DIM_ADD_NEW T1
JOIN DIM_ADD_SCD2_NEWEST T2 --JOIN起过滤作用
ON T1.ADD_NO = T2.ADD_NO
AND T1.ADD_NAME = T2.ADD_NAME
;
对应情况二，有代码：
--基于HQL方言
INSERT INTO TABLE DIM_ADD_NEW_FLAG
SELECT T1.ADD_NO AS ADD_NO
,T1.ADD_NAME AS ADD_NAME
,'2' AS FLAG
FROM DIM_ADD_NEWT1
LEFT JOIN DIM_ADD_SCD2_NEWEST T2
ON T1.ADD_NO = T2.ADD_NO
AND T1.ADD_NAME = T2.ADD_NAME
WHERE T2.ADD_NO IS NULL --关联不上，代表有更新数据
AND T1.ADD_NO IN (SELECT ADD_NO FROM OLD_NEWEST) --自然键需重合
;
对应情况三，有代码：
--基于HQL方言
INSERT INTO TABLE DIM_ADD_NEW_FLAG
SELECT T1.ADD_NO AS ADD_NO
,T1.ADD_NAME AS ADD_NAME
,'3' AS FLAG
FROM DIM_ADD_NEW T1
LEFT JOIN DIM_ADD_SCD2_NEWEST T2
ON T1.ADD_NO = T2.ADD_NO
AND T1.ADD_NAME = T2.ADD_NAME
WHERE T2.ADD_NO IS NULL --关联不上，代表是新数据
AND T1.ADD_NO NOT IN (SELECT ADD_NO FROM OLD_NEWEST) --自然键需不对称
;
则此时，DIM_ADD_NEW_FLAG有数据：
| ADD_NO | ADD_NAME | FLAG |
|---|---|---|
| 010 | 北京 | 1 |
| 002 | A3 | 2 |
| 003 | B1 | 1 |
| 004 | C1 | 3 |
3.3.3更新维度表
本demo基于HQL方言，故无法进行UPDATE操作，在更新时需使用如下代码：
首先，生成一个临时表，作为处理结果的载体，并以此更新维度表，建表语句与DIM_ADD_SCD2相同：
--基于HQL方言
CREATE TABLE DIM_ADD_SCD2_TMP(
ADD_NO STRING COMMENT '地域编码',
ADD_NAME STRING COMMENT '地域名称',
FROM_DATE STRING COMMENT '起始时间',
TO_DATE STRING COMMENT '结束时间'
)
对于情况1，使用自然键将DIM_ADD_SCD2 与DIM_ADD_NEW_FLAG关联，原样装载至临时表即可 ：
--基于HQL方言
INSERT INTO TABLE DIM_ADD_SCD2_TMP
SELECT T1.ADD_NO AS ADD_NO
,T1.ADD_NAME AS ADD_NAME
,T1.FROM_DATE AS FROM_DATE
,T1.TO_DATE AS TO_DATE
FROM DIM_ADD_SCD2 T1
JOIN DIM_ADD_NEW_FLAG T2
ON T1.ADD_NO = T2.ADD_NO
AND T2.FLAG = '1'
;
对于情况二，我们需要将原维度表非最新数据原样装载。最新一条数据的结束时间设为业务时间。并基于DIM_ADD_NEW_FLAG新增一条数据，开始时间为业务时间+1d，结束时间为‘9999-12-31’。
--基于HQL方言
--原样装载非最新数据
INSERT INTO TABLE DIM_ADD_SCD2_TMP
SELECT T1.ADD_NO AS ADD_NO
,T1.ADD_NAME AS ADD_NAME
,T1.FROM_DATE AS FROM_DATE
,T1.TO_DATE AS TO_DATE
FROM DIM_ADD_SCD2 T1
JOIN DIM_ADD_NEW_FLAG T2
ON T1.ADD_NO = T2.ADD_NO
AND T2.FLAG = '2'
WHERE T1.TO_DATE != '9999-12-31'
UNION ALL
--将最新一条数据至结束时间设置为业务时间
SELECT T1.ADD_NO AS ADD_NO
,T1.ADD_NAME AS ADD_NAME
,T1.FROM_DATE AS FROM_DATE
,'${business_tm}' AS TO_DATE --一般而言会传入一个业务时间，此处用占位符表示
FROM DIM_ADD_SCD2 T1
JOIN DIM_ADD_NEW_FLAG T2
ON T1.ADD_NO = T2.ADD_NO
AND T2.FLAG = '2'
WHERE T1.TO_DATE = '9999-12-31'
UNION ALL
--插入最新维度值
SELECT ADD_NO AS ADD_NO
,ADD_NAME AS ADD_NAME
,DATE_ADD('${business_tm}',1) AS FROM_DATE
,'9999-12-31' AS TO_DATE
FROM DIM_ADD_NEW_FLAG
WHERE FLAG = '2'
;
对于情况三，将新数据装载至维度表即可，有代码如下：
--基于HQL方言
INSERT INTO TABLE DIM_ADD_SCD2_TMP
SELECT ADD_NO AS ADD_NO
,ADD_NAME AS ADD_NAME
,DATE_ADD('${business_tm}',1) AS FROM_DATE
,'9999-12-31' AS TO_DATE
FROM DIM_ADD_NEW_FLAG
WHERE FLAG = '3'
最后，我们将临时表的数据覆写至维度表。
--基于HQL方言
INSERT OVERWRITE TABLE DIM_ADD_SCD2
SELECT ADD_NO AS ADD_NO
,ADD_NAME AS ADD_NAME
,'${business_tm}' AS FROM_DATE
,‘9999-12-31’ AS TO_DATE
FROM DIM_ADD_SCD2_TMP
新维表中数据如下：
| ADD_NO | ADD_NAME | FROM_DATE | TO_DATE |
|---|---|---|---|
| 010 | 北平 | 1368-09-12 | 1947-09-27 |
| 010 | 北京 | 1947-09-28 | 9999-12-31 |
| 002 | A1 | 1234-01-01 | 1949-10-01 |
| 002 | A2 | 1949-10-02 | 2021-11-20 |
| 002 | A3 | 2021-11-21 | 9999-12-31 |
| 003 | B1 | 1234-01-01 | 9999-12-31 |
| 004 | C1 | 2021-11-20 | 9999-12-31 |
至此，一轮SCD2类型的维表更新完毕。
以上就是本章内容，相信大家已经对SCD2类型是什么，如何使用，如何搭建有了清晰的认识。
下一章讲解蜈蚣事实表的归约优化，敬请期待。
【版权所有，翻版必究。】
1148
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
