微信搜索lxw1234bigdata | 邀请体验：数阅–数据管理、OLAP分析与可视化平台 | 赞助作者：赞助作者

数据仓库中历史拉链表的更新方法

数据仓库/数据库 lxw1234@qq.com 89935℃ 18评论

关键字：数据仓库、极限存储、历史拉链表、更新

在之前介绍过数据仓库中的历史拉链表《数据仓库数据模型之：极限存储–历史拉链表》，

使用这种方式即可以记录历史，而且最大程度的节省存储。这里简单介绍一下这种历史拉链表的更新方法。

本文中假设：

  1. 数据仓库中订单历史表的刷新频率为一天，当天更新前一天的增量数据；
  2. 如果一个订单在一天内有多次状态变化，则只会记录最后一个状态的历史；
  3. 订单状态包括三个：创建、支付、完成；
  4. 创建时间和修改时间只取到天，如果源订单表中没有状态修改时间，那么抽取增量就比较麻烦，需要有个机制来确保能抽取到每天的增量数据；
  5. 本文中的表和SQL都使用Hive的HQL语法；
  6. 源系统中订单表结构为：

CREATE TABLE orders (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) stored AS textfile;

7.在数据仓库的ODS层，有一张订单的增量数据表，按天分区，存放每天的增量数据：

CREATE TABLE t_ods_orders_inc (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY (day STRING)
stored AS textfile;

8. 在数据仓库的DW层，有一张订单的历史数据拉链表，存放订单的历史状态数据：

CREATE TABLE t_dw_orders_his (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING,
dw_start_date STRING,
dw_end_date STRING
) stored AS textfile;

9. 暂未考虑Hive上表的查询性能问题，只实现功能；

华丽的分割线：您可以关注 lxw的大数据田地 ，或者 加入邮件列表 ，随时接收博客更新的通知邮件。

 

10. 2015-08-21至2015-08-23，每天原系统订单表的数据如下，红色标出的为当天发生变化的订单，即增量数据：

历史拉链表

历史拉链表

历史拉链表

全量初始化

在数据从源业务系统每天正常抽取和刷新到DW订单历史表之前，需要做一次全量的初始化，就是从源订单表中昨天以前的数据全部抽取到ODW，并刷新到DW。

以上面的数据为例，比如在2015-08-21这天做全量初始化，那么我需要将包括2015-08-20之前的所有的数据都抽取并刷新到DW：

第一步，抽取全量数据到ODS：
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘2015-08-20′)
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime <= ‘2015-08-20′;

第二步，从ODS刷新到DW：
INSERT overwrite TABLE t_dw_orders_his
SELECT orderid,createtime,modifiedtime,status,
createtime AS dw_start_date,
‘9999-12-31′ AS dw_end_date
FROM t_ods_orders_inc
WHERE day = ‘2015-08-20′;

完成后，DW订单历史表中数据如下：

spark-sql> select * from t_dw_orders_his;
1       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31      
2       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-19      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-19      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      9999-12-31
6       2015-08-20      2015-08-20      创建    2015-08-20      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-20      9999-12-31
Time taken: 2.296 seconds, Fetched 7 row(s)
华丽的分割线：您可以关注 lxw的大数据田地 ，或者 加入邮件列表 ，随时接收博客更新的通知邮件。

 

增量抽取

每天，从源系统订单表中，将前一天的增量数据抽取到ODS层的增量数据表。
这里的增量需要通过订单表中的创建时间和修改时间来确定：
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘${day}‘)
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime = ‘${day}’ OR modifiedtime = ‘${day}';

注意：在ODS层按天分区的增量表，最好保留一段时间的数据，比如半年，为了防止某一天的数据有问题而回滚重做数据。

增量刷新历史数据

从2015-08-22开始，需要每天正常刷新前一天（2015-08-21）的增量数据到历史表。

第一步，通过增量抽取，将2015-08-21的数据抽取到ODS：
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘2015-08-21′)
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime = ‘2015-08-21′ OR modifiedtime = ‘2015-08-21′;

ODS增量表中2015-08-21的数据如下：

spark-sql> select * from t_ods_orders_inc where day = '2015-08-21';
3       2015-08-19      2015-08-21      支付    2015-08-21
4       2015-08-19      2015-08-21      完成    2015-08-21
7       2015-08-20      2015-08-21      支付    2015-08-21
8       2015-08-21      2015-08-21      创建    2015-08-21
Time taken: 0.437 seconds, Fetched 4 row(s)

第二步，通过DW历史数据(数据日期为2015-08-20)，和ODS增量数据(2015-08-21)，刷新历史表：

