oracle拉链表是什么,拉链表的操作技巧——拆分与合并

骑猪看日落 2023-01-18 06:41 49阅读 0赞

本文主要是结合我平时遇到的一些问题，分析一下Oracle数据库中拉链表的一些非常规操作，即将多个拉链表合并和将有多个属性的拉链表拆分。为此我将从拉链表的设计思路和常规操作，开始逐渐分析。

定义

拉链表是一种数据库设计模式，类似于面向对象的23种设计模式，可以直接套用，经常用于储存历史数据和分析时间维度的数据。

关键点：

储存开始时间和结束时间。

开始时间和结束时间首尾相接，形成链式结构。

拉链表与LinkedList很相似，都以快速找到下一个元素为设计目标，只不过链表会存储内存地址，而拉链表只存了下一条数据的开始时间。理论上如果存了下一条数据的id也是可以的。

拉链表一般用于解决历史版本查询的问题，也可用于解决数值区间问题，查询效率高，占用空间小。

基本操作

１． 查询

对于表LINK\_DEMO

表：LINK\_DEMO

DATA

START\_DATE

END\_DATE

2017-10-31

2017-11-15

2018-03-02

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

DATA

START\_DATE

END\_DATE

2017-10-01

2017-11-30

2018-02-01

2017-10-03

9999-12-31

比如我们现在又有了一张表：LINK\_TABLE(数据为大写字母)，结合原来的LINK\_DEMO(数据为小写字母)，现在我们要查ID为1数据为X和y的START\_DATE和END\_DATE，或者更直接一点，我要查出所有ID的所有属性组合的起止时间。那么经过对表格的观察，我们可以画出如下的一个数轴。

![90f20753271a][]

image.png

根据数轴可以分析出最终结果应该是：

表：LINK\_COMBINE

DATA1

DATA2

START\_DATE

END\_DATE

2017-10-01

2017-10-31

2017-11-15

2017-11-30

2018-02-01

2018-03-02

2017-10-03

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

DATA1

DATA2

START\_DATE

END\_DATE

2017-10-01

2017-10-31

2017-11-15

2017-11-30

2018-02-01

2018-03-01

2018-03-02

2017-10-03

2017-12-03

9999-12-31

其实我们也没怎么大改，只是将第四行的y变成了x再执行上面的SQL无疑就得出了完全错误的结果集。经过分析，上面SQL在上面可以正常使用的原因是因为xy没有交叉出现，而第四行x的出现，打断了第三行y和第五行y的连续性，它们已经不能再被分到同一个分组了。我们还将原来第五行的y拆成了两行，如果要正确查出结果，这两个显然是要分到同一组的。

基于这些分析，我们可以设想，构造一个字段，这个字段就是用来区分连续出现的y和其他y。比如将表构造成这个样子：

DATA1

CONS\_FIELD

START\_DATE

END\_DATE

2017-10-01

2017-10-31

2017-11-15

2017-11-30

2018-02-01

2018-03-01

2018-03-02

2017-10-03

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