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

2.4 有序計算方案

在充分利用遍歷一次的特點進行優化后，可能我們還會覺得計算性能有點慢，希望有進一步優化的空間。由于每次只需要取出總數據量的很小一部分 (100 個指標涉及的所有科目號大概幾百個，即在幾百萬記錄中取幾百條)，這時我們通常能想到的是：如果能利用數據有序直接進行有序查找(若源數據有序，可以快速定位到這幾百條記錄，不需要遍歷幾百萬記錄甚至更多的數據)，將能夠獲得更好的查詢效率。

我們可以利用集算器提供的 iselect() 函數對每個計算的指標進行有序查找，從而減少遍歷次數。這里需要注意兩個關鍵點：

1、 iselect()函數用單個主鍵的查找速度會比用多個主鍵查找更快，并且寫法上也會簡單很多。

2、 在數據預處理時，遇到多個主鍵時應該想辦法合并成一個，并且數字化后進行排序，以便使用 iselect() 函數。

關于 iselect() 函數的具體用法和有序計算的解釋這里不再贅述，可參考集算器教程的相關章節。

2.4.1 合并主鍵、排序

為了滿足上面提出的兩個關鍵點，我們需要對源數據重新預處理一遍，關于分組計算匯總值、利用跨行組計算累計值等原理上面已經講過了，這里主要說合并主鍵和排序。

第一步，在原始數據中，用“年”和“月”兩列字段動態計算一個變量值，稱為“月號”，以便與“科目”字段合并成唯一主鍵。代碼中相應的改動如下：

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

B3：首先在集算器中定義參數名稱：inityear，設置值為 2014，如下圖：

假設原始數據是從 2014 年開始的，所以把初始年份的默認值設置為 2014。所謂“月號”就是每條記錄的時間是從初始年份 1 月開始的第幾個月。比如：當前一條數據記錄中年是 2017，月是 3 的話，那么根據這個公式的結果：月號 =(2017-2014)*12+3，也就是 2014 年 1 月開始的第 39 個月。將計算結果利用 run() 函數重新賦值給月字段，以便后面與科目構造唯一主鍵。

A4：按科目、月號進行分組，金額進行求和（前面已經解釋過）

B5：對金額字段進行累計（前面已經解釋過）

B6：計算后的結果集以追加的方式保存到集文件中（前面已經解釋過），即總賬憑證 -mid.btx，執行結果如下圖：

第二步，對科目前 N 位分別匯總金額；如何計算多層科目匯總值前面已經講過了，這里主要關注月號和科目合并成主鍵 key，然后進行排序。月號計算出來是 2 位（假設數據記錄跨度不超過 99 個月），科目為固定的 10 位，這樣為了保證合并成主鍵后的唯一性，需要定義新主鍵的總長度為 12 位。

這樣，新主鍵的構造規則就是：key(12 位)= 月號 (月號為 2 位)10000000000+ 總賬科目 (最長為 10 位)。有一個技巧需要說明一下：這里設定 key 的長度為 12 位，可以存放在一個 long 類型中，如果更長 (與需求有關)，就要用字符串了，雖然會相對慢一點，但也影響不大。

集算器的 SPL 腳本如下：

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

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

A5：同理于 A4 返回管道，按科目截取前 6 位、月號進行分組，累計金額進行匯總。返回的數據結構如下圖：

A6：返回游標，按科目截取前 4 位、月號進行分組，累計金額進行匯總。返回的數據結構如下圖：

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

A8：對 A7 的每條記錄生成新序表，序表包含兩個字段：由月號 (月號為 2 位)10000000000，然后再加上截取后生成的新的科目 (最長為 10 位)，重新定義為 key 和累計金額匯總兩列字段，接著再對 key 進行排序。

特別說明一下，cs.groupx() 函數按照字段分組后，會對該字段進行排序，也就是說運算后的結果本身就是有序的，所以我們可以利用這個特性，先按月號分組 (寫前面)，再用 cs.mergex() 函數按照月號、科目做有序歸并運算，歸并后的結果就不再需要排序了。相應地代碼改動如下：