先把数据放到一张临时表中：

DROP TABLE IF EXISTS t_dw_orders_his_tmp;
CREATE TABLE t_dw_orders_his_tmp AS 
SELECT orderid,
createtime,
modifiedtime,
status,
dw_start_date,
dw_end_date 
FROM (
    SELECT a.orderid,
    a.createtime,
    a.modifiedtime,
    a.status,
    a.dw_start_date,
    CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-21' THEN '2015-08-20' ELSE a.dw_end_date END AS dw_end_date 
    FROM t_dw_orders_his a 
    left outer join (SELECT * FROM t_ods_orders_inc WHERE day = '2015-08-21') b 
    ON (a.orderid = b.orderid) 
    UNION ALL 
    SELECT orderid,
    createtime,
    modifiedtime,
    status,
    modifiedtime AS dw_start_date,
    '9999-12-31' AS dw_end_date 
    FROM t_ods_orders_inc 
    WHERE day = '2015-08-21' 
) x 
ORDER BY orderid,dw_start_date;

其中：
UNION ALL的两个结果集中，第一个是用历史表left outer join 日期为 ${yyy-MM-dd} 的增量，能关联上的，并且dw_end_date > ${yyy-MM-dd}，说明状态有变化，则把原来的dw_end_date置为(${yyy-MM-dd} – 1), 关联不上的，说明状态无变化，dw_end_date无变化。
第二个结果集是直接将增量数据插入历史表。

最后把临时表中数据插入历史表：
INSERT overwrite TABLE t_dw_orders_his
SELECT * FROM t_dw_orders_his_tmp;

华丽的分割线：您可以关注 lxw的大数据田地 ，或者 加入邮件列表 ，随时接收博客更新的通知邮件。

 

刷新完后，历史表中数据如下：

spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
1       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31
2       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-19      2015-08-20
3       2015-08-19      2015-08-21      支付    2015-08-21      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      9999-12-31
6       2015-08-20      2015-08-20      创建    2015-08-20      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-20      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      9999-12-31
Time taken: 0.717 seconds, Fetched 11 row(s)

由于在2015-08-21做了8月20日以前的数据全量初始化，而订单3、4、7在2015-08-21的增量数据中也存在，因此都有两条记录，但不影响后面的查询。

再看将2015-08-22的增量数据刷新到历史表：

INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-22') 
SELECT orderid,createtime,modifiedtime,status 
FROM orders 
WHERE createtime = '2015-08-22' OR modifiedtime = '2015-08-22';

DROP TABLE IF EXISTS t_dw_orders_his_tmp;
CREATE TABLE t_dw_orders_his_tmp AS 
SELECT orderid,
createtime,
modifiedtime,
status,
dw_start_date,
dw_end_date 
FROM (
    SELECT a.orderid,
    a.createtime,
    a.modifiedtime,
    a.status,
    a.dw_start_date,
    CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-22' THEN '2015-08-21' ELSE a.dw_end_date END AS dw_end_date 
    FROM t_dw_orders_his a 
    left outer join (SELECT * FROM t_ods_orders_inc WHERE day = '2015-08-22') b 
    ON (a.orderid = b.orderid) 
    UNION ALL 
    SELECT orderid,
    createtime,
    modifiedtime,
    status,
    modifiedtime AS dw_start_date,
    '9999-12-31' AS dw_end_date 
    FROM t_ods_orders_inc 
    WHERE day = '2015-08-22' 
) x 
ORDER BY orderid,dw_start_date;


INSERT overwrite TABLE t_dw_orders_his 
SELECT * FROM t_dw_orders_his_tmp;

刷新完后历史表数据如下：

spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
1       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
1       2015-08-18      2015-08-22      支付    2015-08-22      9999-12-31
2       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-19      2015-08-20
3       2015-08-19      2015-08-21      支付    2015-08-21      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      9999-12-31
6       2015-08-20      2015-08-20      创建    2015-08-20      2015-08-21
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-20      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      2015-08-21
8       2015-08-21      2015-08-22      支付    2015-08-22      9999-12-31
9       2015-08-22      2015-08-22      创建    2015-08-22      9999-12-31
10      2015-08-22      2015-08-22      支付    2015-08-22      9999-12-31
Time taken: 0.66 seconds, Fetched 17 row(s)
华丽的分割线：您可以关注 lxw的大数据田地 ，或者 加入邮件列表 ，随时接收博客更新的通知邮件。

 

查看2015-08-21的历史快照数据：

