觀察一些大型用戶(hù)的 BI 系統(tǒng)，經(jīng)常會(huì)發(fā)現(xiàn)數(shù)據(jù)倉(cāng)庫(kù)中有很多快照表。如某交易業(yè)務(wù)的 BI 系統(tǒng)，交易明細(xì)表很大，被按月存儲(chǔ)成多個(gè)分段表。還有一些相對(duì)不太大的表，計(jì)算時(shí)要和交易明細(xì)表關(guān)聯(lián)，比如客戶(hù)表、雇員表、商品表等等。每個(gè)月底，這些表的完整數(shù)據(jù)都會(huì)被存儲(chǔ)成快照表，用于匹配當(dāng)月的交易明細(xì)分段表。

為什么會(huì)有這么多看似很冗余的快照表存在呢？

這個(gè)交易明細(xì)表就是常說(shuō)的事實(shí)表，是用來(lái)存儲(chǔ)發(fā)生事件的數(shù)據(jù)表，數(shù)據(jù)量會(huì)隨著時(shí)間不斷增長(zhǎng)。除了交易明細(xì)表之外，訂單表、保險(xiǎn)單表、銀行帳戶(hù)存取記錄表等也都是事實(shí)表。事實(shí)表中會(huì)有一些代碼字段和其它表關(guān)聯(lián)，比如上面說(shuō)的交易明細(xì)表通過(guò)客戶(hù)號(hào)、雇員號(hào)、商品號(hào)分別關(guān)聯(lián)客戶(hù)表、雇員表和商品表。再比如訂單表通過(guò)產(chǎn)品號(hào)字段關(guān)聯(lián)產(chǎn)品表、銀行帳戶(hù)存取記錄表通過(guò)帳號(hào)字段關(guān)聯(lián)帳戶(hù)表等等。這些和事實(shí)表關(guān)聯(lián)的表稱(chēng)為維表，下圖中的交易明細(xì)表與客戶(hù)表就構(gòu)成了事實(shí)表和維表的關(guān)聯(lián)關(guān)系：

事實(shí)表關(guān)聯(lián)維表的目的，是需要用維表的字段參與計(jì)算。例如交易明細(xì)表與客戶(hù)表關(guān)聯(lián)后，就可以按照客戶(hù)所在城市來(lái)分組匯總交易金額、交易筆數(shù)等。

維表的數(shù)據(jù)相對(duì)比較固定，但仍然也會(huì)有修改。維表數(shù)據(jù)變動(dòng)后，事實(shí)表中新產(chǎn)生的數(shù)據(jù)不受影響。而事實(shí)表以前的歷史數(shù)據(jù)則可能和維表的新數(shù)據(jù)不匹配。這時(shí)，如果用事實(shí)表的老數(shù)據(jù)，去關(guān)聯(lián)維表的新數(shù)據(jù)，就會(huì)出現(xiàn)錯(cuò)誤的情況。

例如，編號(hào)為 B20101 的客戶(hù) James，原先在紐約居住，產(chǎn)生了一些交易記錄。到了 2020 年 5 月 15 日，James 搬家到芝加哥，又產(chǎn)生了一些新的交易明細(xì)記錄。如果我們直接將客戶(hù)表中 James 的城市改成芝加哥，那么按照客戶(hù)城市分組匯總交易金額時(shí)，James 以前在紐約的交易也會(huì)被算到芝加哥，這明顯是不對(duì)的。之所以出錯(cuò)，是因?yàn)?James 的交易記錄到底算哪個(gè)城市和時(shí)間有關(guān)，一律算成紐約或者芝加哥都是不對(duì)的。

如果不對(duì)這個(gè)問(wèn)題加以特別處理，就會(huì)導(dǎo)致 BI 系統(tǒng)中（針對(duì)歷史數(shù)據(jù)）的統(tǒng)計(jì)值和 ERP 系統(tǒng)中（針對(duì)當(dāng)時(shí)的數(shù)據(jù)）的統(tǒng)計(jì)值對(duì)不上的現(xiàn)象，而且這種錯(cuò)誤還很難排查。

