本文主要是結(jié)合我平時遇到的一些問題，分析一下Oracle數(shù)據(jù)庫中拉鏈表的一些非常規(guī)操作，即將多個拉鏈表合并和將有多個屬性的拉鏈表拆分。為此我將從拉鏈表的設(shè)計思路和常規(guī)操作，開始逐漸分析。

定義

拉鏈表是一種數(shù)據(jù)庫設(shè)計模式，類似于面向?qū)ο蟮?3種設(shè)計模式，可以直接套用，經(jīng)常用于儲存歷史數(shù)據(jù)和分析時間維度的數(shù)據(jù)。

關(guān)鍵點(diǎn)：

• 儲存開始時間和結(jié)束時間。
• 開始時間和結(jié)束時間首尾相接，形成鏈?zhǔn)浇Y(jié)構(gòu)。

拉鏈表與LinkedList很相似，都以快速找到下一個元素為設(shè)計目標(biāo)，只不過鏈表會存儲內(nèi)存地址，而拉鏈表只存了下一條數(shù)據(jù)的開始時間。理論上如果存了下一條數(shù)據(jù)的id也是可以的。

拉鏈表一般用于解決歷史版本查詢的問題，也可用于解決數(shù)值區(qū)間問題，查詢效率高，占用空間小。

基本操作

１． 查詢
對于表LINK_DEMO

比如我要查2018-01-01時刻的ID為1的記錄數(shù)據(jù)是多少，那么SQL就是：

SELECT * FROM LINK_DEMO

 WHERE ID = 1

   AND ? >= START_DATE

   AND ? < END_DATE

這就是一個典型的左閉右開區(qū)間的拉鏈表的查詢方法，這類拉鏈表的START_DATE是這條記錄生效的時間，而END_DATE與下一條數(shù)據(jù)的START_DATE完全相同。

并非所有的拉鏈表都是如此設(shè)計的，有雙閉的，查詢時START_DATE和END_DATE都要帶上“=”，當(dāng)然出于業(yè)務(wù)需要，也有雙開，或者左開右閉的，但其原理都是一樣的，只是要注意查詢時“=”的位置。

２． 編輯

拉鏈表的編輯操作有三個：開鏈，關(guān)鏈，關(guān)鏈-開鏈。

開鏈就是第一次插入數(shù)據(jù)，這條數(shù)據(jù)沒有之前的記錄與之對應(yīng)，只需要設(shè)定START_DATE并將END_DATE置為很久以后（比如9999年12月31日）的日期即可。

關(guān)鏈就是設(shè)置整條鏈的結(jié)尾，將END_DATE置為一個有業(yè)務(wù)意義的日期（比如三天前或一個月后都可以）即可。

關(guān)鏈-開鏈，這是我隨意起的，關(guān)于這個操作，我并不知道有什么學(xué)術(shù)名字，只是突然想起了現(xiàn)在比較流行的前端架構(gòu)MVVM（Model View ViewModel），因此起了這個名。是改變原有數(shù)據(jù)的值并將其延續(xù)下去的操作，需要先找到上一條記錄，將其結(jié)束時間置為本條記錄的開始時間（關(guān)鏈），再執(zhí)行開鏈操作。

高級操作

由于某些特殊的業(yè)務(wù)需要，或為了方便查詢，或因為歷史遺留數(shù)據(jù)，常常造成拉鏈表的數(shù)據(jù)太單一或拉鏈表的數(shù)據(jù)太多，這時可能會需要對拉鏈表進(jìn)行合并或拆分。這種操作有時并不是必須的，但是通過對這種操作的研究可以很好的鍛煉我們SQL的運(yùn)用技巧，不妨嘗試一下。

1. 合并

比如我們現(xiàn)在又有了一張表：LINK_TABLE（數(shù)據(jù)為大寫字母），結(jié)合原來的LINK_DEMO（數(shù)據(jù)為小寫字母），現(xiàn)在我們要查ID為1數(shù)據(jù)為X和y的START_DATE和END_DATE，或者更直接一點(diǎn)，我要查出所有ID的所有屬性組合的起止時間。那么經(jīng)過對表格的觀察，我們可以畫出如下的一個數(shù)軸。

image.png

根據(jù)數(shù)軸可以分析出最終結(jié)果應(yīng)該是：

那么SQL應(yīng)該怎樣寫呢？我們大概能分析出來：

1. 兩個表是平等的，沒有主次的，所以關(guān)聯(lián)條件必然對稱。
2. 觀察數(shù)軸得出，關(guān)聯(lián)之后，大的START_DATE會覆蓋小的START_DATE，小的END_DATE會覆蓋大的END_DATE。
3. 兩張表的START_DATE和END_DATE沒有相等的值，所以關(guān)聯(lián)條件會有不等式。

