ETL项目中变更表如何转换为拉链表

还有点东西会补充，吐槽一下手机app里不能保存草稿

在经济学的研究中，研究人员往往需要对历史数据有较高要求（需要做成面板数据panel）。我大学里，在统计学这方面也接触过时间序列。
由于数据量大，历史数据不能像面板数据那样，每一年记录一天，这样会有大量的数据冗余。我遇到的历史数据是这么记录的:
​

样例数据

CREATE TABLE `alt_data` (
  `id` varchar(20) DEFAULT NULL,
  `ALT_TYPE` varchar(45) DEFAULT NULL,
  `ALT_DATE` date DEFAULT NULL,
  `ALT_BE` varchar(45) DEFAULT NULL,
  `ALT_AF` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

假如存在数据如下：
id, ALT_TYPE, ALT_DATE, ALT_BE, ALT_AF
‘111’, ‘TYPE1’, ‘2017-02-25’, ‘a’, ‘b’
‘111’, ‘TYPE1’, ‘2018-03-08’, ‘b’, ‘c’
‘111’, ‘TYPE1’, ‘2019-08-01’, ‘c’, ‘d’
‘112’, ‘TYPE1’, ‘2011-01-01’, ‘1’, ‘2’
（表1）

我们这边采用的保存历史数据的方式为最新数据+如上变更表（我在网上搜了很久也不知道这种形式怎么称呼，就且跟着叫变更表）。

• id是跟关联最新数据的外键
• ALT_TYPE对应的是最新数据中发生变更的字段名
• ALT_DATE是变更时间
• ALT_BE、ALT_AF分别是变更前后的内容。

遇到这种形式的数据，我第一反应是用存储过程。但仔细一想，要么，我每处理一个id，都访问一遍数据库，获取对应id的变更数据（效率低下）；要么做表连接，再处理连接后的结果，那还不如直接用SQL语句做。

那么如果要用SQL语句做成面板数据，我的思路是先把变更表做成拉链表，再与时间表做全外连接（若研究范围是[2010，2015]，那么时间表就是（2010，2011，2012……2015）），筛选出时间表内的年份落在拉链表中时间范围内的记录，即所需面板数据。

变更表2拉链表

若在变更表中以id和ALT_TYPE分组，并按时间升序：

• 每一组中，第一条记录的变更前内容没有开始时间FROM_D