快照表就是為了解決這種問(wèn)題而產(chǎn)生的。定時(shí)（比如每月末）生成有關(guān)數(shù)據(jù)表的快照，保存事實(shí)表某段時(shí)間（比如一個(gè)月）的數(shù)據(jù)和維表當(dāng)時(shí)的完整數(shù)據(jù)，供后續(xù)統(tǒng)計(jì)分析計(jì)算，這樣就會(huì)保證事實(shí)表總是和同期的維表關(guān)聯(lián)，統(tǒng)計(jì)結(jié)果就不會(huì)出錯(cuò)了。

但是，這就會(huì)造成很多冗余的維表數(shù)據(jù)，增加數(shù)據(jù)庫(kù)的存儲(chǔ)量；維表通常還會(huì)有多個(gè)，每個(gè)維表又有多個(gè)快照表，會(huì)導(dǎo)致表間關(guān)系變得異常復(fù)雜，大大增加系統(tǒng)的復(fù)雜度。

這樣，還會(huì)導(dǎo)致計(jì)算代碼也隨之變得復(fù)雜。比如說(shuō)，每個(gè)月都有一個(gè)交易明細(xì)表及其對(duì)應(yīng)的一批維表快照，如果要統(tǒng)計(jì)一年中按客戶(hù)所在城市分組的交易金額和筆數(shù)，就要將十二個(gè)月的交易明細(xì)表和各自的維表快照關(guān)聯(lián)后，再做十一次 UNION。這還僅僅是簡(jiǎn)單的分組匯總，再?gòu)?fù)雜一些的統(tǒng)計(jì)分析計(jì)算，會(huì)出現(xiàn)非常長(zhǎng)且復(fù)雜的 SQL 語(yǔ)句。維護(hù)都很困難，更不用說(shuō)性能調(diào)優(yōu)了。為此，很多采用快照方案的 BI 系統(tǒng)都禁止較長(zhǎng)時(shí)間范圍的統(tǒng)計(jì)分析計(jì)算，嚴(yán)重時(shí)只能選擇一個(gè)時(shí)間周期（一個(gè)月）的數(shù)據(jù)做計(jì)算。

而且，快照方案也沒(méi)有徹底解決維表變動(dòng)帶來(lái)的查詢(xún)不準(zhǔn)確問(wèn)題。BI 系統(tǒng)不可能在維度發(fā)生變動(dòng)時(shí)就立即生成一個(gè)快照（那樣會(huì)有太多的快照而造成巨大的存儲(chǔ)量），一般只會(huì)定期生成快照表。這樣，兩次生成快照的時(shí)間點(diǎn)之間，如果發(fā)生維表數(shù)據(jù)的變動(dòng)，仍然會(huì)出現(xiàn)計(jì)算錯(cuò)誤。假設(shè)每個(gè)月最后一天生成交易明細(xì)表和客戶(hù)表的快照。James 是 5 月 15 日搬家的，在 5 月 31 日生成快照的時(shí)候，James 的城市會(huì)被保存為芝加哥。而 6 月 1 日之后就要基于這個(gè)快照做 5 月的查詢(xún)統(tǒng)計(jì)，那么 5 月 1 日到 15 日這段時(shí)間 James 的交易本來(lái)應(yīng)該算紐約的，現(xiàn)在也都算成芝加哥的了，還是會(huì)出現(xiàn)錯(cuò)誤，只是錯(cuò)誤量相對(duì)較小而已。