由1和3我們可以確定出關(guān)聯(lián)條件，大約就是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);

經(jīng)過運(yùn)行SQL發(fā)現(xiàn)，這個SQL幾乎查出了與我們預(yù)期的結(jié)果LINK_COMBINE一樣的數(shù)據(jù)，但是仍然不是我們預(yù)期的數(shù)據(jù)，兩張表中有一張表沒值的時間段沒有查出來，好在大多數(shù)業(yè)務(wù)需求到這種程度已經(jīng)可以滿足了，有興趣繼續(xù)思考的朋友可以再想想如何得出與我們預(yù)期的表格LINK_COMBINE一模一樣的結(jié)果。

拆分

拆分是合并的逆操作，就是將一個存了多個屬性的拉鏈表拆成多個含有少量屬性的拉鏈表。比如我們現(xiàn)在已經(jīng)有這張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，但這其實是因為這張表數(shù)據(jù)簡單，xy和空沒有交叉出現(xiàn)，而一旦有這種情況，這個SQL查出來的數(shù)據(jù)就不對了，因此我們必須在這個基礎(chǔ)上繼續(xù)分析解決方案。
我們先將LINK_COMBINE的數(shù)據(jù)復(fù)雜化得到下表：

其實我們也沒怎么大改，只是將第四行的y變成了x再執(zhí)行上面的SQL無疑就得出了完全錯誤的結(jié)果集。經(jīng)過分析，上面SQL在上面可以正常使用的原因是因為xy沒有交叉出現(xiàn)，而第四行x的出現(xiàn)，打斷了第三行y和第五行y的連續(xù)性，它們已經(jīng)不能再被分到同一個分組了。我們還將原來第五行的y拆成了兩行，如果要正確查出結(jié)果，這兩個顯然是要分到同一組的。
基于這些分析，我們可以設(shè)想，構(gòu)造一個字段，這個字段就是用來區(qū)分連續(xù)出現(xiàn)的y和其他y。比如將表構(gòu)造成這個樣子：

因為我們是要拆分，所以不必同時關(guān)心DATA1和DATA2，只要研究好一個，另一個就可以同理可證。我們看到這個加了構(gòu)造字段的表可以完美的符合我們的要求，但是這個構(gòu)造字段從何而來呢？我們其實是用ID,DATA1這個組合出現(xiàn)的次數(shù)構(gòu)造出的這個字段，這個字段會增長，我們當(dāng)然就想到了序列。正好Oracle的序列有兩個用法，一個是CURRVAL和NEXTVAL，我們只需要控制在某種情況下用CURRVAL，而在另外的情況下用NEXTVAL即可。但是我們構(gòu)造的字段里，空xy應(yīng)該分別是一個序列，這里只有三個值，三個序列或許還可以接受，但是如果有幾百上千個值呢？難道我們創(chuàng)建幾千個序列就為干這個？當(dāng)然不是，經(jīng)過觀察我們又發(fā)現(xiàn)了，一個序列就可以搞定，前三行的值可以一樣，也可以不一樣，只要保證第二行和第四行不一樣，第三行跟第五六行不一樣就行了。所以只要與前一條數(shù)據(jù)的值一樣就用CURRVAL，只要與前一條數(shù)據(jù)不一樣就用NEXTVAL，就可以了。為了獲取前一條數(shù)據(jù)我們需要用到Oracle的LAG函數(shù)（與LAG相對應(yīng)的是LEAD函數(shù)，有興趣的朋友可以自行查閱一下兩個函數(shù)的用法），所以不能跨數(shù)據(jù)庫通用。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并沒有正確地取到序列的當(dāng)前值，我們可以用函數(shù)來解決這個問題，將取得序列值的SQL封閉成如下函數(shù)，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;

有了這個函數(shù)，我們的SQL也相應(yīng)改為了：

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;

這樣我們就構(gòu)造出了一個可以用GROUP BY拆分出來的表，將這個表作為子查詢即可完成拆分。

我們掌握了合并和拆分之后我們就可以更合理地設(shè)計拉鏈表。拉鏈表存的屬性越少則冗余越少，但聯(lián)表查詢勢必造成效率降低，因此在設(shè)計表時要權(quán)衡利弊，將一些經(jīng)常一起使用的屬性放到一個表中是合理的。而過多的將屬性集成到一個大而全的拉鏈表，不但冗余極大，并且從業(yè)務(wù)角度講很多情況都是不可實現(xiàn)的。