spark-sql> select * from t_dw_orders_his where dw_start_date <= '2015-08-21' and dw_end_date >= '2015-08-21';
1       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
2       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
3       2015-08-19      2015-08-21      支付    2015-08-21      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      9999-12-31
6       2015-08-20      2015-08-20      创建    2015-08-20      2015-08-21
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      2015-08-21

订单1在2015-08-21的时候还处于创建的状态，在2015-08-22的时候状态变为支付。

再刷新2015-08-23的增量数据：

按照上面的方法刷新完后，历史表数据如下：

spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
1       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
1       2015-08-18      2015-08-22      支付    2015-08-22      2015-08-22
1       2015-08-18      2015-08-23      完成    2015-08-23      9999-12-31
2       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-19      2015-08-20
3       2015-08-19      2015-08-21      支付    2015-08-21      2015-08-22
3       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      2015-08-22
5       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
6       2015-08-20      2015-08-20      创建    2015-08-20      2015-08-21
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-20      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      2015-08-21
8       2015-08-21      2015-08-22      支付    2015-08-22      2015-08-22
8       2015-08-21      2015-08-23      完成    2015-08-23      9999-12-31
9       2015-08-22      2015-08-22      创建    2015-08-22      9999-12-31
10      2015-08-22      2015-08-22      支付    2015-08-22      9999-12-31
11      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
12      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
13      2015-08-23      2015-08-23      支付    2015-08-23      9999-12-31

订单1从20号-23号，状态变化了三次，历史表中有三条记录。

//查看2015-08-22当天的历史快照，可以看出，和上面图中2015-08-22时候订单表中的数据是一样的
spark-sql> select * from t_dw_orders_his where dw_start_date <= '2015-08-22' and dw_end_date >= '2015-08-22';
1       2015-08-18      2015-08-22      支付    2015-08-22      2015-08-22
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-21      2015-08-22
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      2015-08-22
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-22      支付    2015-08-22      2015-08-22
9       2015-08-22      2015-08-22      创建    2015-08-22      9999-12-31
10      2015-08-22      2015-08-22      支付    2015-08-22      9999-12-31
Time taken: 0.328 seconds, Fetched 10 row(s)
//查看当前所有订单的最新状态
spark-sql> select * from t_dw_orders_his where dw_end_date = '9999-12-31';                                   
1       2015-08-18      2015-08-23      完成    2015-08-23      9999-12-31
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-23      完成    2015-08-23      9999-12-31
9       2015-08-22      2015-08-22      创建    2015-08-22      9999-12-31
10      2015-08-22      2015-08-22      支付    2015-08-22      9999-12-31
11      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
12      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
13      2015-08-23      2015-08-23      支付    2015-08-23      9999-12-31
Time taken: 0.293 seconds, Fetched 13 row(s)

实际业务中，有可能某一天的数据有问题，需要回滚或重做，这点有点麻烦，后续文章再介绍。

相关阅读：

数据仓库数据模型之：极限存储–历史拉链表

华丽的分割线：您可以关注 lxw的大数据田地 ，或者 加入邮件列表 ，随时接收博客更新的通知邮件。

 

如果觉得本博客对您有帮助，请 赞助作者 。

转载请注明：lxw的大数据田地 » 数据仓库中历史拉链表的更新方法

喜欢 (144)
赏
分享 (0)
发表我的评论
取消评论
表情

