多層科目任意組合匯總報表的性能優化 (上)

一 問題背景

我們先來看一張資產負債表:

這是一個典型的中國式復雜報表格式，其復雜并不在于布局，而在于其中“期末余額”的每個單元格都是一個需要獨立計算的指標，互相之間幾乎沒有關系，事實上就是一個各種指標的匯總清單，而這些指標往往會有上百個之多。

在源數據表結構中，有一個字段稱為科目，其長度總是固定的 10 位，如：1234567890，如下圖：

科目字段的值實際上是一個分層的代碼，而前面表里上百個指標就是根據需求對不同層次科目數據的統計結果，具體的做法是通過截取科目的前幾位來確定層次，然后按需求自由組合，作為條件進行過濾，最后對金額字段進行累計匯總。

比如計算指標 A 對應的科目列表是 [1001，1002]，代表累計所有前 4 位是 1001、1002 的科目，用 SQL 寫出來就是：select sum(金額 )from T1 where concat( 年, 月)<=? and (left( 科目,4)="1001" or left(科目,4)="1002")

其中年、月是公共過濾條件，代表統計的時間范圍。

類似的，如果另一個指標 B 對應的科目為 [2702,153102,12310105], 那就代表對前 4 位是 2702、前 6 位是 153102、前 8 位是 12310105 的所有科目值進行累計，用 SQL 寫出來就是：select sum(金額 )from T1 where concat( 年, 月)<=? and (left( 科目,4)="2702" or left(科目,6)="153102" or left(科目,8)="12310105")；

實際業務中，每個指標對應的科目數量不定，可能多達 10 個以上，而且就像指標 B 這樣，各個科目的層次也不盡相同。

在有了報表工具之后 (固定報表)，原則上這類格式復雜、指標參數任意組合的報表需求并不難實現，只是原始數據量一大，查詢響應就會非常慢，用戶體驗變差，當多并發請求時，還會對正常業務產生影響。

二 開發和優化過程

2.1 多次遍歷方案

最常見的開發思路，就是按前面說的計算方式，對報表的每個指標都寫一句完整的 SQL 來計算，有 100 個指標，就寫 100 個 SQL。

有些報表工具提供了函數，可以直接在單元格中執行 SQL (比如 query/call 等)，單元格的表達式大概會是這樣：

= query("select sum(金額 )from T1 where concat( 年, 月)<=? and (left( 科目,4)='2702' or left(科目,6)='153102' or left(科目,8)='12310105')", concat(year,month))

如果是非多源報表工具，則可以借助外部程序數據源來實現，比如可以直接用集算器編寫以下腳本：

簡單說明一下：

A1：連接數據庫 demo

A2-A3：執行指標 A 的查詢 SQL 和指標 B 的查詢 SQL；其中 query() 函數中 @1 選項代表查詢符合條件的第一條記錄, 返回成單值或序列（一個字段是單值，多個字段是序列）；這個例子是對金額匯總求和，所以返回單值。

A4-A101：假定有剩余的 98 個指標，每個指標的查詢 SQL 都類似于 A2、A3 的寫法

A102：關閉數據庫連接

A103：合并 A2-A101 每個格子的計算結果 (共計 100 個指標值)，返回一個單列數據集，供報表工具使用。

不過，在這種思路下，無論直接在格中使用 SQL 還是在程序數據源中計算，實際上每計算一個指標就得遍歷一次源數據；而每個指標還對應多個需要 AND 的條件，這些都會嚴重降低性能。

這種思路的優點是簡單直接，看上去確實能夠實現需求，開發過程也并不太難。在數據量不大的情況下，查詢也不會很慢，勉強還能接受。不過，隨著數據量越來越大，性能瓶頸就會隨之而來，到了一定程度后，就可能出現在關鍵時刻用戶無法及時獲得自己想要指標的問題，最終只能放棄。

2.2 一次遍歷方案

