1. 全栈程序员必看首页

拉链表详解_拉链表还原统计

全栈程序员-用户IM • • 未分类

拉链表详解_拉链表还原统计拉链表产生背景在数据仓库的数据模型设计过程中，经常会遇到这样的需求：1、数据量比较大；2、表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等；3、需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，比如，查看某一个用户在过去某一段时间内，更新过几次等等；4、变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右；5、如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

拉链表产生背景

在数据仓库的数据模型设计过程中，经常会遇到这样的需求：

1、数据量比较大；

2、表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等；

3、需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，比如，查看某一个用户在过去某一段时间内，更新过几次等等；

4、变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右；

5、如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费；

对于这种表有几种方案可选：

  • 方案一：每天只留最新的一份，比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。
  • 方案二：每天保留一份全量的切片数据。
  • 方案三：使用拉链表。

以上方案对比

方案一

这种方案就不用多说了，实现起来很简单，每天drop掉前一天的数据，重新抽一份最新的。

优点很明显，节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分区什么的。

缺点同样明显，没有历史数据，先翻翻旧账只能通过其它方式，比如从流水表里面抽。

方案二

每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。

缺点就是存储空间占用量太大太大了，如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费，这点我感触还是很深的…

当然我们也可以做一些取舍，比如只保留近一个月的数据？但是，需求是无耻的，数据的生命周期不是我们能完全左右的。

拉链表

拉链表在使用上基本兼顾了我们的需求。

首先它在空间上做了一个取舍，虽说不像方案一那样占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是万分之一。

其实它能满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取历史的数据。

所以我们还是很有必要来使用拉链表的。

拉链表概念

拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。

百度百科的解释：拉链表是维护历史状态，以及最新状态数据的一种表，拉链表根据拉链粒度的不同，实际上相当于快照，只不过做了优化，去除了一部分不变的记录，通过拉链表可以很方便的还原出拉链时点的客户记录。

拉链表算法

1、采集当日全量数据到ND(NowDay当日)表；

2、可从历史表中取出昨日全量数据存储到OD(OldDay上日)表；

3、两个表进行全字段比较，(ND-OD)就是当日新增和变化的数据，也就是当天的增量，用W_I表示；

4、两个表进行全字段比较，(OD-ND)为状态到此结束需要封链的数据，需要修改END_DATE，用W_U表示；

5、将W_I表的内容全部插入到历史表中，这些是新增记录，start_date为当天，而end_date为max值，可以设为’9999-12-31‘；

6、对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天，也就是关链操作，历史表（OD）和W_U表比较，START_DATE，END_DATE除外，以W_U表为准，两者交集将其END_DATE改成当日，说明该记录失效。

拉链表示例1

举个简单例子，比如有一张订单表：

6月20号有3条记录：

订单创建日期 订单编号 订单状态
2012-06-20 001 创建订单
2012-06-20 002 创建订单
2012-06-20 003 支付完成

到6月21日，表中有5条记录：

订单创建日期 订单编号 订单状态
2012-06-20 001 创建订单
2012-06-20 002 创建订单
2012-06-20 003 支付完成
2012-06-21 004 创建订单
2012-06-21 005 创建订单

到6月22日，表中有6条记录：

订单创建日期 订单编号 订单状态
2012-06-20 001 创建订单
2012-06-20 002 创建订单
2012-06-20 003 支付完成
2012-06-21 004 创建订单
2012-06-21 005 创建订单
2012-06-22 006 创建订单

数据仓库中对该表的保留方法：

1、只保留一份全量，则数据和6月22日的记录一样，如果需要查看6月21日订单001的状态，则无法满足；

2、每天都保留一份全量，则数据仓库中的该表共有14条记录，但好多记录都是重复保存，没有任务变化，如订单002,004，数据量大了，会造成很大的存储浪费；

如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表：