Hi，您需要填写昵称和邮箱！

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(18)个小伙伴在吐槽
  1. 文章写的非常的好，我们现在也在做拉链的表，在你的文章中，有几个地方不太明白： 1.CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-21' THEN '2015-08-20' ELSE a.dw_end_date END AS dw_end_date 说明状态有变化，则把原来的dw_end_date置为(${yyy-MM-dd} – 1), 关联不上的，说明状态无变化，dw_end_date无变化。 这个上面的(${yyy-MM-dd} – 1)，我认为是在2015-8-22号做的增量数据，${yyy-MM-dd} – 1) 这个dw_end_date的时间应该为2015-8-21日期，为什么你的语句中是2015-8-20？ 2.若是源库有删除的记录，在这个拉链表中如何把删除的置为失效状态？
    bingfeng20042016-04-16 17:58 回复
    • 1.${yyyy-MM-dd}这个时间已经是数据日期了，也就是2015-08-21，这些数据都是在2015-08-21产生的增量，发生的变化，因此要把以前的记录结束日期置为${yyyy-MM-dd,-1d}. 2.文章中确实没有考虑有删除记录的情况，一般业务系统肯定不会把记录物理删除，都是通过记录状态或者移动到另外一张表来实现，这样的话，就单独处理一下删除的这部分记录。如果真是物理删除，那就比较麻烦，需要通过DW历史表和业务系统表关联找到删除的记录，当然不能硬关联。
      lxw1234@qq.com2016-04-20 09:48 回复
  2. 你好！如果数据有问题，怎么实现数据重做呢？能讲解一下吗？谢谢！！！
    polo_suns2016-04-28 13:27 回复
  3. 你好，数据的回滚和重做，有什么好的方法吗？我现在的做法是每天拉链前先备份上一次拉链的结果，这种方式比较傻，只能当天发现问题后修复，第二天再看连备份的数据也是错的了。
    lx_sot2016-06-21 14:17 回复
  4. 非常期待 数据回滚 及重做拉链的 文章
    核动力贝贝2016-06-22 18:51 回复
  5. 您好，我看了下代码，发现历史表每次都要全表删除再插入数据，那么如果是几千万的历史表，效率应该会很低对不，导致增量更新很慢。 我觉得才用先update历史表的增量数据，再插入这种方法效率会好些。不知您是否这样觉得？ :smile:
    烤年糕2017-04-07 15:15 回复
    • UPDATEL历史表的增量数据？不太理解哎
      沐沐林2017-05-17 19:48 回复
  6. 在ORALCE里面怎么添加这个临时表，
    沐沐林2017-05-17 19:24 回复
    • --拉链表测试 --需要原始表有一个代表记录有更新的时间戳字段 --订单原始表 create table orders ( orderid int, createtime varchar2(10), modifiedtime varchar2(10) , status varchar2(20)); -- 2015-08-21日的订单表 select * from orders ; -- 2015-08-22日的订单表 select * from orders1 ; -- 2015-08-23日的订单表 select * from orders2 ; --订单每日增量更新表 CREATE TABLE t_ods_orders_inc ( orderid INT, createtime varchar2(10), modifiedtime varchar2(10), status varchar2(20) ) ; select * from t_ods_orders_inc ; --订单历史表 CREATE TABLE t_dw_orders_his ( orderid INT, createtime varchar2(10), modifiedtime varchar2(10), status varchar2(20), dw_start_date varchar2(10), dw_end_date varchar2(10) ) ; select * from t_dw_orders_his ; --历史数据临时表 select * from t_dw_orders_his_tmp ; /* 在数据从源业务系统每天正常抽取和刷新到DW订单历史表之前， 需要做一次全量的初始化，就是从源订单表中昨天以前的数据全部抽取到ODW，并刷新到DW。 以上面的数据为例，比如在2015-08-21这天做全量初始化， 那么我需要将包括2015-08-20之前的所有的数据都抽取并刷新到DW： */ truncate table t_ods_orders_inc; select * from t_ods_orders_inc --第一步，抽取全量数据到ODS： INSERT into t_ods_orders_inc SELECT orderid,createtime,modifiedtime,status FROM orders WHERE createtime '2015-08-21' THEN '2015-08-20' ELSE a.dw_end_date END AS dw_end_date FROM t_dw_orders_his a left join t_ods_orders_inc b ON (a.orderid = b.orderid) UNION ALL SELECT orderid, createtime, modifiedtime, status, modifiedtime AS dw_start_date, '9999-12-31' AS dw_end_date FROM t_ods_orders_inc ) x ; commit; select * from t_dw_orders_his_tmp ; /* 其中： UNION ALL的两个结果集中，第一个是用历史表left outer join 日期为 ${yyy-MM-dd} 的增量， 能关联上的，并且dw_end_date > ${yyy-MM-dd}，说明状态有变化，则把原来的dw_end_date置为(${yyy-MM-dd} – 1), 关联不上的，说明状态无变化，dw_end_date无变化。 第二个结果集是直接将增量数据插入历史表。*/ --最后把临时表中数据插入历史表： --清空历史表 t_dw_orders_his truncate table t_dw_orders_his ; INSERT into t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp; select * from t_dw_orders_his ; /* 由于在2015-08-21做了8月20日以前的数据全量初始化，而订单3、4、7在2015-08-21的增量数据中也存在， 因此都有两条记录，但不影响后面的查询。 再看将2015-08-22的增量数据刷新到历史表：*/ truncate table t_ods_orders_inc ; INSERT into t_ods_orders_inc SELECT orderid,createtime,modifiedtime,status FROM orders1 --用对应的8月22号的全量订单表，取出新增数据 WHERE createtime = '2015-08-22' OR modifiedtime = '2015-08-22'; commit; -- 刷新到历史临时表： truncate table t_dw_orders_his_tmp ; insert into t_dw_orders_his_tmp SELECT orderid, createtime, modifiedtime, status, dw_start_date, dw_end_date FROM ( SELECT a.orderid, a.createtime, a.modifiedtime, a.status, a.dw_start_date, CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-22' THEN '2015-08-21' ELSE a.dw_end_date END AS dw_end_date FROM t_dw_orders_his a left join t_ods_orders_inc b ON a.orderid = b.orderid UNION ALL SELECT orderid, createtime, modifiedtime, status, modifiedtime AS dw_start_date, '9999-12-31' AS dw_end_date FROM t_ods_orders_inc ) x ; commit; -- 刷新到历史表 truncate table t_dw_orders_his ; INSERT into t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp; commit; select * from t_dw_orders_his ; -- 查看2015-08-21的历史快照数据： select * from t_dw_orders_his where dw_start_date = '2015-08-21'; -- 再刷新2015-08-23的增量数据： truncate table t_ods_orders_inc ; INSERT into t_ods_orders_inc SELECT orderid,createtime,modifiedtime,status FROM orders2 --用对应的8月22号的全量订单表，取出新增数据 WHERE createtime = '2015-08-23' OR modifiedtime = '2015-08-23'; commit; -- 刷新到历史临时表： truncate table t_dw_orders_his_tmp ; insert into t_dw_orders_his_tmp SELECT orderid, createtime, modifiedtime, status, dw_start_date, dw_end_date FROM ( SELECT a.orderid, a.createtime, a.modifiedtime, a.status, a.dw_start_date, CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-23' THEN '2015-08-22' ELSE a.dw_end_date END AS dw_end_date FROM t_dw_orders_his a left join t_ods_orders_inc b ON a.orderid = b.orderid UNION ALL SELECT orderid, createtime, modifiedtime, status, modifiedtime AS dw_start_date, '9999-12-31' AS dw_end_date FROM t_ods_orders_inc ) x ; commit; -- 刷新到历史表 truncate table t_dw_orders_his ; INSERT into t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp; commit; select * from t_dw_orders_his ; --查看2015-08-22当天的历史快照，可以看出，和上面图中2015-08-22时候订单表中的数据是一样的 select * from t_dw_orders_his where dw_start_date = '2015-08-22'; --查看当前所有订单的最新状态 select * from t_dw_orders_his where dw_end_date = '9999-12-31';
      高尔2017-06-22 15:00 回复
  7. CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-21' THEN '2015-08-20' ELSE a.dw_end_date END AS dw_end_date AND a.dw_end_date > '2015-08-21' 这个条件好像 :???: 多余了， 因为你的dw_end_date 都是9999-12-31，都大过任何日期值了，肯定是成立的， 从另外一个角度来说， union all 两个集合的时候，能关联上，就已经说明状态发生了变化，直接更改历史表的dw_end_date为T-1，表名该阶段状态生命周期的结束，另外全量插入增量数据。 不知道我理解的对不对，还望海涵， 博客很精致，内容很充实，学些了。
    高尔2017-06-22 14:59 回复
  8. 数据量大，效率太低下
    大大叔叔聚聚2017-09-16 09:56 回复
  9. 您好:请教个问题,假设20号新增一条数据,25号对这条数据进行了更新操作,那么这个时候想要查询21号的数据,根据您提供的sql,select * from t_dw_orders_his where dw_start_date = '2015-08-21'; 这样的结果不对吧? 谢谢
    炼心天毅2017-12-05 10:59 回复
  10. 您好:请教个问题,假设20号新增一条数据,25号对这条数据进行了更新操作,那么这个时候想要查询21号的数据,根据您提供的sql,select * from t_dw_orders_his where dw_start_date = '2015-08-21'; 这样查出的结果不对吧? 谢谢
    炼心天毅2017-12-05 11:01 回复
  11. 不好意思,这个sql select * from t_dw_orders_his where dw_start_date = '2015-08-21';
    炼心天毅2017-12-05 11:03 回复
  12. dw_start_date='2015-08-21' 不知道这个评论怎么回事
    炼心天毅2017-12-05 11:04 回复
  13. 增量检测到删除记录，需要特殊处理下join sql
    小白2019-06-12 14:31 回复
  14. 如果将此任务打包为job，每天自动运行，当这个job每天运行超过一次时， 数据重复如何解决？
    倚楼听风雨2019-07-27 16:31 回复
  15. 数据回滚怎么做呢
    哈哈2021-01-18 11:23 回复