報表工具擅長統計圖和表格的設計、風格美化、查詢界面、錄入填報、導出列印,是應用最廣泛的工具之一。而報表中經常存在着複雜運算,對報表設計者的技術能力提出了很高的要求,是報表設計最大的障礙之一。
某公司有一張HighGrowth SalesMan of the Year報表,主要通過銷售資料來分析連續三個月銷售額增長超過10%的銷售人員,展示他們的銷售額amount,銷售額環比Amount LRR,客戶數量Client Count,客戶數量環比Client LRR等名額。報表式樣如下圖:
報表的主要的資料源是每月銷售資料sales表,它存貯着銷售人員每個月的銷售情況,以salesMan和month為主鍵。結構如下圖:
可以看出,計算連續三個月銷售額增長超過10%的銷售人員名單,是這份報表最複雜的部分,隻要計算出這個名單,那麼剩下的部分都可以輕易地利用報表工具呈現出來。讓我們看一下用SQL和集算器是如何計算的。
用SQL語句解答
01 WITH A AS
02 (SELECT salesMan,month,amount/lag(amount)
03 OVER(PARTITION BY salesMan ORDER BY month)-1 rising_range
04 FROM sales),
05 B AS
06 (SELECTsalesMan,
07 CASE WHEN rising_range>=1.1 AND
08 lag(rising_range) OVER(PARTITION BY salesMan
09 ORDER BY month)>=1.1 AND
10 lag(rising_range,2) OVER(PARTITION BY salesMan
11 ORDER BY month)>=1.1
12 THEN 1 ELSE 0 END is_three_consecutive_month
13 FROM A)
14 SELECT DISTINCT salesMan FROM B WHERE is_three_consecutive_month=1
1、1-4行:使用SQL-2003的視窗函數求得每個銷售人員每個月的銷售額環比rising_range,其中lag是求相對的上個月銷售額,這裡用WITH語句生成了一個獨立子查詢。
2、5-13行:繼續使用視窗函數,求得每條記錄中銷售人員的連續增長标志is_three_consecutive_month_gains,其中鄰近三個月的rising_range同時大于1.1的銷售人員,這個标志為1;否則等于0,這裡用到了case when的技巧。最後仍然使用”WITH”語句生成獨立子查詢B。
3、14行:依據上兩步的結果,求得符合報表條件的銷售人員,即is_three_consecutive_month_gains等于1的記錄。這裡需要用distinct過濾掉重複的銷售人員。
集算器的解決
A1:按銷售人員對資料分組。每組是一個銷售人員的所有資料,按月份從小到大排序。
A2:引用上一步的計算結果,選出A1中符合條件的組。條件來自A1運算區域中的最後一個格,即B3格。B2和B3都屬于A1的運算區域。把條件分步寫在多個格裡可以降低難度。
B3:條件判斷。如果組内連續3個月環比大于1.1,則這組資料滿足條件。這裡amount[-1]是對于目前資料的上一條資料,amount/ amount[-1]既環比。pselect用來獲得組内序号,遇到組内第一條符合條件的資料時,pselect就立即傳回序号不再重複計算。
A4:獲得A2中銷售人員的編号,這個結果将以JDBC的形式傳回給報表工具使用。
這個例子按照”分步驟”計算的方法會很清晰,比較适合用存儲過程,但報表開發人員往往不能随意在資料庫中添加存儲過程,是以一般還是需要使用SQL語句來解決。普通的SQL-92語句解決此類問題将會非常費力,這裡采用尚未廣泛使用的SQL-2003标準,可以降低難度。即使這樣,仍然需要面對大段難懂的SQL,對普通報表開發人員而言,這無疑是個巨大的挑戰。
集算器解決此類計算則更為靈活輕松。它使用類似Excel的網格風格來書寫表達式,天然分步;單元格可以互相引用計算結果,省去了複雜的嵌套查詢,也無需定義雜亂的變量;集算器還提供了相對位置、序号引用、分組後分步計算等針對批量資料計算的功能,可以極大的簡化計算步驟。