订单创建日期 订单编号 订单状态 dw_bigin_date dw_end_date
2012-06-20 001 创建订单 2012-06-20 2012-06-20
2012-06-20 001 支付完成 2012-06-21 9999-12-31
2012-06-20 002 创建订单 2012-06-20 9999-12-31
2012-06-20 003 支付完成 2012-06-20 2012-06-21
2012-06-20 003 已发货 2012-06-22 9999-12-31
2012-06-21 004 创建订单 2012-06-21 9999-12-31
2012-06-21 005 创建订单 2012-06-21 2012-06-21
2012-06-21 005 支付完成 2012-06-22 9999-12-31
2012-06-22 006 创建订单 2012-06-22 9999-12-31

说明：

1、dw_begin_date表示该条记录的生命周期开始时间，dw_end_date表示该条记录的生命周期结束时间；

2、dw_end_date = ‘9999-12-31’表示该条记录目前处于有效状态；

3、如果查询当前所有有效的记录，则select * from order_his where dw_end_date = ‘9999-12-31’；

4、如果查询2012-06-21的历史快照，则select * from order_his where dw_begin_date <= ‘2012-06-21’ and end_date >= ‘2012-06-21’，这条语句会查询到以下记录：

订单创建日期 订单编号 订单状态 dw_bigin_date dw_end_date
2012-06-20 001 支付完成 2012-06-21 9999-12-31
2012-06-20 002 创建订单 2012-06-20 9999-12-31
2012-06-20 003 支付完成 2012-06-20 2012-06-21
2012-06-21 004 创建订单 2012-06-21 9999-12-31
2012-06-21 005 创建订单 2012-06-21 2012-06-21

和源表在6月21日的记录完全一致：

订单创建日期 订单编号 订单状态
2012-06-20 001 创建订单
2012-06-20 002 创建订单
2012-06-20 003 支付完成
2012-06-21 004 创建订单
2012-06-21 005 创建订单

可以看出，这样的历史拉链表，既能满足对历史数据的需求，又能很大程度的节省存储资源；

拉链表示例2：

在历史表中对人的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题：

人名 开始日期 结束日期 状态
client 19000101 19070901 H在家
client 19070901 19130901 A小学
client 19130901 19160901 B初中
client 19160901 19190901 C高中
client 19190901 19230901 D大学
client 19230901 19601231 E公司
client 19601231 29991231 H退休在家

上面的每一条记录都是不算末尾的，比如到19070901，client已经在A，而不是H了。所以除最后一条记录因为状态到目前都未改变的，其余的记录实际上在结束日期那天，都不在是该条记录结束日期那天的状态。这种现象可以理解为算头不算尾。

拉链表实现方式

1、定义两个临时表，一个为当日全量数据，另一个为需要新增或更新的数据；

CREATE VOLATILE TABLE VT_xxxx_NEW AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS;
CREATE VOLATILE SET TABLE VT_xxxx_CHG,NO LOG AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS;

2、获取当日全量数据

INSERT INTO VT_xxxx_NEW(xx) SELECT (xx,cur_date, max_date) FROM xxxx_sorce;

3、抽取新增或有变化的数据，从xxxx_NEW临时表到xxxx_CHG临时表；

INSERT INTO VT_xxxx_CHG(xx)
SELECT xx FROM VT_xxxx_NEW
WHERE (xx) NOT IN (select xx from xxxx_HIS where end_date='max_date');

4、更新历史表的失效记录的end_date为max值

UPDATE A1 FROM xxxx_HIS A1, VT_xxxx_CHG A2
SET End_Date='current_date'
WHERE A1.xx=A2.xx AND A1.End_Date='max_date';

5、将新增或者有变化的数据插入目标表

INSERT INTO xxxx_HIS SELECT * FROM VT_xxxx_CHG;

以商品数据为例

存在商品表 t_product，表结构如下：

列名 类型 说明
goods_id varchar(50) 商品编号
goods_status varchar(50) 商品状态（待审核、待售、在售、已删除）
createtime varchar(50) 商品创建日期
modifytime varchar(50) 商品修改日期

2019年12月20日的数据如下所示：

goods_id goods_status createtime modifytime
001 待审核 2019-12-20 2019-12-20
002 待售 2019-12-20 2019-12-20
003 在售 2019-12-20 2019-12-20
004 已删除 2019-12-20 2019-12-20

商品的状态，会随着时间推移而变化，我们需要将商品的所有变化的历史信息都保存下来。

