社群 學習 工具庫 休閒
搜尋
繁体中文
首頁 > 資料庫 > mysql教程 > 主體

一起來聊聊資料庫拉鍊表

WBOY
發布： 2022-06-20 12:01:37
轉載
2678 人瀏覽過

本篇文章為大家帶來了關於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-21004#建立訂單2012-06-21005建立訂單
訂單建立日期 訂單編號 訂單狀態
2012-06-20 001 建立訂單
2012-06-20 002 建立訂單
2012-06-20 003
到6月22日，表中有6筆記錄：

訂單建立日期訂單編號訂單狀態#2012-06-20001建立訂單2012-06-20002」建立訂單#2012-06-20003支付完成2012-06-21#004建立訂單2012-06-21#005建立訂單2012-06-22006建立訂單
#資料倉儲中對該資料表的保留方法：

1、只保留一份全量，則資料和6月22日的記錄一樣，如果需要查看6月21日訂單001的狀態，則無法滿足；

2、每天都保留一份全量，則資料倉儲中的該表共有14筆記錄，但好多記錄都是重複保存，沒有任務變化，如訂單002,004，資料量大了，會造成很大的儲存浪費；

如果在資料倉儲中設計成歷史拉鍊表儲存該表，則會有下面這樣一張表：

#訂單建立日期#2012-06-20##2012-06-20001支付完成2012-06-219999-12-312012-06- 20002建立訂單2012-06-209999-12-31# 2012-06-20003支付完成2012-06-202012-06-21#2012-06-20003已出貨2012-06-229999-12-31#2012-06-21004 建立訂單2012-06-21#9999-12- 312012-06-21#005建立訂單2012-06-21 2012-06-21#2012-06-21005支付完成#2012-06-229999-12-312012-06-22006建立訂單#2012-06 -229999-12-31#說明：
##訂單編號 訂單狀態 dw_bigin_date dw_end_date
#001 建立訂單 2012-06-20 2012-06-20
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-;= '2012-; 06-21' and end_date >= '2012-06-21'，這語句會查詢到以下記錄：

##訂單建立日期訂單編號date2012-06-20001支付完成2012-06-219999-12-312012-06- 20002建立訂單2012-06-209999-12-31# 2012-06-20003支付完成2012-06-202012-06-21#2012-06-21004建立訂單2012-06-219999-12-31#2012-06-21#005#建立訂單2012-06-21#2012-06-21 和來源表在6月21日的記錄完全一致：
訂單狀態 dw_bigin_date

訂單建立日期訂單編號訂單狀態2012-06-20001建立訂單2012-06-20002建立訂單003004#005
##2012-06-20
支付完成 2012-06-21
建立訂單 #2012-06-21
#建立訂單

可以看出，這樣的歷史拉鍊表，既能滿足對歷史資料的需求，又能很大程度的節省儲存資源；

拉鍊表示例2：

#在歷史表中對人的一生的記錄可能就這樣幾筆記錄，避免了按每一天記錄客戶狀態造成的海量存儲的問題：

##client19190901#19230901D大學client1923090119601231E公司#client1960123129991231H退休在家
人名 開始日期 結束日期 狀態
#client 19000101 #19070901 H在家
client 19070901 19130901 A小學
client 19130901 19160901 B國中
client # 19160901 19190901 C高中
上面的每一筆記錄都是不算結尾的，例如到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_statusvarchar(50)商品狀態（待審核、待售、在售、已刪除）createtimevarchar(50)商品建立日期modifytimevarchar(50)商品修改日期
列名 #說明
2019年12月20日的資料如下所示：

001待審核2019-12-202019-12-20002待售2019-12-202019-12-20003
goods_id #goods_status createtime #modifytime

##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條資料）
#2019-12-192019-12-20003在販售#2019-12-20 2019-12-20004已刪除2019 -12-202019-12 -212019-12-202019-12-20## 004已刪除2019-12-152019-12-20005（新商品）待審核2019-12-212019-12-21
#goods_id goods_status createtime modifytime
以下為12月20日快照資料


001 待審核 2019-12-18 2019-12-20
002
##2019-12-15
以下為12月21日快照資料

001 出售（從待審核到出售） 2019-12-18
002 #出售 #2019-12-19
003 在販售 #2019-12-20
##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日商品拉鍊表的資料
#001待審核2019-12-21#2019-12-202019-12-202019-12-20#9999-12-31
goods_id goods_status createtime #modifytime ##dw_start_date dw_end_date
##2019-12-18 2019-12-20 2019-12-20
## 002 出售 2019-12-19 2019-12-20
#9999- 12-31 003 在販售 2019-12-20 2019-12-20
9999-12-31 004 已刪除 #2019-12-15 2019-12-20
9999-12-31 #001（變） 出售 2019-12-18 2019-12-21 2019-12-21
##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日商品拉鍊表的資料
#001待審核#0020032019-12-22004001005#006#003（變）已刪除2019-12-202019-12-222019-12-229999-12-31#007（新)待審核2019-12-222019-12-222019-12-22#9999-12-31 #008（新）待審核#2019-12-22#2019- 12-222019-12-22
goods_id goods_status createtime #modifytime ##dw_start_date dw_end_date
##2019-12-18 2019-12-20 2019-12-20 2019-12-21
#待售 2019-12-19 2019-12-20 2019-12-20 #9999-12-31
在販售 2019-12-20 2019-12-20 #2019-12- 20
已刪除 2019-12-15 2019-12-20 2019-12-20 9999-12-31
#待售 2019-12-18 2019-12-21 #2019-12-21 #9999-12-31
待審核 2019-12-21 2019-12-21 #2019-12 -21 9999-12-31
待審核 #2019-12-21 2019-12-21 2019-12-21 9999-12-31
###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_des...
來自於 2024-04-06 18:42:44
0
2
479
相關專題
更多>
熱門推薦
熱門教學
更多>
相關教學
熱門推薦
最新課程
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板