社区 学习 工具库 休闲
搜索
简体中文
首页 > 数据库 > mysql教程 > 正文

一起来聊聊数据库拉链表

WBOY
发布： 2022-06-20 12:01:37
转载
2669 人浏览过

本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于数据库拉链表的相关问题，拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史，下面一起来看一下，希望对大家有帮助。

一起来聊聊数据库拉链表

推荐学习：mysql视频教程

拉链表产生背景

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

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_NEWWHERE (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 A2SET 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_productselect *,'20191220' from lalian.t_product ;# 从ods同步到dw层insert into lalian.dw_t_productselect * 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_productselect *,'20191221' from lalian.t_product ;# 从ods同步到dw层insert into lalian.dw_t_productselect * 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_product2select *,'20191220' from lalian.t_product2 where modifytime >='2019-12-20';
登录后复制

将数据从ods层导入到dw层

insert into lalian.dw_t_product2select 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_product2select *,'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.cdatfrom lalian.dw_t_product2 t1left join (select * from lalian.ods_t_product2 where cdat='20191221')t2 on t1.goods_id=t2.goods_idunionselect 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个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。

推荐学习：mysql视频教程

以上是一起来聊聊数据库拉链表的详细内容。更多信息请关注PHP中文网其他相关文章！

相关标签：
mysql
来源：csdn.net
上一篇：mysql存储过程中的循环语句有哪些 下一篇：mysql不是内部命令要怎么解决
本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
作者最新文章
最新问题
如何从结果集中仅显示最低值（MYSQL） 我有以下声明：selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'frommessagewherefacilityi...
来自于 2024-04-06 21:44:19
0
1
603
使用MySQL SQL查询计算另一张表中字段的总和 我有一个这样的模式：具有属性“user_id”和“username”的用户表以及具有属性“customer_id”（user_id的FK）和“finalPrice”的订单表数据库架...
来自于 2024-04-06 19:39:29
0
1
441
MySQL：'从......中选择文本作为<此处变量或子查询>” 我有下表，其中包含以下数据：id文本语言1德语文本德语2英文文本英语我想要的是获得以下格式的结果：german="deutschertext"english=&...
来自于 2024-04-06 19:25:54
0
2
529
MySQL中如何进行分组和计数？ 我正在尝试编写一个查询，提取发送给朋友的未删除消息的总数。这是表结构的屏幕截图：我想要实现的输出结果如下：idmessages_count---------------------...
来自于 2024-04-06 18:30:17
0
1
353
MySQL从多个表中获取数据 我有一个eg_design表，其中包含以下几列：和eg_domains表，其中包含以下列：和eg_fonts表，其中包含以下列：现在，基于$domain_id我想获取eg_desi...
来自于 2024-04-06 18:42:44
0
2
479
相关专题
更多>
热门推荐
热门教程
更多>
相关教程
热门推荐
最新课程
最新下载
更多>
网站特效
网站源码
网站素材
前端模板