SPL 簡化 SQL 案例詳解：組內運算

在開發數據庫應用時，經常在分組后需要對組內數據進行計算，例如：列出近3年每年都發表過論文的學生名單（按論文發表年分組后列出每年都出現的學生清單），統計全部參加了歷次培訓的員工（按培訓分組后統計每次都有的員工人數），選出每位客戶的高爾夫成績最高的三天（按客戶分組后按天計算成績的Top3）等等。

對于SQL來說，完成這類運算一般較為復雜，需要嵌套多層，往往導致代碼難以理解和維護困難。而SPL非常擅長這類組內計算，并且很容易和JAVA、報表工具集成。下面用一個例子來進行說明。

表sales存儲著多年的訂單數據，部分數據如下：

要求是請根據該表統計出指定年份（例如2015）中，每個月銷售金額均排在前20名的客戶名稱。

解決這個問題的思路是，首先選出2015年銷售數據，按月分組統計，然后循環選出每月銷售前20名的客戶，最后求各組的交集。

這樣的計算在SQL很難直接表達，而SPL則可以很自然地逐步計算，將復雜問題拆分后得出最終結果。

SPL代碼：

A1：從數據庫中讀出銷售表；

A2：=A1.select(year(ORDERDATE)==YEAR)，從銷售數據中取出指定年份的數據。通過網格參數YEAR靈活適應不同的查詢需求，此例中YEAR=2015。當然，這里的過濾也可以直接通過A1中的SQL來完成。

A3：=A2.group(month(ORDERDATE))，使用group函數，將2015年的數據按照月份分組。這里需要特別說明的是，SPL的數據分組，是真實的分組，會根據需要將數據實際分為多個組。這和SQL中的情況不同，SQL中的group by命令是直接計算分組的匯總值，并不能保留中間的分組結果。因此也就無法對分組的數據進一步計算了。分組后，A3中的數據如下：

我們還可以繼續通過雙擊來查看詳細數據，例如下面是三月份的數據：

為了統計每個月中，每個客戶的月銷售總額，需要再按客戶分組。在SPL中，只需要對每個月的數據進行循環，分別按客戶分組就可以了。循環組內成員時，還可以使用簡潔的A.(x)來執行，而不必再去編寫循環代碼。

A4：=A3.(~.group(CLIENT))

再次分組后，A4中，每個月的數據就都是分組的分組了：

其中3月按客戶分組的數據如下：

可以看到，3月數據中的每個分組，都是某個客戶的交易數據。

注意，上述代碼中的“~”表示分組中的每個成員，針對“~”書寫的代碼就是組內運算代碼，比如上面的~.group(CLIENT)。

接下里，繼續通過組內運算求出每月排名前20的大客戶：

A5：=A4.(~.top(-20;sum(AMOUNT)))

A6：=A5.(~.new(CLIENT,sum(AMOUNT):MONTH_AMOUNT))

在A5中，循環每個月的數據，使用top函數計算出了每月銷售額最大的前20個客戶。在A6中列出了這些客戶的名稱及月銷售額。A6中計算的結果和3月份的統計數據如下：

最后列出分組內的Client字段，并對各分組求交集：

A7：==A6.(~.(CLIENT))

A8：=A7.isect()

在A7中列出每月銷售額最大的20個客戶名稱。最后在A8中求12個月的客戶名稱交集，得到我們需要的結果如下：

???????

從這個問題中可以看到，SPL可以輕松實現結構化數據的組內計算，解決問題時的思路非常直觀。組內計算時也能夠輕松地完成再分組、排序等計算，每一步的數據處理更加清晰自然。此外，SPL提供的組成員循環、求交集等運算也使得計算變得更為簡易，大大減少了代碼量。

對于計算結果，除了導出數據，SPL還可以直接以被調用的方式向報表工具或java程序提供數據，調用方法和普通數據庫相似，使用它提供的JDBC接口即可向java主程序返回ResultSet形式的計算結果，具體方法可參考相關文檔。【Java如何調用SPL腳本】