上面“多次遍歷方案”的問題在于，無論如何，對源數據遍歷 100 次實在是太低效了，那么，我們有沒有辦法能少遍歷幾次呢? 能不能只做一次遍歷就把所有指標都計算出來呢？

這種一次遍歷的思路確實是可以的，我們只需要把 SQL 中的 WHERE 條件拼到 SELECT 中就行了，比如前面說到的指標 A 和 B 可以寫成：

SELECT SUM(CASE WHEN (LEFT(科目,4)='1001' OR LEFT( 科目,4)='1002')THEN 金額 ELSE 0 END) 指標 A,

SUM(CASE WHEN (LEFT(科目,4)="2702" OR LEFT( 科目,6)="153102" OR LEFT(科目,8)="12310105")THEN 金額 ELSE 0 END) 指標 B,

    …

FROM T1 WHERE CONCAT(年, 月 )<=?

但是，真要用這個思路來處理 100 個指標，可以想見這個 SQL 會有多長，維護難度會有多大。為此，我們可以利用集算器的游標來實現這個邏輯，適當降低維護難度。

另外，這種方案下的遍歷，還是需要把整表數據讀出數據庫，而 JDBC 太慢，IO 時間很可能成為瓶頸。對于這個問題，我們注意到其中處理的都是不再變化的歷史數據，那么我們就可以把數據先搬出數據庫存成文件，然后用文件作為數據源，從而加快 IO 訪問。具體實現如下：

1、把數據搬出數據庫保存成文件，集算器的 SPL 腳本如下：

A1：連接數據庫 demo

A2：根據 sql 創建數據庫游標返回

A3：集文件保存的位置

A4：導出整表數據并保存到文件中，其中 export()函數的 @b 選項代表寫入到集文件中，即總賬憑證 -pre.btx

A5：關閉數據庫連接

2、遍歷一次源數據，計算 100 個指標，集算器的 SPL 腳本如下：

值得注意的是：這個例子引入了一個新的寫法，100 個指標參數可以統一寫到 C 列上，當 B 列每計算一個指標時，直接引用 C 列當前行的所對應的參數即可。比如：

C5：指標 A 的參數條件 (按科目號前 4 位截取的多個值形成的集合)

C6：指標 B 的參數條件 (按科目號前 4 位 / 前 6 位 / 前 8 位截取的多個值，形成的參數集合)

剩余的 98 個指標，計算的寫法類似 B6，參數的寫法類似 C6，依次類推到 100。

顯然，這種計算邏輯和參數分離的寫法，能夠極大地提高可維護性。

下面我們完整地分析一下這段腳本：

A1：打開預處理前的原始數據表的集文件對象

A2：根據文件創建游標返回，其中 cursor() 函數使用 @b 選項代表從集文件中讀取。

A3：在 A2 的基礎上，先按公共條件年、月過濾出結果集中符合條件的記錄，其中 year,month 是 SPL 腳本中定義的參數，接收來自報表前端傳入的查詢條件，比如查詢 2017 年 01 月，日期范圍是截止到某個時間點，所以需要利用 concat 函數對 year、month 連接起來再去做條件比較。

A4：循環游標，每次從游標讀取 10000 條記錄返回。

B5：代表指標 A 的金額累計匯總值；每次 for 循環，根據 C5 的參數選出符合條件的記錄，用 contain()函數來判斷參數是否在結果集中 ( 其中參數都是 4 位，所以需要對原記錄中科目 \1000000 后保留科目的前 4 位，才能與參數進行比較)，然后對金額進行累計匯總。其中的 @符號代表當前格的值，初始值為空，每次循環時將上次的值與本次符合條件的數據值相加，作為新值寫入格中，最終可計算出某個指標的金額累計匯總。

B6：代表指標 B 的金額累計匯總值；與指標 A 不同的是，多個參數由不同的位數組成，所以需要在 contain()函數中分別截取不同的位數，與 C6 列的參數進行多次比較。

A105：合并 B5-B104 每個格子的值 (從上往下，100 個指標的計算結果)，返回一個單列數據集，可以供報表工具使用。

