在充分利用遍歷一次的特點進行優(yōu)化后,可能我們還會覺得計算性能有點慢,希望有進一步優(yōu)化的空間。由于每次只需要取出總數(shù)據(jù)量的很小一部分 (100 個指標涉及的所有科目號大概幾百個,即在幾百萬記錄中取幾百條),這時我們通常能想到的是:如果能利用數(shù)據(jù)有序直接進行有序查找(若源數(shù)據(jù)有序,可以快速定位到這幾百條記錄,不需要遍歷幾百萬記錄甚至更多的數(shù)據(jù)),將能夠獲得更好的查詢效率。
目前累計服務(wù)客戶上1000+,積累了豐富的產(chǎn)品開發(fā)及服務(wù)經(jīng)驗。以網(wǎng)站設(shè)計水平和技術(shù)實力,樹立企業(yè)形象,為客戶提供成都做網(wǎng)站、成都網(wǎng)站設(shè)計、網(wǎng)站策劃、網(wǎng)頁設(shè)計、網(wǎng)絡(luò)營銷、VI設(shè)計、網(wǎng)站改版、漏洞修補等服務(wù)。成都創(chuàng)新互聯(lián)公司始終以務(wù)實、誠信為根本,不斷創(chuàng)新和提高建站品質(zhì),通過對領(lǐng)先技術(shù)的掌握、對創(chuàng)意設(shè)計的研究、對客戶形象的視覺傳遞、對應(yīng)用系統(tǒng)的結(jié)合,為客戶提供更好的一站式互聯(lián)網(wǎng)解決方案,攜手廣大客戶,共同發(fā)展進步。
我們可以利用集算器提供的 iselect() 函數(shù)對每個計算的指標進行有序查找,從而減少遍歷次數(shù)。這里需要注意兩個關(guān)鍵點:
1、iselect()函數(shù)用單個主鍵的查找速度會比用多個主鍵查找更快,并且寫法上也會簡單很多。
2、在數(shù)據(jù)預處理時,遇到多個主鍵時應(yīng)該想辦法合并成一個,并且數(shù)字化后進行排序,以便使用 iselect() 函數(shù)。
關(guān)于 iselect() 函數(shù)的具體用法和有序計算的解釋這里不再贅述,可參考集算器教程的相關(guān)章節(jié)。
2.4.1 合并主鍵、排序
為了滿足上面提出的兩個關(guān)鍵點,我們需要對源數(shù)據(jù)重新預處理一遍,關(guān)于分組計算匯總值、利用跨行組計算累計值等原理上面已經(jīng)講過了,這里主要說合并主鍵和排序。
第一步,在原始數(shù)據(jù)中,用“年”和“月”兩列字段動態(tài)計算一個變量值,稱為“月號”,以便與“科目”字段合并成唯一主鍵。代碼中相應(yīng)的改動如下:
A | B | |
1 | =file("總賬憑證 -pre.btx") | |
2 | =file("總賬憑證 -mid.btx") | |
3 | =A1.cursor@b() | >A3.run(((年 -inityear)*12+ 月): 月 ) |
4 | =A3.groupx(科目, 月:月號;sum(金額): 金額 ) | |
5 | for A4;科目 | =A5.run(金額 = 金額 [-1]+ 金額 ) |
6 | >A2.export@ab(B5,#1:科目,#2: 月號,#3: 累計金額 ) |
其他格子的代碼,前面已經(jīng)解釋過了,這里不再贅述。
B3:首先在集算器中定義參數(shù)名稱:inityear,設(shè)置值為 2014,如下圖:
假設(shè)原始數(shù)據(jù)是從 2014 年開始的,所以把初始年份的默認值設(shè)置為 2014。所謂“月號”就是每條記錄的時間是從初始年份 1 月開始的第幾個月。比如:當前一條數(shù)據(jù)記錄中年是 2017,月是 3 的話,那么根據(jù)這個公式的結(jié)果:月號 =(2017-2014)*12+3,也就是 2014 年 1 月開始的第 39 個月。將計算結(jié)果利用 run() 函數(shù)重新賦值給月字段,以便后面與科目構(gòu)造唯一主鍵。
A4:按科目、月號進行分組,金額進行求和(前面已經(jīng)解釋過)
B5:對金額字段進行累計(前面已經(jīng)解釋過)
B6:計算后的結(jié)果集以追加的方式保存到集文件中(前面已經(jīng)解釋過),即總賬憑證 -mid.btx,執(zhí)行結(jié)果如下圖:
第二步,對科目前 N 位分別匯總金額;如何計算多層科目匯總值前面已經(jīng)講過了,這里主要關(guān)注月號和科目合并成主鍵 key,然后進行排序。月號計算出來是 2 位(假設(shè)數(shù)據(jù)記錄跨度不超過 99 個月),科目為固定的 10 位,這樣為了保證合并成主鍵后的唯一性,需要定義新主鍵的總長度為 12 位。
這樣,新主鍵的構(gòu)造規(guī)則就是:key(12 位)= 月號 (月號為 2 位)10000000000+ 總賬科目 (最長為 10 位)。有一個技巧需要說明一下:這里設(shè)定 key 的長度為 12 位,可以存放在一個 long 類型中,如果更長 (與需求有關(guān)),就要用字符串了,雖然會相對慢一點,但也影響不大。
集算器的 SPL 腳本如下:
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 | =A7.new(#210000000000+#1:key,#3:累計金額匯總 ).sortx(key) |
9 | >A2.export@z(A8) |
A1-A3:前面已經(jīng)解釋過了,這里不再贅述。
A4:創(chuàng)建管道,將游標 A3 中的數(shù)據(jù)推送到管道,其中 ch.groupx() 函數(shù)針對管道中的有序記錄分組并返回管道;按科目截取前 8 位、月號進行分組,累計金額進行匯總。返回的數(shù)據(jù)結(jié)構(gòu)如下圖:
A5:同理于 A4 返回管道,按科目截取前 6 位、月號進行分組,累計金額進行匯總。返回的數(shù)據(jù)結(jié)構(gòu)如下圖:
A6:返回游標,按科目截取前 4 位、月號進行分組,累計金額進行匯總。返回的數(shù)據(jù)結(jié)構(gòu)如下圖:
A7:多個游標運算結(jié)果合并成一個結(jié)果集;其中 ch.result() 代表管道的運算結(jié)果
A8:對 A7 的每條記錄生成新序表,序表包含兩個字段:由月號 (月號為 2 位)10000000000,然后再加上截取后生成的新的科目 (最長為 10 位),重新定義為 key 和累計金額匯總兩列字段,接著再對 key 進行排序。
特別說明一下,cs.groupx() 函數(shù)按照字段分組后,會對該字段進行排序,也就是說運算后的結(jié)果本身就是有序的,所以我們可以利用這個特性,先按月號分組 (寫前面),再用 cs.mergex() 函數(shù)按照月號、科目做有序歸并運算,歸并后的結(jié)果就不再需要排序了。相應(yīng)地代碼改動如下:
A | |
… | … |
4 | =channel(A3).groupx(月號,(科目 \100): 科目;sum( 累計金額): 累計金額匯總 ) |
5 | =channel(A3).groupx(月號,(科目 \10000): 科目;sum( 累計金額): 累計金額匯總 ) |
6 | =A3.groupx(月號,(科目 \1000000): 科目;sum( 累計金額): 累計金額匯總 ) |
7 | =[A6,A5.result(),A4.result()].mergex(月號, 科目 ) |
8 | =A7.new(#110000000000+#2:key,#3:累計金額匯總 ) |
… | … |
A9:計算后的結(jié)果集導出并保存到集文件中,即總賬憑證 -later.btx。數(shù)據(jù)結(jié)構(gòu)如下圖:
2.4.2 有序查詢
這樣,我們就按照要求完成了數(shù)據(jù)預處理工作,接下來分兩步驗證報表查詢:
1、定義子程序: 任意給定一個計算指標,能夠快速返回指標匯總值;然后多次調(diào)用子程序來完成 100 個指標的計算,返回結(jié)果集。
2、任意給定 100 個計算指標,快速返回與之對應(yīng)的指標匯總值。
2.4.2.1 多次 ISELECT 查詢
首先,定義一個子程序,任意給定一個計算指標(可能只有科目號前面 N 位,比如前 4 位 /6 位 /8 位 /10 位等,自由組合出現(xiàn)),返回這個指標匯總值。
然后,通過調(diào)用子程序來完成 100 個指標的計算,先定義查詢參數(shù), yyyy 代表查詢年,mm 代表查詢月,比如:查詢 2017 年 1 月的數(shù)據(jù),如下圖:
調(diào)用子程序的樣例:
A | B | C | |
1 | =inityear=2014 | =((yyyy-inityear)*12+mm)10000000000 | =file("總賬憑證 -later.btx") |
2 | func | ||
3 | =A2.(B1+) | =B3.sort() | |
4 | =C1.iselect@b(C3,key) | ||
5 | =B4.fetch() | ||
6 | return B5.sum(累計金額匯總 ) | /指標參數(shù)列 | |
7 | =func(A2,C7) | [1001,1002] | |
8 | =func(A2,C8) | [2702,153102,12310105,1122,12310101,12310401,12319001,12310201,12310301,12310501,12310601,12310701,12310801,12319101] | |
… | … | … | |
107 | return [A7:A106] |
A1:定義變量 inityear,假定原始數(shù)據(jù)是從 2014 年開始的,所以設(shè)置默認值為 2014;
B1:按照前面相同的規(guī)則生成“月號”。如果參數(shù)是 2017 年 1 月,執(zhí)行結(jié)果如下:
C1:預處理后的數(shù)據(jù)文件對象
A2-C6:子程序代碼。子程序是以語句 func 為主格的代碼塊,結(jié)果用 return 語句返回。這個子程序主要功能是任意給定一個計算指標,返回匯總值。
B3:接收參數(shù)中每一個科目號,利用月號 (月號為 12 位) 加上當前科目號,形成指標的參數(shù)集合。比如傳入?yún)?shù)為:[1214,1207], 則執(zhí)行結(jié)果如下圖:
C3:接著對對指標參數(shù)集合 B3 排序。執(zhí)行結(jié)果如下圖:
B4:根據(jù)指標 C3 中的參數(shù)集合與結(jié)果集文件中有序的 key 字段進行比對查找,返回游標;其中 @b 代表從集文件中讀取。
B5:從游標中獲取記錄,執(zhí)行結(jié)果如下圖:
B6:對累計金額匯總求和,返回指標的計算結(jié)果。
C7:指標 A 的參數(shù)條件 (按科目號前 4 位截取的多個值形成的集合)
C8:指標 B 的參數(shù)條件 (按科目號前 4 位 / 前 6 位 / 前 8 位截取的多個值,形成的參數(shù)集合) ,剩余的 98 個指標,計算的寫法類似 A8,參數(shù)的寫法類似 C8,依次類推到 100。
A7:調(diào)用 func 子程序,把 C7 的指標參數(shù)值傳入到子程序中,子程序計算后返回結(jié)果。
A8:同理,計算指標 B 的結(jié)果集
A107:合并 A7-A106 每個格子的值 (從上往下,100 個指標的計算結(jié)果),返回一個單列數(shù)據(jù)集,可以供報表工具使用。
這樣做已經(jīng)可以利用有序查詢了,但計算 100 個指標還需要執(zhí)行 100 次子程序的 iselect() 函數(shù),依然遍歷太多,編碼過程也比較繁瑣。
2.4.2.2 一次 ISELECT 查詢
那么,有沒有辦法只做一次 iselect 查詢呢?
答案是有!我們可以把 100 個需要計算的指標的科目號都整理好,然后執(zhí)行一次 iselect() 函數(shù),把所有指標匯總結(jié)果都查找出來,這樣,就大功告成了。
這里,需要注意兩個關(guān)鍵點:
1、需要將多個計算指標中的不同科目號進行合并、構(gòu)造主鍵、排序。
2、利用 pos()的函數(shù)技巧,根據(jù)每個計算指標中多個科目號與月號構(gòu)造的主鍵在結(jié)果集中的找到坐標位置 ( 與 key 列字段比對),返回位置序號, 接著根據(jù)位置序號在結(jié)果集中找到的累計金額匯總字段進行求和,求和結(jié)果再按位置序號倒回到每個指標中,即每個指標的匯總值計算完成。
為了便于理解,舉個例子,詳細解釋一下利用 pos() 函數(shù)是如何做到定位計算的?示意圖如下:
解釋:指標 A 和指標 B 的所有科目號合并,然后統(tǒng)一排序生成序號,通過序號在有序結(jié)果集中找到對應(yīng)的金額,再利用位置序號把金額倒回到每個指標中,每個指標下對多個科目號的金額匯總,即指標匯總值。
最終,計算 100 個指標的集算器的 SPL 腳本樣例如下:
A | B | C | D | E | |
1 | /參數(shù)變量 | =now() | =((yyyy-inityear)*12+mm)*10000000000 | ||
2 | [1001,1002,1012] | [2001] | [1101] | [1121,12310106,12310206,12310306,12310406,12310506,12310606,12310706,12310806,12319006,12319106] | [2101] |
… | … | … | … | … | … |
21 | [221102] | [1221,12310102,12310202,12310302,12310402,12310502,12310602,12310702,12310802,12319002,12319102] | [2221] | [1321,1401,1402,1403,1404,1405,1406,1407,1408,1409,1411,1412,1461,1471] | [1403,147101,1471050100] |
22 | =[A2:E21] | =A22.(.(C1+)) | =A22.union().sort() | ||
23 | =file("總賬憑證 -later.btx") | ||||
24 | =A23.iselect@b(C22,key) | =A24.fetch() | =B24.(key) | =B24.(累計金額匯總 ) | |
25 | =A22.(.(C24.pos@b())) | ||||
26 | =A25.(.sum(D24(~))) | ||||
27 | return A26 | =interval@ms(B1,now()) |
A22:把 A2 到 E21 范圍內(nèi) 100 個計算指標的科目號合并起來,其中 A2 格子代表指標 1,B2 代表指標 2,依次類推;合并完后,執(zhí)行結(jié)果如下圖:
B22:對 A22 指標中每一個科目號,分別利用月號 (月號為 2 位)*10000000000,加上當前科目號,形成指標的參數(shù)組集合。執(zhí)行結(jié)果如下圖:
C22:對指標參數(shù)組集合進行合并,然后排序
A23:打開預處理后的集文件對象
A24:根據(jù)指標 C22 中構(gòu)造的參數(shù)組集合與文件中有序的 key 字段進行比對,記錄返回成游標
B24:從游標中獲取記錄,返回所有科目號的查詢到的結(jié)果集,執(zhí)行結(jié)果如下圖:
C24-D24:分別獲取結(jié)果集中的:key、累計金額匯總。
A25:按照 A22 中每個科目號的順序,在 B24 結(jié)果集中利用 key 列與當前科目號 + 月號構(gòu)造的主鍵進行比對,然后返回位置序號,其中 pos() 函數(shù)中 @b 代表使用二分法查找,效率更高,但要求被尋找序列是有序的。運算結(jié)果如下圖:
A26:利用 A25 每個成員坐標位置的序號,在結(jié)果集 B24 中對比找到的累計金額匯總字段進行求和,求和結(jié)果再按位置序號倒回到每個指標中,比如序號 1 返回的結(jié)果代表 A2 的參數(shù)查詢出來的指標 1 匯總結(jié)果,序號 2 返回的結(jié)果代表 B2 的參數(shù)查詢出來的指標 2 匯總結(jié)果。依次類推。即每個指標的匯總值計算完成。執(zhí)行結(jié)果如下圖:
A27:返回結(jié)果集,供報表工具使用。
至此,利用一次遍歷,搞定所有事情,難題迎刃而解!
實測結(jié)果:報表從取數(shù)到展現(xiàn)整個環(huán)節(jié)大概需要1-2秒,其中指標計算部分用時不到1秒。
對于報表的制作過程來說,并不需要做什么改變,只需要把數(shù)據(jù)源切換到集算器即可。假定報表工具是潤乾報表 V5:
首先,導入 excel 表樣,創(chuàng)建數(shù)據(jù)集類型為集算器,選取已經(jīng)做好的 dfx 腳本;同時設(shè)置相應(yīng)的查詢參數(shù)等。
然后,在報表的每個單元格里分別按順序取值,即可得到每個指標匯總結(jié)果;比如單元格 C5 的表達式寫法:=ds1.select(#1)(1),單元格 C6 的寫法:=ds1.select(#1)(2),……,報表單元格表達式從上往下,依次類推。樣例如下圖:
在實際的報表開發(fā)過程中,當我們遇到問題,往往并不能一開始就想到最優(yōu)的解決辦法。我們可以試著先用最簡單、最容易的辦法實現(xiàn),然后再一步步進行優(yōu)化;對比每種方案的存在的缺陷及改進后所帶來的性能提升,從而最終滿足業(yè)務(wù)需求。
本文中我們就采用了這種方式,逐步優(yōu)化的步驟如下:
1、多次遍歷方案
2、一次遍歷方案
3、預先匯總方案,查詢部分在 2 的基礎(chǔ)上進行優(yōu)化
4、有序計算方案
整個過程中,我們用到的集算器相關(guān)技術(shù)包括:游標、管道、遍歷復用、數(shù)據(jù)外置、分組子集、跨行組計算、有序計算 / 查詢、二分法查找、位置序號等。
了解了這些概念并熟練掌握集算器相關(guān)的函數(shù)后,我們就可以寫出高效的代碼,快速實現(xiàn)報表數(shù)據(jù)集的準備工作!
實際業(yè)務(wù)中,我們針對客戶提供的生產(chǎn)環(huán)境數(shù)據(jù) (原數(shù)據(jù)表大概 6000 萬明細記錄),利用這種方案進行了 POC 實測。結(jié)果表明,原來需要 30-40 秒才能呈現(xiàn)的資產(chǎn)負債表,現(xiàn)在提高到 1-2 秒內(nèi)。而且,集算器腳本可以與報表模板一起管理,從而有效降低應(yīng)用管理的復雜度。
使用集算器進行預處理計算后,形成的數(shù)據(jù)緩存文件,能夠很好的優(yōu)化現(xiàn)有報表實現(xiàn)模式,有效解決大數(shù)據(jù)集報表運算慢的難題。
原有模式和引入集算器后的報表系統(tǒng)結(jié)構(gòu)對比如下圖所示:
根據(jù)報表的業(yè)務(wù)特點,通常具體的實現(xiàn)步驟如下:
1、集算器抽取來自數(shù)據(jù)源的數(shù)據(jù),根據(jù)報表的業(yè)務(wù)規(guī)則,取出需要的維度、過濾字段、計算指標等明細記錄。
2、集算器對明細記錄進行預處理計算,生成報表需要的各類指標。
3、計算后的指標以數(shù)據(jù)緩存文件的方式存放,可以按照業(yè)務(wù)種類、模塊關(guān)系、時間順序進行多級目錄管理,也可以和報表模板一起管理。
4、報表工具通過 JDBC 方式調(diào)用集算器,計算結(jié)果返回給報表工具呈現(xiàn)。
5、可以設(shè)置計劃任務(wù)定時執(zhí)行,完成上述各項數(shù)據(jù)預處理動作。