多層科目任意組合匯總報表的性能優(yōu)化(上)
一 問題背景
我們先來看一張資產(chǎn)負(fù)債表:
創(chuàng)新互聯(lián)建站專注于寧都網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗。 熱誠為您提供寧都營銷型網(wǎng)站建設(shè)，寧都網(wǎng)站制作、寧都網(wǎng)頁設(shè)計、寧都網(wǎng)站官網(wǎng)定制、微信小程序開發(fā)服務(wù)，打造寧都網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供寧都網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。
這是一個典型的中國式復(fù)雜報表格式，其復(fù)雜并不在于布局，而在于其中“期末余額”的每個單元格都是一個需要獨立計算的指標(biāo)，互相之間幾乎沒有關(guān)系，事實上就是一個各種指標(biāo)的匯總清單，而這些指標(biāo)往往會有上百個之多。
在源數(shù)據(jù)表結(jié)構(gòu)中，有一個字段稱為科目，其長度總是固定的 10 位，如：1234567890，如下圖：
科目字段的值實際上是一個分層的代碼，而前面表里上百個指標(biāo)就是根據(jù)需求對不同層次科目數(shù)據(jù)的統(tǒng)計結(jié)果，具體的做法是通過截取科目的前幾位來確定層次，然后按需求自由組合，作為條件進(jìn)行過濾，最后對金額字段進(jìn)行累計匯總。
比如計算指標(biāo) A 對應(yīng)的科目列表是 [1001，1002]，代表累計所有前 4 位是 1001、1002 的科目，用 SQL 寫出來就是：select sum(金額 )from T1 where concat( 年, 月)<=? and (left( 科目,4)="1001" or left(科目,4)="1002")
其中年、月是公共過濾條件，代表統(tǒng)計的時間范圍。
類似的，如果另一個指標(biāo) B 對應(yīng)的科目為 [2702,153102,12310105], 那就代表對前 4 位是 2702、前 6 位是 153102、前 8 位是 12310105 的所有科目值進(jìn)行累計，用 SQL 寫出來就是：select sum(金額 )from T1 where concat( 年, 月)<=? and (left( 科目,4)="2702" or left(科目,6)="153102" or left(科目,8)="12310105")；
實際業(yè)務(wù)中，每個指標(biāo)對應(yīng)的科目數(shù)量不定，可能多達(dá) 10 個以上，而且就像指標(biāo) B 這樣，各個科目的層次也不盡相同。
在有了報表工具之后 (固定報表)，原則上這類格式復(fù)雜、指標(biāo)參數(shù)任意組合的報表需求并不難實現(xiàn)，只是原始數(shù)據(jù)量一大，查詢響應(yīng)就會非常慢，用戶體驗變差，當(dāng)多并發(fā)請求時，還會對正常業(yè)務(wù)產(chǎn)生影響。
二 開發(fā)和優(yōu)化過程
2.1 多次遍歷方案
最常見的開發(fā)思路，就是按前面說的計算方式，對報表的每個指標(biāo)都寫一句完整的 SQL 來計算，有 100 個指標(biāo)，就寫 100 個 SQL。
有些報表工具提供了函數(shù)，可以直接在單元格中執(zhí)行 SQL (比如 query/call 等)，單元格的表達(dá)式大概會是這樣：
= query("select sum(金額 )from T1 where concat( 年, 月)<=? and (left( 科目,4)='2702' or left(科目,6)='153102' or left(科目,8)='12310105')", concat(year,month))
如果是非多源報表工具，則可以借助外部程序數(shù)據(jù)源來實現(xiàn)，比如可以直接用集算器編寫以下腳本：
A | |
1 | =connect("demo") |
2 | =A1.query@1("select sum(金額 )from T1 where concat( 年, 月)<=? and (left( 科目,4)='1001' or left(科目,4)='1002')",concat(year,month)) |
3 | =A1.query@1("select sum(金額 )from T1 where concat( 年, 月)<=? and (left( 科目,4)='2702' or left(科目,6)='153102' or left(科目,8)='12310105')",concat(year,month)) |
… | … |
102 | >A1.close() |
103 | return [A2:A101] |
簡單說明一下：
A1：連接數(shù)據(jù)庫 demo
A2-A3：執(zhí)行指標(biāo) A 的查詢 SQL 和指標(biāo) B 的查詢 SQL；其中 query() 函數(shù)中 @1 選項代表查詢符合條件的第一條記錄, 返回成單值或序列（一個字段是單值，多個字段是序列）；這個例子是對金額匯總求和，所以返回單值。
A4-A101：假定有剩余的 98 個指標(biāo)，每個指標(biāo)的查詢 SQL 都類似于 A2、A3 的寫法
A102：關(guān)閉數(shù)據(jù)庫連接
A103：合并 A2-A101 每個格子的計算結(jié)果 (共計 100 個指標(biāo)值)，返回一個單列數(shù)據(jù)集，供報表工具使用。
不過，在這種思路下，無論直接在格中使用 SQL 還是在程序數(shù)據(jù)源中計算，實際上每計算一個指標(biāo)就得遍歷一次源數(shù)據(jù)；而每個指標(biāo)還對應(yīng)多個需要 AND 的條件，這些都會嚴(yán)重降低性能。
這種思路的優(yōu)點是簡單直接，看上去確實能夠?qū)崿F(xiàn)需求，開發(fā)過程也并不太難。在數(shù)據(jù)量不大的情況下，查詢也不會很慢，勉強(qiáng)還能接受。不過，隨著數(shù)據(jù)量越來越大，性能瓶頸就會隨之而來，到了一定程度后，就可能出現(xiàn)在關(guān)鍵時刻用戶無法及時獲得自己想要指標(biāo)的問題，最終只能放棄。
2.2 一次遍歷方案
上面“多次遍歷方案”的問題在于，無論如何，對源數(shù)據(jù)遍歷 100 次實在是太低效了，那么，我們有沒有辦法能少遍歷幾次呢? 能不能只做一次遍歷就把所有指標(biāo)都計算出來呢？
這種一次遍歷的思路確實是可以的，我們只需要把 SQL 中的 WHERE 條件拼到 SELECT 中就行了，比如前面說到的指標(biāo) A 和 B 可以寫成：
SELECT SUM(CASE WHEN (LEFT(科目,4)='1001' OR LEFT( 科目,4)='1002')THEN 金額 ELSE 0 END) 指標(biāo) A,
SUM(CASE WHEN (LEFT(科目,4)="2702" OR LEFT( 科目,6)="153102" OR LEFT(科目,8)="12310105")THEN 金額 ELSE 0 END) 指標(biāo) B,
…
FROM T1 WHERE CONCAT(年, 月 )<=?
但是，真要用這個思路來處理 100 個指標(biāo)，可以想見這個 SQL 會有多長，維護(hù)難度會有多大。為此，我們可以利用集算器的游標(biāo)來實現(xiàn)這個邏輯，適當(dāng)降低維護(hù)難度。
另外，這種方案下的遍歷，還是需要把整表數(shù)據(jù)讀出數(shù)據(jù)庫，而 JDBC 太慢，IO 時間很可能成為瓶頸。對于這個問題，我們注意到其中處理的都是不再變化的歷史數(shù)據(jù)，那么我們就可以把數(shù)據(jù)先搬出數(shù)據(jù)庫存成文件，然后用文件作為數(shù)據(jù)源，從而加快 IO 訪問。具體實現(xiàn)如下：
1、把數(shù)據(jù)搬出數(shù)據(jù)庫保存成文件，集算器的 SPL 腳本如下：
A | |
1 | =connect("demo") |
2 | =A1.cursor("select 科目, 年, 月, 金額 from T1") |
3 | =file("總賬憑證 -pre.btx") |
4 | >A3.export@b(A2) |
5 | >A1.close() |
A1：連接數(shù)據(jù)庫 demo
A2：根據(jù) sql 創(chuàng)建數(shù)據(jù)庫游標(biāo)返回
A3：集文件保存的位置
A4：導(dǎo)出整表數(shù)據(jù)并保存到文件中，其中 export()函數(shù)的 @b 選項代表寫入到集文件中，即總賬憑證 -pre.btx
A5：關(guān)閉數(shù)據(jù)庫連接
2、遍歷一次源數(shù)據(jù)，計算 100 個指標(biāo)，集算器的 SPL 腳本如下：
A | B | C | |
1 | =file("總賬憑證 -pre.btx") | /指標(biāo)參數(shù)列 | |
2 | =A1.cursor@b() | ||
3 | =A2.select(concat(年, 月 )<=concat(year,month)) | ||
4 | for A3,10000 | ||
5 | =@+A4.select(C5.contain(科目 \1000000)).sum( 金額) | [1001,1002] | |
6 | =@+A4.select(C6.contain(科目 \1000000)||C6.contain( 科目 \10000)||C6.contain(科目 \100)).sum(金額) | [2702,153102,12310105] | |
… | … | … | |
105 | return [B5:B104] | ||
值得注意的是：這個例子引入了一個新的寫法，100 個指標(biāo)參數(shù)可以統(tǒng)一寫到 C 列上，當(dāng) B 列每計算一個指標(biāo)時，直接引用 C 列當(dāng)前行的所對應(yīng)的參數(shù)即可。比如：
C5：指標(biāo) A 的參數(shù)條件 (按科目號前 4 位截取的多個值形成的集合)
C6：指標(biāo) B 的參數(shù)條件 (按科目號前 4 位 / 前 6 位 / 前 8 位截取的多個值，形成的參數(shù)集合)
剩余的 98 個指標(biāo)，計算的寫法類似 B6，參數(shù)的寫法類似 C6，依次類推到 100。
顯然，這種計算邏輯和參數(shù)分離的寫法，能夠極大地提高可維護(hù)性。
下面我們完整地分析一下這段腳本：
A1：打開預(yù)處理前的原始數(shù)據(jù)表的集文件對象
A2：根據(jù)文件創(chuàng)建游標(biāo)返回，其中 cursor() 函數(shù)使用 @b 選項代表從集文件中讀取。
A3：在 A2 的基礎(chǔ)上，先按公共條件年、月過濾出結(jié)果集中符合條件的記錄，其中 year,month 是 SPL 腳本中定義的參數(shù)，接收來自報表前端傳入的查詢條件，比如查詢 2017 年 01 月，日期范圍是截止到某個時間點，所以需要利用 concat 函數(shù)對 year、month 連接起來再去做條件比較。
A4：循環(huán)游標(biāo)，每次從游標(biāo)讀取 10000 條記錄返回。
B5：代表指標(biāo) A 的金額累計匯總值；每次 for 循環(huán)，根據(jù) C5 的參數(shù)選出符合條件的記錄，用 contain()函數(shù)來判斷參數(shù)是否在結(jié)果集中 ( 其中參數(shù)都是 4 位，所以需要對原記錄中科目 \1000000 后保留科目的前 4 位，才能與參數(shù)進(jìn)行比較)，然后對金額進(jìn)行累計匯總。其中的 @符號代表當(dāng)前格的值，初始值為空，每次循環(huán)時將上次的值與本次符合條件的數(shù)據(jù)值相加，作為新值寫入格中，最終可計算出某個指標(biāo)的金額累計匯總。
B6：代表指標(biāo) B 的金額累計匯總值；與指標(biāo) A 不同的是，多個參數(shù)由不同的位數(shù)組成，所以需要在 contain()函數(shù)中分別截取不同的位數(shù)，與 C6 列的參數(shù)進(jìn)行多次比較。
A105：合并 B5-B104 每個格子的值 (從上往下，100 個指標(biāo)的計算結(jié)果)，返回一個單列數(shù)據(jù)集，可以供報表工具使用。
2.3 預(yù)先匯總方案
現(xiàn)在我們已經(jīng)做到了只需要遍歷一次數(shù)據(jù)，但需要遍歷的整體數(shù)據(jù)量仍然比較大，還有什么辦法能進(jìn)一步減少數(shù)據(jù)量呢？
如果能夠把數(shù)據(jù)事先按科目匯總，那么我們就可以不必重復(fù)累加科目相等的記錄了，而且存儲量也會變少，IO 也會更快。
2.3.1分組計算匯總值
首先，按照科目、年、月分組，金額進(jìn)行匯總，匯總結(jié)果的數(shù)據(jù)結(jié)構(gòu)應(yīng)當(dāng)是：科目、年、月、本科目下當(dāng)月的金額匯總值。
集算器 SPL 腳本實現(xiàn)分組、匯總計算的樣例如下：
A | |
1 | =file("總賬憑證 -pre.btx") |
2 | =file("總賬憑證 -mid.btx") |
3 | =A1.cursor@b() |
4 | =A3.groupx(科目, 年, 月;sum(金額): 匯總金額 ) |
5 | >A2.export@b(A4) |
A1：打開預(yù)處理前的原始數(shù)據(jù)表的集文件對象
A2：計算后中間結(jié)果數(shù)據(jù)的集文件保存的位置
A3：根據(jù)文件創(chuàng)建游標(biāo)返回，其中 cursor() 函數(shù)的 @b 選項代表從集文件中讀取
A4：先按照科目、年、月分組，金額匯總
A5：執(zhí)行 A4 的計算結(jié)果寫入到集文件中，其中 export() 函數(shù)使用了 @b 的選項，@b 代表寫成集文件格式，即總賬憑證 -mid.btx
2.3.2利用跨行組計算累計值
我們這個問題最終是要計算指標(biāo)的期末值，也就是截止某個日期的金額累計值；上一步計算的是當(dāng)月的金額匯總值，那金額的累計值該如何計算呢？
集算器提供了跨行引用的語法，可以用A[-1]代表上一行的 A，這樣就可以計算：累計值 = 上一行的累計值 + 當(dāng)前行值。
腳本中，接著上一步作如下修改即可計算累計值：
A | B | |
1 | =file("總賬憑證 -pre.btx") | |
2 | =file("總賬憑證 -mid.btx") | |
3 | =A1.cursor@b() | |
4 | =A3.groupx(科目, 年, 月;sum(金額): 金額 ) | |
5 | for A4;科目 | =A5.run(金額 = 金額 [-1]+ 金額 ) |
6 | >A2.export@ab(B5,#1:科目,#2: 年,#3: 月,#4: 累計金額 ) |
其他格子的代碼，在上面已經(jīng)解釋過了，這里不再贅述。
A5：利用 for 循環(huán)游標(biāo) A4，其中分號的參數(shù)“科目”表示每次從游標(biāo)讀取一組科目值相同的記錄返回。我們先單步執(zhí)行一下，返回某一個科目的記錄:
再接著執(zhí)行一次 for 循環(huán)，返回下一組科目的記錄：
B5：針對取出的同一科目的記錄，對金額累計；其中表達(dá)式：金額 = 金額 [-1]+ 金額，金額代表當(dāng)前行金額，金額[-1] 代表上一行累計金額值，相加計算好后再重新賦值給金額字段。如下圖是接著 A5 格子執(zhí)行后的結(jié)果變化：
B6：執(zhí)行計算后的結(jié)果寫入到集文件中。其中 export() 函數(shù)使用了 @ab 的選項，@b 代表寫成集文件格式，由于在 for 循環(huán)里面，需要執(zhí)行多次，所以用 @a 以追加的方式把結(jié)果逐步保存到文件中，保證文件的完整性；即總賬憑證 -mid.btx。部分執(zhí)行結(jié)果如下圖：
2.3.3構(gòu)造多層科目匯總值
現(xiàn)在計算出了明細(xì)科目的累計值，我們還需要計算高層次科目（截取前 N 位）對應(yīng)的匯總值。
從需求可以看到，每個計算指標(biāo)都是按照科目截取前 4 位、前 6 位、前 8 位等作為參數(shù)集合，那么在構(gòu)造不同層次的科目號時，也需要和這種規(guī)則匹配，從而計算出不同層次的聚合值。
比如：對于科目是 1234567890，那么就需要新增科目號 1234、123456、12345678 對應(yīng)的匯總金額。也就是對于每個 1234567890 這樣的 10 位科目號，還需要分別增加 4、6、8 位的科目 1234、123456、12345678。其中科目 1234 會把所有 1234 開頭的科目的金額值進(jìn)行累計匯總，依次類推。其實，這就是 CUBE 的常用手段。
需要注意的是，基于上一步計算結(jié)果，數(shù)據(jù)量大小又需要分兩種情況討論：
1、結(jié)果已經(jīng)可以全部直接讀入內(nèi)存參與下一步計算；
2、結(jié)果依然很大，需要采用外存計算 (游標(biāo)技術(shù)可以邊讀邊算，多次計算還需要管道技術(shù)來配合)
2.3.3.1 內(nèi)存計算
如果結(jié)果集可以全部裝入內(nèi)存，集算器 SPL 腳本構(gòu)造多層次科目匯總值的樣例如下：
A | |
1 | =file("總賬憑證 -mid.btx") |
2 | =file("總賬憑證 -later.btx") |
3 | =A1.import@b() |
4 | =A3.groups((科目 \100): 科目, 年, 月;sum( 累計金額): 累計金額匯總 ) |
5 | =A4.groups((科目 \100): 科目, 年, 月;sum( 累計金額匯總): 累計金額匯總 ) |
6 | =A5.groups((科目 \100): 科目, 年, 月;sum( 累計金額匯總): 累計金額匯總 ) |
7 | =[A6,A5,A4].conj() |
8 | >A2.export@z(A7) |
A1：打開中間計算結(jié)果的集文件對象
A2：計算后的結(jié)果集文件保存的位置
A3：從文件對象 A1 中讀出內(nèi)容作為記錄形成結(jié)果集返回；其中 @b 代表從集文件中讀出。
A4：按科目截取前 8 位 (科目 \100)、年、月進(jìn)行分組，累計金額進(jìn)行匯總，如果截取前 7 位，就需要寫成：(科目 \1000)；具體按多少位截取由需求場景決定。執(zhí)行結(jié)果如下圖：
A5：在 A4 的結(jié)果集的基礎(chǔ)上，按科目 \100 得到科目前 6 位、年、月進(jìn)行分組，累計金額進(jìn)行匯總；執(zhí)行結(jié)果如下圖：
A6：同理，在 A5 的基礎(chǔ)上，按科目 \100 得到科目前 4 位、年、月進(jìn)行分組，累計金額進(jìn)行匯總；執(zhí)行結(jié)果如下圖：
A7：多個結(jié)果集合并成一個結(jié)果集
A8：計算后的結(jié)果集導(dǎo)出并保存到文件中，其中 export()函數(shù)使用了 @z 的選項，代表分段寫入到集文件中，即總賬憑證 -later.btx
2.3.3.2 外存計算（游標(biāo) + 管道）
在前面的例子中，我們已經(jīng)使用了游標(biāo)，需要特別強(qiáng)調(diào)的是游標(biāo)只能從前向后單向移動，執(zhí)行一次遍歷計算，只有最終生成的游標(biāo)中的 cs.fetch() 函數(shù)才能夠有效取得數(shù)據(jù)。遍歷結(jié)束后，計算過程中產(chǎn)生的其它游標(biāo)都將不能再次讀取數(shù)據(jù)。
不過有時候，在一次讀取數(shù)據(jù)的過程中，我們需要同時計算出多個結(jié)果，那么此時就需要使用與游標(biāo)類似的管道，用 channel(cs) 建立管道將游標(biāo) cs 的數(shù)據(jù)在遍歷同時壓入管道以便實施其它運算。
和內(nèi)存相比，外存速度慢很多，因此要盡量減少硬盤訪問，所以，我們采用游標(biāo) + 管道的機(jī)制一次遍歷獲得需要的匯總結(jié)果：
A | |
1 | =file("總賬憑證 -mid.btx") |
2 | =file("總賬憑證 -later.btx") |
3 | =A1.cursor@b() |
4 | =channel(A3).groupx((科目 \100): 科目, 年, 月;sum( 累計金額): 累計金額匯總 ) |
5 | =channel(A3).groupx((科目 \10000): 科目, 年, 月;sum( 累計金額): 累計金額匯總 ) |
6 | =A3.groupx((科目 \1000000): 科目, 年, 月;sum( 累計金額): 累計金額匯總 ) |
7 | =[A6,A5.result(),A4.result()].conjx() |
8 | >A2.export@z(A7) |
A1-A3：前面已經(jīng)解釋過了，這里不再贅述。
A4：創(chuàng)建管道，將游標(biāo) A3 中的數(shù)據(jù)推送到管道，其中 ch.groupx() 函數(shù)針對管道中的有序記錄分組并返回管道；按科目截取前 8 位、年、月進(jìn)行分組，累計金額進(jìn)行匯總
A5：同理于 A4 返回管道，按科目截取前 6 位、年、月進(jìn)行分組，累計金額進(jìn)行匯總
A6：返回游標(biāo)，按科目截取前 4 位、年、月進(jìn)行分組，累計金額進(jìn)行匯總
A7：多個游標(biāo)運算結(jié)果合并成一個結(jié)果集；其中 ch.result() 代表管道的運算結(jié)果
A8：計算后的結(jié)果集導(dǎo)出并保存到集文件，即總賬憑證 -later.btx
2.3.4優(yōu)化“一次遍歷”的方案
經(jīng)過上面兩步數(shù)據(jù)預(yù)處理，結(jié)果數(shù)據(jù)可以直接作為報表的數(shù)據(jù)源，每個指標(biāo)的計算條件只要相等比較就可以，而不再需要截取、計算前幾位了。
所以在前述“一次遍歷“方案的基礎(chǔ)上，我們來做一些優(yōu)化；集算器的 SPL 腳本樣例如下：
A | B | C | |
1 | =file("總賬憑證 -later.btx") | /指標(biāo)參數(shù)列 | |
2 | =A1.cursor@b() | ||
3 | =A2.select(concat(年, 月 )<=concat(year,month)) | ||
4 | for A3,10000 | ||
5 | =@+A4.select(C5.contain(科目 )).sum(累計金額匯總) | [1001,1002] | |
6 | =@+A4.select(C6.contain(科目 )).sum(累計金額匯總) | [2702,153102,12310105,1122,12310101,12310401,12319001,12310201,12310301,12310501,12310601,12310701,12310801,12319101] | |
… | … | … | |
105 | return [B5:B104] | ||
A1-A4：前面已經(jīng)解釋過了，這里不再贅述。
B5：代表指標(biāo) A 的累計金額的匯總值求和；每次 for 循環(huán)，根據(jù) C5 的參數(shù)選出符合條件的記錄，用 contain() 函數(shù)來判斷參數(shù)是否在結(jié)果集中，然后對累計金額匯總進(jìn)行求和。其中的 @符號代表當(dāng)前格的值，初始值為空，每次循環(huán)時將上次的值與本次符合條件的數(shù)據(jù)值相加，作為新值寫入格中，最終可計算出某個指標(biāo)的累計金額匯總的求和值。
B6：同 B5 的寫法，代表指標(biāo) B 的累計金額匯總值求和，通常集合元素個數(shù)超過 13 個時，如果事先能對常數(shù)集合排序，那么可以選擇 contain() 函數(shù)的 @b 選項，利用二分查找會明顯快于順序查找。
A105：合并 B5-B104 每個格子的值 (從上往下，100 個指標(biāo)的計算結(jié)果)，返回一個單列數(shù)據(jù)集，供報表工具使用。
至此，我們可以看到，按照預(yù)先匯總的思路，事先根據(jù)數(shù)據(jù)特征對數(shù)據(jù)進(jìn)行預(yù)處理，可以讓總的數(shù)據(jù)量變小，同時減少遍歷量，從而避免前述方案中總是從最底層再去累加的模式。經(jīng)過實測：從報表取數(shù)到報表展現(xiàn)整個環(huán)節(jié)比“常規(guī)”方案足足提高了6-8倍左右，這樣的體驗已經(jīng)可以很好地滿足用戶要求了。
那么，是否有更好的優(yōu)化方案呢？答案是肯定的！請看：多層科目任意組合匯總報表的性能優(yōu)化 (下)
著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。
文章標(biāo)題：多層科目任意組合匯總報表的性能優(yōu)化(上)
文章來源：http://m.jiaotiyi.com/article/gcdsci.html