A9：計算后的結果集導出并保存到集文件中，即總賬憑證 -later.btx。數據結構如下圖：

2.4.2 有序查詢

這樣，我們就按照要求完成了數據預處理工作，接下來分兩步驗證報表查詢：

1、定義子程序: 任意給定一個計算指標，能夠快速返回指標匯總值；然后多次調用子程序來完成 100 個指標的計算，返回結果集。

2、任意給定 100 個計算指標，快速返回與之對應的指標匯總值。

2.4.2.1 多次 ISELECT 查詢

首先，定義一個子程序，任意給定一個計算指標（可能只有科目號前面 N 位，比如前 4 位 /6 位 /8 位 /10 位等，自由組合出現），返回這個指標匯總值。

然后，通過調用子程序來完成 100 個指標的計算，先定義查詢參數， yyyy 代表查詢年，mm 代表查詢月，比如：查詢 2017 年 1 月的數據，如下圖：

調用子程序的樣例：

A1：定義變量 inityear，假定原始數據是從 2014 年開始的，所以設置默認值為 2014；

B1：按照前面相同的規則生成“月號”。如果參數是 2017 年 1 月，執行結果如下：

C1：預處理后的數據文件對象

A2-C6：子程序代碼。子程序是以語句 func 為主格的代碼塊，結果用 return 語句返回。這個子程序主要功能是任意給定一個計算指標，返回匯總值。

B3：接收參數中每一個科目號，利用月號 (月號為 12 位) 加上當前科目號，形成指標的參數集合。比如傳入參數為：[1214,1207], 則執行結果如下圖：

C3：接著對對指標參數集合 B3 排序。執行結果如下圖：

B4：根據指標 C3 中的參數集合與結果集文件中有序的 key 字段進行比對查找，返回游標；其中 @b 代表從集文件中讀取。

B5：從游標中獲取記錄，執行結果如下圖：

B6：對累計金額匯總求和，返回指標的計算結果。

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

C8：指標 B 的參數條件 (按科目號前 4 位 / 前 6 位 / 前 8 位截取的多個值，形成的參數集合) ，剩余的 98 個指標，計算的寫法類似 A8，參數的寫法類似 C8，依次類推到 100。

A7：調用 func 子程序，把 C7 的指標參數值傳入到子程序中，子程序計算后返回結果。

A8：同理，計算指標 B 的結果集

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

這樣做已經可以利用有序查詢了，但計算 100 個指標還需要執行 100 次子程序的 iselect() 函數，依然遍歷太多，編碼過程也比較繁瑣。

2.4.2.2 一次 ISELECT 查詢

那么，有沒有辦法只做一次 iselect 查詢呢？

答案是有！我們可以把 100 個需要計算的指標的科目號都整理好，然后執行一次 iselect() 函數，把所有指標匯總結果都查找出來，這樣，就大功告成了。

這里，需要注意兩個關鍵點：

1、需要將多個計算指標中的不同科目號進行合并、構造主鍵、排序。

2、利用 pos()的函數技巧，根據每個計算指標中多個科目號與月號構造的主鍵在結果集中的找到坐標位置 ( 與 key 列字段比對)，返回位置序號, 接著根據位置序號在結果集中找到的累計金額匯總字段進行求和，求和結果再按位置序號倒回到每個指標中，即每個指標的匯總值計算完成。

為了便于理解，舉個例子，詳細解釋一下利用 pos() 函數是如何做到定位計算的？示意圖如下：

解釋：指標 A 和指標 B 的所有科目號合并，然后統一排序生成序號，通過序號在有序結果集中找到對應的金額，再利用位置序號把金額倒回到每個指標中，每個指標下對多個科目號的金額匯總，即指標匯總值。

最終，計算 100 個指標的集算器的 SPL 腳本樣例如下：

A22：把 A2 到 E21 范圍內 100 個計算指標的科目號合并起來，其中 A2 格子代表指標 1，B2 代表指標 2，依次類推；合并完后，執行結果如下圖：

B22：對 A22 指標中每一個科目號，分別利用月號 (月號為 2 位)*10000000000，加上當前科目號，形成指標的參數組集合。執行結果如下圖：