方案一: 快照每一天的数据到数仓

该方案为：每一天都保存一份全量，将所有数据同步到数仓中，很多记录都是重复保存，没有任何变化。

12月20日（4条数据）
goods_id goods_status createtime modifytime
001 待审核 2019-12-18 2019-12-20
002 待售 2019-12-19 2019-12-20
003 在售 2019-12-20 2019-12-20
004 已删除 2019-12-15 2019-12-20
12月21日（10条数据）
goods_id goods_status createtime modifytime
以下为12月20日快照数据
001 待审核 2019-12-18 2019-12-20
002 待售 2019-12-19 2019-12-20
003 在售 2019-12-20 2019-12-20
004 已删除 2019-12-15 2019-12-20
以下为12月21日快照数据
001 待售（从待审核到待售） 2019-12-18 2019-12-21
002 待售 2019-12-19 2019-12-20
003 在售 2019-12-20 2019-12-20
004 已删除 2019-12-15 2019-12-20
005（新商品） 待审核 2019-12-21 2019-12-21
006（新商品） 待审核 2019-12-21 2019-12-21
12月22日（18条数据）
goods_id goods_status createtime modifytime
以下为12月20日快照数据
001 待审核 2019-12-18 2019-12-20
002 待售 2019-12-19 2019-12-20
003 在售 2019-12-20 2019-12-20
004 已删除 2019-12-15 2019-12-20
以下为12月21日快照数据
001 待售（从待审核到待售） 2019-12-18 2019-12-21
002 待售 2019-12-19 2019-12-20
003 在售 2019-12-20 2019-12-20
004 已删除 2019-12-15 2019-12-20
005 待审核 2019-12-21 2019-12-21
006 待审核 2019-12-21 2019-12-21
以下为12月22日快照数据
001 待售 2019-12-18 2019-12-21
002 待售 2019-12-19 2019-12-20
003 已删除（从在售到已删除） 2019-12-20 2019-12-22
004 待审核 2019-12-21 2019-12-21
005 待审核 2019-12-21 2019-12-21
006 已删除（从待审核到已删除） 2019-12-21 2019-12-22
007 待审核 2019-12-22 2019-12-22
008 待审核 2019-12-22 2019-12-22
MySQL数仓代码实现

MySQL初始化

在MySQL中 lalian 库和商品表用于到原始数据层

-- 创建数据库
create database if not exists lalian;
-- 创建商品表
create table if not exists `lalian`.`t_product`(
	goods_id varchar(50), -- 商品编号
    goods_status varchar(50), -- 商品状态
    createtime varchar(50), -- 商品创建时间
    modifytime varchar(50) -- 商品修改时间
);

在MySQL中创建ods和dw层来模拟数仓

-- ods创建商品表
create table if not exists `lalian`.`ods_t_product`(
	goods_id varchar(50), -- 商品编号
	goods_status varchar(50), -- 商品状态
	createtime varchar(50), -- 商品创建时间
	modifytime varchar(50), -- 商品修改时间
	cdat varchar(10)   -- 模拟hive分区
)default character set = 'utf8';
-- dw创建商品表
create table if not exists `lalian`.`dw_t_product`(
	goods_id varchar(50), -- 商品编号
	goods_status varchar(50), -- 商品状态
 	createtime varchar(50), -- 商品创建时间
 	modifytime varchar(50), -- 商品修改时间
 	cdat varchar(10)  -- 模拟hive分区
)default character set = 'utf8';

增量导入12月20号数据

原始数据导入12月20号数据（4条）

insert into `lalian`.`t_product`(goods_id, goods_status, createtime, modifytime) values
('001', '待审核', '2019-12-18', '2019-12-20'),
('002', '待售', '2019-12-19', '2019-12-20'),
('003', '在售', '2019-12-20', '2019-12-20'),
('004', '已删除', '2019-12-15', '2019-12-20');

注意：由于这里使用的MySQL来模拟的数仓所以直接使用insert into的方式导入数据，在企业中可能会使用hive来做数仓使用 kettle 或者 sqoop 或 datax 等来同步数据。