2.3 預先匯總方案

現在我們已經做到了只需要遍歷一次數據，但需要遍歷的整體數據量仍然比較大，還有什么辦法能進一步減少數據量呢？

如果能夠把數據事先按科目匯總，那么我們就可以不必重復累加科目相等的記錄了，而且存儲量也會變少，IO 也會更快。

2.3.1 分組計算匯總值

首先，按照科目、年、月分組，金額進行匯總，匯總結果的數據結構應當是：科目、年、月、本科目下當月的金額匯總值。

集算器 SPL 腳本實現分組、匯總計算的樣例如下：

A1：打開預處理前的原始數據表的集文件對象

A2：計算后中間結果數據的集文件保存的位置

A3：根據文件創建游標返回，其中 cursor() 函數的 @b 選項代表從集文件中讀取

A4：先按照科目、年、月分組，金額匯總

A5：執行 A4 的計算結果寫入到集文件中，其中 export() 函數使用了 @b 的選項，@b 代表寫成集文件格式，即總賬憑證 -mid.btx

2.3.2 利用跨行組計算累計值

我們這個問題最終是要計算指標的期末值，也就是截止某個日期的金額累計值；上一步計算的是當月的金額匯總值，那金額的累計值該如何計算呢？

集算器提供了跨行引用的語法，可以用A[-1]代表上一行的 A，這樣就可以計算：累計值 = 上一行的累計值 + 當前行值。

腳本中，接著上一步作如下修改即可計算累計值：

其他格子的代碼，在上面已經解釋過了，這里不再贅述。

A5：利用 for 循環游標 A4，其中分號的參數“科目”表示每次從游標讀取一組科目值相同的記錄返回。我們先單步執行一下，返回某一個科目的記錄:

再接著執行一次 for 循環，返回下一組科目的記錄：

B5：針對取出的同一科目的記錄，對金額累計；其中表達式：金額 = 金額 [-1]+ 金額，金額代表當前行金額，金額[-1] 代表上一行累計金額值，相加計算好后再重新賦值給金額字段。如下圖是接著 A5 格子執行后的結果變化：

B6：執行計算后的結果寫入到集文件中。其中 export() 函數使用了 @ab 的選項，@b 代表寫成集文件格式，由于在 for 循環里面，需要執行多次，所以用 @a 以追加的方式把結果逐步保存到文件中，保證文件的完整性；即總賬憑證 -mid.btx。部分執行結果如下圖：

2.3.3 構造多層科目匯總值

現在計算出了明細科目的累計值，我們還需要計算高層次科目（截取前 N 位）對應的匯總值。

從需求可以看到，每個計算指標都是按照科目截取前 4 位、前 6 位、前 8 位等作為參數集合，那么在構造不同層次的科目號時，也需要和這種規則匹配，從而計算出不同層次的聚合值。

比如：對于科目是 1234567890，那么就需要新增科目號 1234、123456、12345678 對應的匯總金額。也就是對于每個 1234567890 這樣的 10 位科目號，還需要分別增加 4、6、8 位的科目 1234、123456、12345678。其中科目 1234 會把所有 1234 開頭的科目的金額值進行累計匯總，依次類推。其實，這就是 CUBE 的常用手段。

需要注意的是，基于上一步計算結果，數據量大小又需要分兩種情況討論：

1、 結果已經可以全部直接讀入內存參與下一步計算；

2、 結果依然很大，需要采用外存計算 (游標技術可以邊讀邊算，多次計算還需要管道技術來配合)

2.3.3.1 內存計算

如果結果集可以全部裝入內存，集算器 SPL 腳本構造多層次科目匯總值的樣例如下：

A1：打開中間計算結果的集文件對象

A2：計算后的結果集文件保存的位置

A3：從文件對象 A1 中讀出內容作為記錄形成結果集返回；其中 @b 代表從集文件中讀出。

