本文主要是结合我平时遇到的一些问题，分析一下Oracle数据库中拉链表的一些非常规操作，即将多个拉链表合并和将有多个属性的拉链表拆分。为此我将从拉链表的设计思路和常规操作，开始逐渐分析。
定义
拉链表是一种数据库设计模式，类似于面向对象的23种设计模式，可以直接套用，经常用于储存历史数据和分析时间维度的数据。
关键点：
储存开始时间和结束时间。
开始时间和结束时间首尾相接，形成链式结构。
拉链表与LinkedList很相似，都以快速找到下一个元素为设计目标，只不过链表会存储内存地址，而拉链表只存了下一条数据的开始时间。理论上如果存了下一条数据的id也是可以的。
拉链表一般用于解决历史版本查询的问题，也可用于解决数值区间问题，查询效率高，占用空间小。
基本操作
１． 查询
对于表LINK_DEMO
表：LINK_DEMO
ID
DATA
START_DATE
END_DATE
1
x
2017-10-31
2017-11-15
1
y
2017-11-15
2018-03-02
2
z
2017-12-03
9999-12-31
比如我要查2018-01-01时刻的ID为1的记录数据是多少，那么SQL就是：
SELECT * FROM LINK_DEMO
WHERE ID = 1
AND ? >= START_DATE
AND ? < END_DATE
这就是一个典型的左闭右开区间的拉链表的查询方法，这类拉链表的START_DATE是这条记录生效的时间，而END_DATE与下一条数据的START_DATE完全相同。
并非所有的拉链表都是如此设计的，有双闭的，查询时START_DATE和END_DATE都要带上“=”，当然出于业务需要，也有双开，或者左开右闭的，但其原理都是一样的，只是要注意查询时“=”的位置。
２． 编辑
拉链表的编辑操作有三个：开链，关链，关链-开链。
开链就是第一次插入数据，这条数据没有之前的记录与之对应，只需要设定START_DATE并将END_DATE置为很久以后(比如9999年12月31日)的日期即可。
关链就是设置整条链的结尾，将END_DATE置为一个有业务意义的日期(比如三天前或一个月后都可以)即可。
关链-开链，这是我随意起的，关于这个操作，我并不知道有什么学术名字，只是突然想起了现在比较流行的前端架构MVVM(Model View ViewModel)，因此起了这个名。是改变原有数据的值并将其延续下去的操作，需要先找到上一条记录，将其结束时间置为本条记录的开始时间(关链)，再执行开链操作。
高级操作
由于某些特殊的业务需要，或为了方便查询，或因为历史遗留数据，常常造成拉链表的数据太单一或拉链表的数据太多，这时可能会需要对拉链表进行合并或拆分。这种操作有时并不是必须的，但是通过对这种操作的研究可以很好的锻炼我们SQL的运用技巧，不妨尝试一下。
1. 合并
表：LINK_TABLE
ID
DATA
START_DATE
END_DATE
1
X
2017-10-01
2017-11-30
1
Y
2017-11-30
2018-02-01
2
Z
2017-10-03
9999-12-31
比如我们现在又有了一张表：LINK_TABLE(数据为大写字母)，结合原来的LINK_DEMO(数据为小写字母)，现在我们要查ID为1数据为X和y的START_DATE和END_DATE，或者更直接一点，我要查出所有ID的所有属性组合的起止时间。那么经过对表格的观察，我们可以画出如下的一个数轴。
image.png
根据数轴可以分析出最终结果应该是：
表：LINK_COMBINE
ID
DATA1
DATA2
START_DATE
END_DATE
1
X
2017-10-01
2017-10-31
1
x
X
2017-10-31
2017-11-15
1
y
X
2017-11-15
2017-11-30
1
y
Y
2017-11-30
2018-02-01
1
y
2018-02-01
2018-03-02
2
Z
2017-10-03
2017-12-03
2
z
Z
2017-12-03
9999-12-31
那么SQL应该怎样写呢？我们大概能分析出来：
两个表是平等的，没有主次的，所以关联条件必然对称。
观察数轴得出，关联之后，大的START_DATE会覆盖小的START_DATE，小的END_DATE会覆盖大的END_DATE。
两张表的START_DATE和END_DATE没有相等的值，所以关联条件会有不等式。
由1和3我们可以确定出关联条件，大约就是ID相等，START_DATE小于另一个表的END_DATE。由2我们可以得出我们SELECT的字段中会有CASE WHEN THEN语句。大概就能写出下面的SQL。
SELECT A.ID, A.DATA, B.DATA,
CASE WHEN A.START_DATE
CASE WHEN A.END_DATE>B.END_DATE THEN B.END_DATE ELSE A.END_DATE END
FROM LINK_DEMO A
JOIN LINK_TABLE B
ON A.ID=B.ID
AND (A.START_DATE < B.END_DATE OR B.START_DATE < A.END_DATE);
经过运行SQL发现，这个SQL几乎查出了与我们预期的结果LINK_COMBINE一样的数据，但是仍然不是我们预期的数据，两张表中有一张表没值的时间段没有查出来，好在大多数业务需求到这种程度已经可以满足了，有兴趣继续思考的朋友可以再想想如何得出与我们预期的表格LINK_COMBINE一模一样的结果。
拆分
拆分是合并的逆操作，就是将一个存了多个属性的拉链表拆成多个含有少量属性的拉链表。比如我们现在已经有这张LINK_COMBINE表了，我们想将它拆成LINK_DEMO和LINK_TABLE，如何完成呢？看上去很简单，好像只要GROUP BY一下就可以了。
SELECT ID, DATA1, MIN(START_DATE), MAX(END_DATE)
FROM LINK_COMBINE
GROUP BY ID,DATA1;
我们貌似通过这样一条SQL就从LINK_COMBINE中拆出了LINK_DEMO，但这其实是因为这张表数据简单，xy和空没有交叉出现，而一旦有这种情况，这个SQL查出来的数据就不对了，因此我们必须在这个基础上继续分析解决方案。
我们先将LINK_COMBINE的数据复杂化得到下表：
表：LINK_MULTIPLE
ID
DATA1
DATA2
START_DATE
END_DATE
1
X
2017-10-01
2017-10-31
1
x
X
2017-10-31
2017-11-15
1
y
X
2017-11-15
2017-11-30
1
x
Y
2017-11-30
2018-02-01
1
y
2018-02-01
2018-03-01
1
y
X
2018-03-01
2018-03-02
2
Z
2017-10-03
2017-12-03
2
z
Z
2017-12-03
9999-12-31
其实我们也没怎么大改，只是将第四行的y变成了x再执行上面的SQL无疑就得出了完全错误的结果集。经过分析，上面SQL在上面可以正常使用的原因是因为xy没有交叉出现，而第四行x的出现，打断了第三行y和第五行y的连续性，它们已经不能再被分到同一个分组了。我们还将原来第五行的y拆成了两行，如果要正确查出结果，这两个显然是要分到同一组的。
基于这些分析，我们可以设想，构造一个字段，这个字段就是用来区分连续出现的y和其他y。比如将表构造成这个样子：
ID
DATA1
CONS_FIELD
START_DATE
END_DATE
1
1
2017-10-01
2017-10-31
1
x
1
2017-10-31
2017-11-15
1
y
1
2017-11-15
2017-11-30
1
x
2
2017-11-30
2018-02-01
1
y
2
2018-02-01
2018-03-01
1
y
2
2018-03-01
2018-03-02
2
1
2017-10-03
2017-12-03
2
z
1
2017-12-03
9999-12-31
因为我们是要拆分，所以不必同时关心DATA1和DATA2，只要研究好一个，另一个就可以同理可证。我们看到这个加了构造字段的表可以完美的符合我们的要求，但是这个构造字段从何而来呢？我们其实是用ID,DATA1这个组合出现的次数构造出的这个字段，这个字段会增长，我们当然就想到了序列。正好Oracle的序列有两个用法，一个是CURRVAL和NEXTVAL，我们只需要控制在某种情况下用CURRVAL，而在另外的情况下用NEXTVAL即可。但是我们构造的字段里，空xy应该分别是一个序列，这里只有三个值，三个序列或许还可以接受，但是如果有几百上千个值呢？难道我们创建几千个序列就为干这个？当然不是，经过观察我们又发现了，一个序列就可以搞定，前三行的值可以一样，也可以不一样，只要保证第二行和第四行不一样，第三行跟第五六行不一样就行了。所以只要与前一条数据的值一样就用CURRVAL，只要与前一条数据不一样就用NEXTVAL，就可以了。为了获取前一条数据我们需要用到Oracle的LAG函数(与LAG相对应的是LEAD函数，有兴趣的朋友可以自行查阅一下两个函数的用法)，所以不能跨数据库通用。SQL如下：
SELECT A.ID, A.DATA, CASE
WHEN LAG(A.DATA, 1, '')
OVER(PARTITION BY A.ID ORDER BY A.START_DATE) = A.DATA THEN
SELECT LINK_SEQ.CURRVAL FROM DUAL
ELSE
SELECT LINK_SEQ.NEXTVAL FROM DUAL
END CONS_FIELD,
A.START_DATE,
A.END_DATE
FROM LINK_MULTIPLE A;
这个版本是失败的。不知道为什么CURRVAL并没有正确地取到序列的当前值，我们可以用函数来解决这个问题，将取得序列值的SQL封闭成如下函数，CURRVAL就能如我们所愿地取值了。
CREATE OR REPLACE FUNCTION GETSEQ(SEQ IN NUMBER) RETURN NUMBER IS
RESULT NUMBER;
BEGIN
IF SEQ = 1 THEN
RESULT := CONTROL_SEQ.CURRVAL;
ELSE
RESULT := CONTROL_SEQ.NEXTVAL;
END IF;
RETURN RESULT;
END GETSEQ;
有了这个函数，我们的SQL也相应改为了：
SELECT A.ID, A.DATA, CASE
WHEN LAG(A.DATA, 1, '')
OVER(PARTITION BY A.ID ORDER BY A.START_DATE) = A.DATA THEN
GETSEQ(1)
ELSE
GETSEQ(0)
END CONS_FIELD,
A.START_DATE,
A.END_DATE
FROM LINK_MULTIPLE A;
这样我们就构造出了一个可以用GROUP BY拆分出来的表，将这个表作为子查询即可完成拆分。
我们掌握了合并和拆分之后我们就可以更合理地设计拉链表。拉链表存的属性越少则冗余越少，但联表查询势必造成效率降低，因此在设计表时要权衡利弊，将一些经常一起使用的属性放到一个表中是合理的。而过多的将属性集成到一个大而全的拉链表，不但冗余极大，并且从业务角度讲很多情况都是不可实现的。
扫一扫
2786
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