# 从原始数据层导入到ods 层
insert into lalian.ods_t_product
select *,'20191220' from lalian.t_product ;
# 从ods同步到dw层
insert into lalian.dw_t_product
select * from lalian.ods_t_product where cdat='20191220';

查看dw层的运行结果

select * from lalian.dw_t_product where cdat='20191220';
goods_id goods_status createtime modifytime cdat
1 待审核 2019/12/18 2019/12/20 20191220
2 待售 2019/12/19 2019/12/20 20191220
3 在售 2019/12/20 2019/12/20 20191220
4 已删除 2019/12/15 2019/12/20 20191220

增量导入12月21数据

原始数据层导入12月21日数据（6条数据）

UPDATE `lalian`.`t_product` SET goods_status = '待售', modifytime = '2019-12-21' WHERE goods_id = '001';
INSERT INTO `lalian`.`t_product`(goods_id, goods_status, createtime, modifytime) VALUES
('005', '待审核', '2019-12-21', '2019-12-21'),
('006', '待审核', '2019-12-21', '2019-12-21');

将数据导入到ods层与dw层

# 从原始数据层导入到ods 层
insert into lalian.ods_t_product
select *,'20191221' from lalian.t_product ;
# 从ods同步到dw层
insert into lalian.dw_t_product
select * from lalian.ods_t_product where cdat='20191221';

查看dw层的运行结果

select * from lalian.dw_t_product where cdat='20191221';
goods_id goods_status createtime modifytime cdat
1 待售 2019/12/18 2019/12/21 20191221
2 待售 2019/12/19 2019/12/20 20191221
3 在售 2019/12/20 2019/12/20 20191221
4 已删除 2019/12/15 2019/12/20 20191221
5 待审核 2019/12/21 2019/12/21 20191221
6 待审核 2019/12/21 2019/12/21 20191221

增量导入12月22日数据

原始数据层导入12月22日数据（6条数据）

UPDATE `lalian`.`t_product` SET goods_status = '已删除', modifytime = '2019-12-22' WHERE goods_id = '003';
UPDATE `lalian`.`t_product` SET goods_status = '已删除', modifytime = '2019-12-22' WHERE goods_id = '006';
INSERT INTO `lalian`.`t_product`(goods_id, goods_status, createtime, modifytime) VALUES
('007', '待审核', '2019-12-22', '2019-12-22'),
('008', '待审核', '2019-12-22', '2019-12-22');

将数据导入到ods层与dw层

# 从原始数据层导入到ods 层
insert into lalian.ods_t_product
select *,'20191222' from lalian.t_product ;
# 从ods同步到dw层
insert into lalian.dw_t_productpeizhiwenjian
select * from lalian.ods_t_product where cdat='20191222';

查看dw层的运行结果

select * from lalian.dw_t_product where cdat='20191222';
goods_id goods_status createtime modifytime cdat
1 待售 2019/12/18 2019/12/21 20191222
2 待售 2019/12/19 2019/12/20 20191222
3 已删除 2019/12/20 2019/12/22 20191222
4 已删除 2019/12/15 2019/12/20 20191222
5 待审核 2019/12/21 2019/12/21 20191222
6 已删除 2019/12/21 2019/12/22 20191222
7 待审核 2019/12/22 2019/12/22 20191222
8 待审核 2019/12/22 2019/12/22 20191222

查看dw层的运行结果

select * from lalian.dw_t_product;
goods_id goods_status createtime modifytime cdat
1 待审核 2019/12/18 2019/12/20 20191220
2 待售 2019/12/19 2019/12/20 20191220
3 在售 2019/12/20 2019/12/20 20191220
4 已删除 2019/12/15 2019/12/20 20191220
1 待售 2019/12/18 2019/12/21 20191221
2 待售 2019/12/19 2019/12/20 20191221
3 在售 2019/12/20 2019/12/20 20191221
4 已删除 2019/12/15 2019/12/20 20191221
5 待审核 2019/12/21 2019/12/21 20191221
6 待审核 2019/12/21 2019/12/21 20191221
1 待售 2019/12/18 2019/12/21 20191222
2 待售 2019/12/19 2019/12/20 20191222
3 已删除 2019/12/20 2019/12/22 20191222
4 已删除 2019/12/15 2019/12/20 20191222
5 待审核 2019/12/21 2019/12/21 20191222
6 已删除 2019/12/21 2019/12/22 20191222
7 待审核 2019/12/22 2019/12/22 20191222
8 待审核 2019/12/22 2019/12/22 20191222

