吴乙己的数仓指南

• • 0.引言
  • 1.SCD类型
  • 2.举个栗子
  • 3.SCD2
  • • 3.1 什么是SCD2
    • 3.2 如何使用SCD2
    • 3.3 如何实现SCD2
    • • 3.3.1 获取维度基准
      • 3.3.2 按情况分治打标
      • 3.3.3更新维度表

缓慢变化维技术（Slow Changing Dimension，简称SCD）是维度建模中几乎无时无刻不在使用的技术，它为维度提供了基于历史数据进行切片、切块的能力。

以kimball老爷子的理论，共有7种SCD类型，但在日常的数仓开发中，最常使用的是SCD2。所谓SCD2类型，即在维度表中增加新行记录最新维度。

假设我们有一张地域维度表，记录了行政区号、地名信息。建表语句如下：

--基于HQL方言
CREATE TABLE DIM_ADD(
ADD_NO STRING COMMENT '地域编码',
ADD_NAME STRING COMMENT '地域名称'
)

其中有一行数据：

我们知道，在100年前，北京还不叫“北京”，叫做“北平”，如果我们要基于100年前的数据进行分析，那彼时的地名字段应写做“北平”。可是我们的维表中没有“北平”，该怎么办呢？

为了解决第二节的问题，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 '结束时间'
)

接着，北平的数据就可以维护进来

我们可以看到，在SCD2类型中，维护了FROM_DATE和TO_DATE，代表了起始时间、结束时间，业务时间落在其中时就叫生效时间段。

注意
1.自然键相同的维度值，各行之起止时间跨度，不得有交集，从业务上讲不合逻辑，同时会导致数据翻倍。
2.最新的数据，结束时间写“9999-12-31”即可。
3.若基于HQL开发，起始时间、结束时间可直接使用STRING类型，与STRING、DATE类型变量都可以直接进行比较。

在使用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 '入籍时间'
)

数据示例：

那么需求指标计算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.2节中的DIM_ADD_SCD2为例，要对其进行更新，更新的依据是通过ETL获取的最新地域维度信息表——DIM_ADD_NEW。

其建表语句如下：

--基于HQL方言
CREATE TABLE DIM_ADD_NEW(
ADD_NO STRING COMMENT '地域编码',
ADD_NAME STRING COMMENT '地域名称'
)

设其中数据如下：

我们对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代码的撰写：

设维表有数据如下：

获取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'
 ;

处理过后数据如下：

对 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有数据：

本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

新维表中数据如下：

至此，一轮SCD2类型的维表更新完毕。

以上就是本章内容，相信大家已经对SCD2类型是什么，如何使用，如何搭建有了清晰的认识。

下一章讲解蜈蚣事实表的归约优化，敬请期待。

【版权所有，翻版必究。】