C22：對指標參數組集合進行合并，然后排序

A23：打開預處理后的集文件對象

A24：根據指標 C22 中構造的參數組集合與文件中有序的 key 字段進行比對，記錄返回成游標

B24：從游標中獲取記錄，返回所有科目號的查詢到的結果集，執行結果如下圖：

C24-D24：分別獲取結果集中的：key、累計金額匯總。

A25：按照 A22 中每個科目號的順序，在 B24 結果集中利用 key 列與當前科目號 + 月號構造的主鍵進行比對，然后返回位置序號，其中 pos() 函數中 @b 代表使用二分法查找，效率更高，但要求被尋找序列是有序的。運算結果如下圖：

A26：利用 A25 每個成員坐標位置的序號，在結果集 B24 中對比找到的累計金額匯總字段進行求和，求和結果再按位置序號倒回到每個指標中，比如序號 1 返回的結果代表 A2 的參數查詢出來的指標 1 匯總結果，序號 2 返回的結果代表 B2 的參數查詢出來的指標 2 匯總結果。依次類推。即每個指標的匯總值計算完成。執行結果如下圖：

A27：返回結果集，供報表工具使用。

至此，利用一次遍歷，搞定所有事情，難題迎刃而解！

實測結果：報表從取數到展現整個環節大概需要1-2秒，其中指標計算部分用時不到1秒。

2.5 作為報表數據源

對于報表的制作過程來說，并不需要做什么改變，只需要把數據源切換到集算器即可。假定報表工具是潤乾報表 V5：

首先，導入 excel 表樣，創建數據集類型為集算器，選取已經做好的 dfx 腳本；同時設置相應的查詢參數等。

然后，在報表的每個單元格里分別按順序取值，即可得到每個指標匯總結果；比如單元格 C5 的表達式寫法：=ds1.select(#1)(1)，單元格 C6 的寫法：=ds1.select(#1)(2)，……，報表單元格表達式從上往下，依次類推。樣例如下圖：

2.6 總結

在實際的報表開發過程中，當我們遇到問題，往往并不能一開始就想到最優的解決辦法。我們可以試著先用最簡單、最容易的辦法實現，然后再一步步進行優化；對比每種方案的存在的缺陷及改進后所帶來的性能提升，從而最終滿足業務需求。

本文中我們就采用了這種方式，逐步優化的步驟如下：

1、多次遍歷方案

2、一次遍歷方案

3、預先匯總方案，查詢部分在 2 的基礎上進行優化

4、有序計算方案

整個過程中，我們用到的集算器相關技術包括：游標、管道、遍歷復用、數據外置、分組子集、跨行組計算、有序計算 / 查詢、二分法查找、位置序號等。

了解了這些概念并熟練掌握集算器相關的函數后，我們就可以寫出高效的代碼，快速實現報表數據集的準備工作！

三 引入集算器后的報表系統結構

實際業務中，我們針對客戶提供的生產環境數據 (原數據表大概 6000 萬明細記錄)，利用這種方案進行了 POC 實測。結果表明，原來需要 30-40 秒才能呈現的資產負債表，現在提高到 1-2 秒內。而且，集算器腳本可以與報表模板一起管理，從而有效降低應用管理的復雜度。

使用集算器進行預處理計算后，形成的數據緩存文件，能夠很好的優化現有報表實現模式，有效解決大數據集報表運算慢的難題。

原有模式和引入集算器后的報表系統結構對比如下圖所示：

根據報表的業務特點，通常具體的實現步驟如下：

1、集算器抽取來自數據源的數據，根據報表的業務規則，取出需要的維度、過濾字段、計算指標等明細記錄。

2、集算器對明細記錄進行預處理計算，生成報表需要的各類指標。

3、計算后的指標以數據緩存文件的方式存放，可以按照業務種類、模塊關系、時間順序進行多級目錄管理，也可以和報表模板一起管理。

4、報表工具通過 JDBC 方式調用集算器，計算結果返回給報表工具呈現。

5、可以設置計劃任務定時執行，完成上述各項數據預處理動作。