从上述案例，可以看到：表每天保留一份全量，每次全量中会保存很多不变的信息，如果数据量很大的话，对存储是极大的浪费，可以将表设计为拉链表，既能满足反应数据的历史状态，又可以最大限度地节省存储空间。

方案二: 使用拉链表保存历史快照

拉链表不存储冗余的数据，只有某行的数据发生变化，才需要保存下来，相比每次全量同步会节省存储空间

能够查询到历史快照

额外的增加了两列（dw_start_date、dw_end_date），为数据行的生命周期。

12月20日商品拉链表的数据
goods_id goods_status createtime modifytime dw_start_date dw_end_date
001 待审核 2019-12-18 2019-12-20 2019-12-20 9999-12-31
002 待售 2019-12-19 2019-12-20 2019-12-20 9999-12-31
003 在售 2019-12-20 2019-12-20 2019-12-20 9999-12-31
004 已删除 2019-12-15 2019-12-20 2019-12-20 9999-12-31

12月20日的数据是全新的数据导入到dw表

  • dw_start_date表示某一条数据的生命周期起始时间，即数据从该时间开始有效（即生效日期）
  • dw_end_date表示某一条数据的生命周期结束时间，即数据到这一天（不包含）（即失效日期）
  • dw_end_date为 9999-12-31，表示当前这条数据是最新的数据，数据到9999-12-31才过期
12月21日商品拉链表的数据
goods_id goods_status createtime modifytime dw_start_date dw_end_date
001 待审核 2019-12-18 2019-12-20 2019-12-20 2019-12-21
002 待售 2019-12-19 2019-12-20 2019-12-20 9999-12-31
003 在售 2019-12-20 2019-12-20 2019-12-20 9999-12-31
004 已删除 2019-12-15 2019-12-20 2019-12-20 9999-12-31
001（变） 待售 2019-12-18 2019-12-21 2019-12-21 9999-12-31
005（新） 待审核 2019-12-21 2019-12-21 2019-12-21 9999-12-31

拉链表中没有存储冗余的数据，即只要数据没有变化，无需同步

  • 001编号的商品数据的状态发生了变化（从待审核 → 待售），需要将原有的dw_end_date从9999-12-31变为2019-12-21，表示待审核状态，在2019/12/20（包含） – 2019/12/21（不包含）有效；
  • 001编号新的状态重新保存了一条记录，dw_start_date为2019/12/21，dw_end_date为9999/12/31；
  • 新数据005、006、dw_start_date为2019/12/21，dw_end_date为9999/12/31。
12月22日商品拉链表的数据
goods_id goods_status createtime modifytime dw_start_date dw_end_date
001 待审核 2019-12-18 2019-12-20 2019-12-20 2019-12-21
002 待售 2019-12-19 2019-12-20 2019-12-20 9999-12-31
003 在售 2019-12-20 2019-12-20 2019-12-20 2019-12-22
004 已删除 2019-12-15 2019-12-20 2019-12-20 9999-12-31
001 待售 2019-12-18 2019-12-21 2019-12-21 9999-12-31
005 待审核 2019-12-21 2019-12-21 2019-12-21 9999-12-31
006 待审核 2019-12-21 2019-12-21 2019-12-21 9999-12-31
003（变） 已删除 2019-12-20 2019-12-22 2019-12-22 9999-12-31
007（新） 待审核 2019-12-22 2019-12-22 2019-12-22 9999-12-31
008（新） 待审核 2019-12-22 2019-12-22 2019-12-22 9999-12-31

拉链表中没有存储冗余的数据，即只要数据没有变化，无需同步

  • 003编号的商品数据的状态发生了变化（从在售→已删除），需要将原有的 dw_end_date从9999-12-31变为2019-12-22，表示在售状态，在2019/12/20（包含） – 2019/12/22（不包含） 有效
  • 003编号新的状态重新保存了一条记录，dw_start_date为2019-12-22，dw_end_date为9999-12-31
  • 新数据007、008、dw_start_date为2019-12-22，dw_end_date为9999-12-31
