拉链表的操作技巧——拆分与合并

96
码语者
0.1 2018.05.22 21:45 字数 2392

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

定义

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

关键点:

  • 储存开始时间和结束时间。
  • 开始时间和结束时间首尾相接,形成链式结构。

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

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

基本操作

1. 查询
对于表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都要带上“=”,当然出于业务需要,也有双开,或者左开右闭的,但其原理都是一样的,只是要注意查询时“=”的位置。

2. 编辑

拉链表的编辑操作有三个:开链,关链,关链-开链。

开链就是第一次插入数据,这条数据没有之前的记录与之对应,只需要设定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应该怎样写呢?我们大概能分析出来:

  1. 两个表是平等的,没有主次的,所以关联条件必然对称。
  2. 观察数轴得出,关联之后,大的START_DATE会覆盖小的START_DATE,小的END_DATE会覆盖大的END_DATE。
  3. 两张表的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<B.START_DATE THEN B.START_DATE ELSE A.START_DATE END,
       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拆分出来的表,将这个表作为子查询即可完成拆分。

我们掌握了合并和拆分之后我们就可以更合理地设计拉链表。拉链表存的属性越少则冗余越少,但联表查询势必造成效率降低,因此在设计表时要权衡利弊,将一些经常一起使用的属性放到一个表中是合理的。而过多的将属性集成到一个大而全的拉链表,不但冗余极大,并且从业务角度讲很多情况都是不可实现的。

随笔
Web note ad 1