A4：按科目截取前 8 位 (科目 \100)、年、月進行分組，累計金額進行匯總，如果截取前 7 位，就需要寫成：(科目 \1000)；具體按多少位截取由需求場景決定。執行結果如下圖：

A5：在 A4 的結果集的基礎上，按科目 \100 得到科目前 6 位、年、月進行分組，累計金額進行匯總；執行結果如下圖：

A6：同理，在 A5 的基礎上，按科目 \100 得到科目前 4 位、年、月進行分組，累計金額進行匯總；執行結果如下圖：

A7：多個結果集合并成一個結果集

A8：計算后的結果集導出并保存到文件中，其中 export()函數使用了 @z 的選項，代表分段寫入到集文件中，即總賬憑證 -later.btx

2.3.3.2 外存計算（游標 + 管道）

在前面的例子中，我們已經使用了游標，需要特別強調的是游標只能從前向后單向移動，執行一次遍歷計算，只有最終生成的游標中的 cs.fetch() 函數才能夠有效取得數據。遍歷結束后，計算過程中產生的其它游標都將不能再次讀取數據。

不過有時候，在一次讀取數據的過程中，我們需要同時計算出多個結果，那么此時就需要使用與游標類似的管道，用 channel(cs) 建立管道將游標 cs 的數據在遍歷同時壓入管道以便實施其它運算。

和內存相比，外存速度慢很多，因此要盡量減少硬盤訪問，所以，我們采用游標 + 管道的機制一次遍歷獲得需要的匯總結果：

A1-A3：前面已經解釋過了，這里不再贅述。

A4：創建管道，將游標 A3 中的數據推送到管道，其中 ch.groupx() 函數針對管道中的有序記錄分組并返回管道；按科目截取前 8 位、年、月進行分組，累計金額進行匯總

A5：同理于 A4 返回管道，按科目截取前 6 位、年、月進行分組，累計金額進行匯總

A6：返回游標，按科目截取前 4 位、年、月進行分組，累計金額進行匯總

A7：多個游標運算結果合并成一個結果集；其中 ch.result() 代表管道的運算結果

A8：計算后的結果集導出并保存到集文件，即總賬憑證 -later.btx

2.3.4 優化“一次遍歷”的方案

經過上面兩步數據預處理，結果數據可以直接作為報表的數據源，每個指標的計算條件只要相等比較就可以，而不再需要截取、計算前幾位了。

所以在前述“一次遍歷“方案的基礎上，我們來做一些優化；集算器的 SPL 腳本樣例如下：

A1-A4：前面已經解釋過了，這里不再贅述。

B5：代表指標 A 的累計金額的匯總值求和；每次 for 循環，根據 C5 的參數選出符合條件的記錄，用 contain() 函數來判斷參數是否在結果集中，然后對累計金額匯總進行求和。其中的 @符號代表當前格的值，初始值為空，每次循環時將上次的值與本次符合條件的數據值相加，作為新值寫入格中，最終可計算出某個指標的累計金額匯總的求和值。

B6：同 B5 的寫法，代表指標 B 的累計金額匯總值求和，通常集合元素個數超過 13 個時，如果事先能對常數集合排序，那么可以選擇 contain() 函數的 @b 選項，利用二分查找會明顯快于順序查找。

A105：合并 B5-B104 每個格子的值 (從上往下，100 個指標的計算結果)，返回一個單列數據集，供報表工具使用。

至此，我們可以看到，按照預先匯總的思路，事先根據數據特征對數據進行預處理，可以讓總的數據量變小，同時減少遍歷量，從而避免前述方案中總是從最底層再去累加的模式。經過實測：從報表取數到報表展現整個環節比“常規”方案足足提高了6-8倍左右，這樣的體驗已經可以很好地滿足用戶要求了。

那么，是否有更好的優化方案呢？答案是肯定的！請看：多層科目任意組合匯總報表的性能優化 (下)
著作權歸作者所有。商業轉載請聯系作者獲得授權，非商業轉載請注明出處。