如何用SQL语句处理缓慢变化维（渐变维，拉链表）SCD-2？

 

假设有一张居民维表，需要记录居民状态的变更历史，根据Kimball建模理论，设计居民维表如下：

另外在ODS中有居民信息的每日快照表（每天都记录一份居民的全量信息）：O_USERINFO

如何将ODS中的O_USERINFO的每日批次数据加载到居民维表D_RESIDENT_SCD中，需注意两个表必须在同一个数据源中。

在HHDI中新建数据抽取任务，也可采用执行SQL任务，在源表查询语句中写入以下SQL：

SELECT     O.ID RESIDENTID,
     O.NAME,
     O.IDNUMBER,
     O.SEX,
     TO_CHAR(O.BIRTHDAY, 'YYYY/MM/DD') BIRTHDAY,
     AREACODE DIVISIONCODE,
     IFNULL(O.STATUS,-1) STATUS,
     O.CREATETIME,
     O.IMAGEPATH,
     O.CONTACTADDRESS,
     TRUE DIM_FLAG,
     '${HDIBATCHNO}' DIM_STARTTIME,
     '20990101' DIM_ENDTIME
FROM O_USERINFO O
WHERE O.ETLBATCHID = ${HDIBATCHNO}
AND NOT EXISTS (SELECT 1 FROM D_RESIDENT_SCD D WHERE (D.DIM_FLAG=TRUE) AND O.ID = D.RESIDENTID AND IFNULL(O.STATUS,-1) = IFNULL(D.STATUS,-1))
AND NOT EXISTS (SELECT 1 FROM D_RESIDENT_SCD D WHERE ('${HDIBATCHNO}' >=D.DIM_STARTTIME AND '${HDIBATCHNO}' < D.DIM_ENDTIME) AND O.ID = D.RESIDENTID AND IFNULL(O.STATUS,-1) = IFNULL(D.STATUS,-1))

这段SQL的意思是筛选出新增的居民记录以及居民状态发生变化的居民信息，其中WHERE语句的最后一个条件的意思是如果重跑n天之前的历史数据时，将这段时间内的居民状态发生变化的记录（和维表比较）筛选出来。如图：

 

在目标页签中，在自定义删除语句中，写入以下SQL：

UPDATE PUBLIC.D_RESIDENT_SCD D

SET D.STATUS=(SELECT IFNULL(O.STATUS,-1) FROM O_USERINFO O WHERE O.ETLBATCHID = ${HDIBATCHNO} AND D.RESIDENTID=O.ID AND IFNULL(O.STATUS,-1) <> IFNULL(D.STATUS,-1))
WHERE D.DIM_FLAG=FALSE
AND '${HDIBATCHNO}' >=D.DIM_STARTTIME AND '${HDIBATCHNO}' < D.DIM_ENDTIME
AND EXISTS (SELECT 1 FROM O_USERINFO O WHERE O.ETLBATCHID = ${HDIBATCHNO} AND D.RESIDENTID=O.ID AND IFNULL(O.STATUS,-1) <> IFNULL(D.STATUS,-1))
;

UPDATE PUBLIC.D_RESIDENT_SCD D
SET DIM_FLAG=FALSE,DIM_ENDTIME='${HDIBATCHNO}'
WHERE D.DIM_FLAG=TRUE
AND D.DIM_STARTTIME<='${HDIBATCHNO}'
AND EXISTS (
SELECT 1 FROM O_USERINFO O
WHERE O.ETLBATCHID = ${HDIBATCHNO}
AND D.RESIDENTID=O.ID
AND IFNULL(O.STATUS,-1) <> IFNULL(D.STATUS,-1))
;

这两个SQL的意思是修改已存在的且状态发生变化的居民维度记录的有效标志和有效时间段，第一个UPDATE语句是跑历史数据时需要执行的，第二个语句是用来跑最新数据的，如图：

这样最终在执行任务的时候，先执行两个UPDATE语句，然后再执行INSERT语句将之前SELECT的数据插入到居民维表。

如果是执行SQL任务的话，就是两个UPDATE语句加一个INSERT语句即可。

数据库中该维表写入后的结果如图所示：

 

 HaoheDI（昊合数据整合平台）

是一款基于B/S架构的轻量级ETL工具，开发运维均极为简单，帮您快速搭建ETL平台，广泛支持各种数据库、文本文件、SAP和Hadoop，开发数据转换只需熟悉SQL开发即可，可轻松调度Kettle的转换和作业，并提供图形化的作业流程设计界面，以及可视化的任务作业管理、计划调度、实时监控、消息提醒和日志分析，有效弥补传统ETL工具在调度管理和监控分析等方面的不足，大幅降低您的开发和运维成本，产品还提供数据质量和血缘分析等其它功能，详情可通过官网 http://www.haohedi.com 进行了解和在线体验

 

转载于:https://www.cnblogs.com/haohedi/p/9679490.html