格間計算效能提升方案

一般情況下，如果報表中存在很多格間計算（即單元格之間的關聯計算），那麼通常會影響報表效能。這是因為：

1、格間計算很難分步驟編寫，導致運算過程很難最佳化。

2、格間計算可能需要多次遍歷單元格集才能完成運算。

3、格間計算往往要利用較多隱藏格作為中間變數。而隱藏格除格值外，還記錄了很多與顯示相關的屬性值，比如：字型、顏色、顯示方式等。即便設定了單元格隱藏，但這些屬性還在，依然會佔用記憶體，影響計算速度。

不過在潤乾報表的集算指令碼支援下，這個問題能夠得到很好的解決。下面我們就透過一個典型的例子——《僱員銷售情況排名》報表，來看下傳統方式和潤乾報表的對比：

報表要求如下：

按照員工姓名排序，根據訂單總價計算排名，並且計算“和上一名的差距”。

一、傳統實現方式

1、首先定義 sql 資料集 ds1，其 sql 如下：

SELECT EMPLOYEE.EID,MAX(EMPLOYEE.NAME) 姓名,sum(訂單明細. 數量 * 訂單明細. 單價) 訂單總價 FROM EMPLOYEE, 訂單, 訂單明細 WHERE  訂單. 訂單 ID = 訂單明細. 訂單 ID  AND  EMPLOYEE.EID = 訂單. 僱員 ID group by EMPLOYEE.EID order by 姓名

可以看到，為了讓輸出結果要按姓名排序，SQL 中要寫作“ORDER BY 姓名”。

2、報表設計

其中，

僱員姓名、訂單總價：直接來自 sql。

排名：C2 單元格利用格間計算實現排名計算。計算當前訂單總價的排名時，需要先找出所有訂單總價中比本格值大的單元格，然後符合條件的單元格數加一就是排名。也就是說每計算一個僱員的排名，就要遍歷一邊所有的訂單總價。

類似地，D2 計算“和上一名的差距”時還要再做一次按條件遍歷。這個遍歷還存在引用關係的處理，要等排名列計算完才能算差距列，但報表計算次序一般是從上到下的，中途處理引用關係會導致多次失敗的試算（試圖計算某格，如果發現該格引用的格未計算就要存放等待著下一輪再計算），失敗的試算會浪費大量時間。

上述格間計算對訂單總價遍歷了 n 次，n 是僱員個數，兩個格間運算的總體複雜度是 2*N*N。

二、潤乾報表方案

採用潤乾報表方案（結合集算器實現），可以編寫過程化的集算指令碼代替格間計算，從而提升報表效能。

1、編寫集算器指令碼

對於本報表，編寫指令碼如下（儲存為 sales.dfx）：

	A
1	=connect(“demo”)
2	=A1.query(“SELECT EMPLOYEE.EID,MAX(EMPLOYEE.NAME) 姓名,sum(訂單明細. 數量 * 訂單明細. 單價) 訂單總價,null 排名,0 和上一名的差距 FROM EMPLOYEE, 訂單, 訂單明細 WHERE  訂單. 訂單 ID = 訂單明細. 訂單 ID  AND  EMPLOYEE.EID = 訂單. 僱員 ID group by EMPLOYEE.EID order by 訂單總價 desc”)
3	=A1.close()
4	=A2.run(#: 排名,if(#!=1, 訂單總價 [-1]- 訂單總價,0): 和上一名的差距)
5	=A4.sort(姓名)
6	return A5

程式碼說明：

A1：連線資料庫。

A2：用 sql 計算訂單總價，並按照訂單總價降序排序。

A3：關閉資料庫。

A4：對 A2 迴圈計算，“排名”等於當前記錄的行號 #，“和上一名的差距”等於用上一行的訂單總價減去當前行的訂單總價，如果是第一行就直接為 0。

A5：按照姓名重新排序。

A6：返回結果集給報表。

可以看到集算指令碼僅用一次遍歷加一次排序就完成了排名和上一名的差距的計算，也不存在試算以解決引用關係的問題，所有計算都能一次成功進行。本方案的整體複雜度是 N（遍歷計算）+N*logN（排序），比之前的格間計算快得多。

2、配置報表資料集

在報表設計器中定義集算資料集，呼叫 sales.dfx：

3、設計報表

設計表樣如下：