還有一種變通辦法是用事實(shí)表和維表生成寬表。將交易明細(xì)表和客戶(hù)表數(shù)據(jù)關(guān)聯(lián)好，生成交易寬表，這個(gè)寬表中的客戶(hù)姓名、城市等就不會(huì)受到客戶(hù)維表數(shù)據(jù)變動(dòng)的影響了，并且能保證系統(tǒng)的數(shù)據(jù)結(jié)構(gòu)相對(duì)簡(jiǎn)單。但是，寬表通常仍然只能是定期生成（實(shí)時(shí)生成寬表記錄會(huì)拖累交易系統(tǒng)的性能），也就仍然會(huì)有上述的在兩個(gè)生成時(shí)間點(diǎn)之間發(fā)生維表變化后導(dǎo)致的錯(cuò)誤。而且，由于事實(shí)表和維表是多對(duì)一關(guān)系，交易寬表中的客戶(hù)數(shù)據(jù)將出現(xiàn)大量冗余，造成事實(shí)表膨脹，空間占用會(huì)遠(yuǎn)遠(yuǎn)超過(guò)快照方案。再者，寬表結(jié)構(gòu)維護(hù)很不靈活，特別是需要增加字段時(shí)還要考慮大量歷史數(shù)據(jù)的處理。這就要求建立寬表時(shí)盡量將字段添加完全，而大而全的寬表占用的空間會(huì)更大。

其實(shí)，維表數(shù)據(jù)雖然有變動(dòng)，但會(huì)相對(duì)很少，變動(dòng)量與總數(shù)據(jù)量相比通常會(huì)少一個(gè)到幾個(gè)數(shù)量級(jí)。利用這個(gè)特征，我們可以設(shè)計(jì)更低成本的方法來(lái)解決這個(gè)問(wèn)題。

開(kāi)源數(shù)據(jù)計(jì)算引擎 SPL 提供的時(shí)間鍵機(jī)制，就利用了這個(gè)特征，可以便捷、準(zhǔn)確地解決維表數(shù)據(jù)變動(dòng)問(wèn)題。

具體的做法是，在維表中增加一個(gè)時(shí)間字段，和原有主鍵一起組成聯(lián)合主鍵，這個(gè)字段稱(chēng)為時(shí)間鍵。事實(shí)表和維表關(guān)聯(lián)時(shí)，用原來(lái)的外鍵字段加上合適的時(shí)間字段，與新維表的聯(lián)合主鍵關(guān)聯(lián)。時(shí)間鍵的關(guān)聯(lián)方式和原來(lái)的外鍵有所不同，并不是用“相等”的關(guān)系判斷的，而是找“指定時(shí)間前的最新記錄”來(lái)關(guān)聯(lián)。

仍以上述交易明細(xì)表和客戶(hù)表為例，后者要增加一個(gè)生效時(shí)間字段 edate，如下圖：

edate 存儲(chǔ)的是這條記錄是什么時(shí)候生效的，也就是維表發(fā)生變動(dòng)的時(shí)間。比如客戶(hù) James 搬家后，客戶(hù)表就會(huì)變成下圖這樣：

圖中，客戶(hù) James 搬家前只有一條維表記錄 i。而搬家當(dāng)天新增了第 ii 條記錄，生效日期是搬家的時(shí)間 2020-05-15。

這時(shí)，SPL 做交易明細(xì)表和客戶(hù)表關(guān)聯(lián)，除了比較 cid 和 id 是否相等，還要比較交易時(shí)間 ddate 和客戶(hù)記錄生效時(shí)間 edate，找到 edate 不大于 ddate 的最大值，其所在記錄才是對(duì)應(yīng)的關(guān)聯(lián)記錄（也就是這個(gè)時(shí)間點(diǎn)之前的最新記錄）。這樣，James 搬家前的交易記錄日期是早于 2020-05-15 的，會(huì)和客戶(hù)表中生效日期為 2017-02-01 的記錄 i 關(guān)聯(lián)，所以這些交易明細(xì)會(huì)被算作紐約的。而 James 搬家后的交易記錄日期等于或晚于 2020-05-15，就會(huì)和客戶(hù)表中生效日期為 2020-05-15 的記錄 ii 關(guān)聯(lián)，這些交易明細(xì)就會(huì)被算作是芝加哥的。