MySQL数仓拉链表快照实现

操作流程：

  1. 在原有dw层表上，添加额外的两列
  2. 只同步当天修改的数据到ods层
  3. 拉链表算法实现
  4. 拉链表的数据为：当天最新的数据 UNION ALL 历史数据

代码实现

在MySQL中lalian库和商品表用于到原始数据层

-- 创建数据库
create database if not exists lalian;
-- 创建商品表
create table if not exists `lalian`.`t_product2`(
	goods_id varchar(50), -- 商品编号
	goods_status varchar(50), -- 商品状态
    createtime varchar(50), -- 商品创建时间
    modifytime varchar(50) -- 商品修改时间
)default character set = 'utf8';

在MySQL中创建ods和dw层 模拟数仓

-- ods创建商品表
create table if not exists `lalian`.`ods_t_product2`(
	goods_id varchar(50), -- 商品编号
	goods_status varchar(50), -- 商品状态
	createtime varchar(50), -- 商品创建时间
	modifytime varchar(50), -- 商品修改时间
	cdat varchar(10)   -- 模拟hive分区
)default character set = 'utf8';
-- dw创建商品表
create table if not exists `lalian`.`dw_t_product2`(
	goods_id varchar(50), -- 商品编号
	goods_status varchar(50), -- 商品状态
	createtime varchar(50), -- 商品创建时间
	modifytime varchar(50), -- 商品修改时间
	dw_start_date varchar(12), -- 生效日期
	dw_end_date varchar(12), -- 失效时间
	cdat varchar(10)  -- 模拟hive分区
)default character set = 'utf8';

全量导入2019年12月20日数据

原始数据层导入12月20日数据（4条数据）

insert into `lalian`.`t_product_2`(goods_id, goods_status, createtime, modifytime) values
('001', '待审核', '2019-12-18', '2019-12-20'),
('002', '待售', '2019-12-19', '2019-12-20'),
('003', '在售', '2019-12-20', '2019-12-20'),
('004', '已删除', '2019-12-15', '2019-12-20');

将数据导入到数仓中的ods层

insert into lalian.ods_t_product2
select *,'20191220' from lalian.t_product2 where modifytime >='2019-12-20';

将数据从ods层导入到dw层

insert into lalian.dw_t_product2
select goods_id, goods_status, createtime, modifytime, modifytime,'9999-12-31', cdat from lalian.ods_t_product2 where cdat='20191220';

增量导入2019年12月21日数据

原始数据层导入12月21日数据（6条数据）

UPDATE `lalian`.`t_product2` SET goods_status = '待售', modifytime = '2019-12-21' WHERE goods_id = '001';
INSERT INTO `lalian`.`t_product2`(goods_id, goods_status, createtime, modifytime) VALUES
('005', '待审核', '2019-12-21', '2019-12-21'),
('006', '待审核', '2019-12-21', '2019-12-21');

原始数据层同步到ods层

insert into lalian.ods_t_product2
select *,'20191221' from lalian.t_product2 where modifytime >='2019-12-21';

编写ods层到dw层重新计算 dw_end_date

select t1.goods_id, t1.goods_status, t1.createtime, t1.modifytime,
       t1.dw_start_date,
       case when (t2.goods_id is not null and t1.dw_end_date>'2019-12-21') then '2019-12-21'else t1.dw_end_date end as dw_end_date ,
       t1.cdat
from lalian.dw_t_product2 t1
left join (select * from lalian.ods_t_product2 where cdat='20191221')t2 on t1.goods_id=t2.goods_id
union
select goods_id, goods_status, createtime, modifytime, modifytime,'9999-12-31', cdat from lalian.ods_t_product2 where cdat='20191221';

执行结果如下：