可以看到，采用時(shí)間鍵機(jī)制后的關(guān)聯(lián)結(jié)果是符合實(shí)際情況的。這是因?yàn)?，我們是在維表發(fā)生數(shù)據(jù)變動(dòng)的當(dāng)時(shí)，增加維表記錄并存入生效時(shí)間，所以可以保證后續(xù)計(jì)算的正確性。這樣，就可以避免定期生成快照或者寬表時(shí)存在的問(wèn)題，不會(huì)出現(xiàn)兩次生成時(shí)間點(diǎn)之間的計(jì)算錯(cuò)誤現(xiàn)象。

而且，因?yàn)榫S表的變動(dòng)量很小，增加了變動(dòng)信息的維表和原來(lái)的維表規(guī)?；旧鲜且粯拥?，并不會(huì)大幅加大存儲(chǔ)量。

理論上，也可以在關(guān)系數(shù)據(jù)庫(kù)的維表中增加類(lèi)似的時(shí)間字段，但是卻沒(méi)辦法表示這種關(guān)聯(lián)關(guān)系。時(shí)間鍵的關(guān)聯(lián)顯然不是常規(guī)的等值 JOIN，使用非等值 JOIN 也要用復(fù)雜的子查詢(xún)選出最新的維表記錄再來(lái)關(guān)聯(lián)，語(yǔ)句很復(fù)雜，也很難保證執(zhí)行性能。所以，在關(guān)系數(shù)據(jù)庫(kù)中，就只能用快照或?qū)挶淼确桨竵?lái)解決了。

SPL 實(shí)現(xiàn)時(shí)間鍵機(jī)制的代碼也很簡(jiǎn)單，大致是下面這樣：

A1 讀入客戶(hù)表，B1 定義聯(lián)合主鍵 id 和 edate，@s 選項(xiàng)就表示主鍵的最后一個(gè)字段是時(shí)間鍵。如果業(yè)務(wù)需要，也可以用精度更高的日期時(shí)間類(lèi)型字段作為時(shí)間鍵。

A2 建立交易明細(xì)表的游標(biāo)。

B2 將游標(biāo)和 A1 中的客戶(hù)表關(guān)聯(lián)起來(lái)，明細(xì)表的關(guān)聯(lián)字段是 cid 和 ddate，客戶(hù)表是主鍵。使用方式和普通沒(méi)有時(shí)間鍵的維表是一樣的。

A3 用關(guān)聯(lián)的結(jié)果游標(biāo)按照客戶(hù)所在城市分組匯總交易金額和交易筆數(shù)，這時(shí)候就不必再關(guān)心時(shí)間鍵了。

SPL 內(nèi)置了時(shí)間鍵處理機(jī)制，運(yùn)算性能和沒(méi)有時(shí)間鍵的維表差別很小。關(guān)聯(lián)時(shí)和普通維表一樣，可以隨意選定時(shí)間區(qū)間進(jìn)行統(tǒng)計(jì)，不存在快照表那種難以跨越時(shí)間周期的問(wèn)題。

SPL 提供的時(shí)間鍵可以很簡(jiǎn)便地解決維表數(shù)據(jù)變動(dòng)問(wèn)題。事實(shí)表保持原狀，只在維表中增加時(shí)間字段，并記錄變動(dòng)情況即可。可以在保證統(tǒng)計(jì)結(jié)果準(zhǔn)確和計(jì)算性能的前提下，避免保留大量的快照表，降低系統(tǒng)復(fù)雜度；也可以避免寬表的大量數(shù)據(jù)冗余，保持靈活的系統(tǒng)結(jié)構(gòu)。

SPL下載地址：http://c.raqsoft.com.cn/article/1595816810031

SPL開(kāi)源地址：https://github.com/SPLWare/esProc