goods_id goods_status createtime modifytime dw_start_date dw_end_date cdat
1 待审核 2019-12-18 2019-12-20 2019-12-20 2019-12-21 20191220
2 待售 2019-12-19 2019-12-20 2019-12-20 9999-12-31 20191220
3 在售 2019-12-20 2019-12-20 2019-12-20 9999-12-31 20191220
4 已删除 2019-12-15 2019-12-20 2019-12-20 9999-12-31 20191220
1 待售 2019-12-18 2019-12-21 2019-12-21 9999-12-31 20191221
5 待审核 2019-12-21 2019-12-21 2019-12-21 9999-12-31 20191221
6 待审核 2019-12-21 2019-12-21 2019-12-21 9999-12-31 20191221

拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储。我们做拉链表的时候要确定拉链表的粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/181005.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

赞 (0)
全栈程序员-用户IM全栈程序员-用户IM
0 0


相关推荐

  • docker 上传本地镜像_不同docker仓库镜像同步 docker

    docker 上传本地镜像_不同docker仓库镜像同步

    docker 上传本地镜像_不同docker仓库镜像同步前言之前通过docker搭建过jenkins+python3环境，如果想要在不同的机器上搭建一样的环境，就可以将之前搭建的镜像上传到镜像仓库，这样方便在不同的机器上快速搭建同一套环境。如果公开的话

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年7月31日
  • 工作站与主域间的信任关系失败_变色龙工作站无法卸载

    工作站与主域间的信任关系失败_变色龙工作站无法卸载

    工作站与主域间的信任关系失败_变色龙工作站无法卸载症状：登陆域用户，出现错误“此工作站和主域间的信任关系失败”，并无法登陆解决方案：1、使用本地用户登录，更改当前“域”为“工作组”（如默认的WORKGROUP等），并输入具有域管理权限的用户名、密码，随后此电脑上的“域”将会被删除；2、重启电脑3、重新使用域用户信息登陆域4、重启电脑5、登陆成功 或者使用域管理员身份登陆DC服务器，从域中删除此计算机应该也可以。…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年10月10日
  • h5实现长按复制文本_复制css

    h5实现长按复制文本_复制css

    h5实现长按复制文本_复制css复制2333333<style> p{ -webkit-user-select:none; user-select:none; } p>i{ -webkit-user-select:text; user-select:text; } </style> <p>长按复制短码<i>23…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年9月27日
  • LabVIEW入门教程

    LabVIEW入门教程

    LabVIEW从初学到入门LabVIEW简介如何入门LabVIEW我该去哪找相应学习资源LabVIEW简介LabVIEW是一款图形化编程语言(G语言)，由美国国家仪器研制(NationalInstruments,NI)研制，被称为虚拟仪器(VirtualInstrument,VI)。它提供了整套的工具用来对信号进行采集、分析、保存及后续的处理。优点：界面美观程序模块化强与设备交…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年4月4日
  • Web Service进阶(一)运行原理[通俗易懂]

    Web Service进阶(一)运行原理[通俗易懂]

    Web Service进阶(一)运行原理[通俗易懂]利用清明小假期，温习了一遍WebService的相关内容，对其工作原理进行了简要总结。以供有需求的朋友和自己日后参考。文章若有不当之处，敬请朋友们提出宝贵建议，以求共勉。Web服务中，我们应该首先了解相关的术语含义：WSDL、UDDI….相关术语方面的介绍在此不再赘述，重点放在原理上。在Web服务中，存在三个角色：服务提供者、服务请求者和服务中介，三者之间的关系如图1…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年7月24日
  • 关于【ExecuteReader: Connection 属性尚未初始化】的错误提示解决方案「建议收藏」

    关于【ExecuteReader: Connection 属性尚未初始化】的错误提示解决方案「建议收藏」

    关于【ExecuteReader: Connection 属性尚未初始化】的错误提示解决方案「建议收藏」遇到这种错误，很大概率是因为缺少SqlCommand里的两个参数。找到【SqlCommandmycmd=newSqlCommand();】这条语句，SqlCommand（）有两个带传入的参数：一个是定义查询语句的sql， 另一个是定义数据库连接的conn以下为部分代码片段，仅供理解：stringsql=”select*fromInformationwhereUsername='”+Username+”‘andPassword='”+Passw.

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年6月20日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号