SPL 簡化 SQL 案例詳解：固定分組

在數據庫應用開發中，我們經常需要面對復雜的SQL式計算，固定分組就是其中一種。固定分組的分組依據不在待分組的數據中，而是來自于外部，比如另一張表、外部參數、條件列表等。對于特定類型的固定分組，用SQL實現還算簡單（比如：分組依據來自另一張表，且對分組次序沒有要求），但對于比較通用、靈活的要求，實現起來就困難了。

而對于SPL來說，完全可以輕松解決固定分組中的各類難題，下面就用幾個例子來說明。

表sales存儲著訂單記錄，其中CLIENT列是客戶名，AMOUNT列是訂單金額。表sales的部分數據如下：

要求將sales按照“潛力客戶列表”進行分組，并對各組的AMOUNT列匯總求和。這里的“潛力客戶”就是一種固定分組，可能來自于外部不同的條件設定：

?

案例一：潛力客戶列表來自于另外一張表potential的Std字段，只有四條記錄，依次為：ANATR、BERGS、LACOR、ZTOZ，并且客戶ZTOZ不在sales表中。在輸出結果時，要求按照上述記錄順序來分組匯總。

如果我們對分組的順序沒有要求，那么SQL可以較簡單地實現本案例：

select potential.std as client, sum(sales.amount) as amount from potential left join client on potential.std=sales.client group by potential.std。

但如果像本案例中要求的那樣，按照特定的順序來分組，那么用SQL實現的話就必須制造一個用于排序的字段，最后還要用子查詢去掉這個字段。而用SPL實現則會簡單很多，代碼如下：

A1、B1：從數據庫檢索數據，分別命名為sales和potential，如下所示：

A3：=sales.align@a(potential:STD,CLIENT)

這句代碼使用了函數align，它將sales的Client字段按照potentail的Std字段順序對位分為四個組，如下：

可以看到，前三個組是sales中已有的數據，而第四個組不在sales中，因此是空值。另外，函數align的參數選項@a表示取出分組中的所有數據，如果不用這個函數選項，則只取每組的第一條。

A4：=A3.new(potential(#).STD:CLIENT,~.sum(AMOUNT):AMOUNT)

這句代碼用函數new產生新的序表，成員一個是potential.STD，即potential的Std字段；另一個是~.sum(AMOUNT)，即對A3中每組數據的Amount字段的求和結果。最終結果如下：

?

案例二：潛力客戶列表是固定值，但客戶的數量較多。

如果客戶的數量較少，用SQL時可以用union語句將所有的客戶拼成一個假表，如果客戶數量較多，這么做就可不取了，必須新建一張表持久保存數據才行。而用SPL實現卻可以省去建表的麻煩，代碼如下：

上述代碼中，A2是個字符串組成的序列，并命名為potential。A3、A4可以像案例一那樣對potential訪問，直接引用其成員。

?

案例三：潛力客戶列表是外部參數，形如："BSBEV","FAMIA","GALED"。

外部參數經常變化，在SQL中用union來制造假表就更不方便了，只能創建一個臨時表，將參數解析后一條條插入臨時表，再進行后續的計算。而用SPL實現則不必建立臨時表，具體實現過程如下：

首先定義一個參數clients，如下：

然后修改腳本文件，如下：

運行腳本，并輸入的參數值，假設參數值為"BSBEV","FAMIA","GALED"，如下：

分組依據輸入不同，最終計算結果也不一樣。上面輸入對應的結果如下：

?

案例四：固定分組的分組依據可以是數值，也可以是條件，比如：將訂單金額按照1000、2000、4000劃分為四個區間，每個區間一組訂單，統計各組訂單的總額。

如果條件是已知，那就可以將這些條件寫死在SQL里，如果條件是動態的外部參數，則需要用JAVA等高級語言拼湊SQL，過程非常復雜。而由于SPL支持動態表達式，因此可以輕松實現本案例，代碼如下：

上述代碼中，變量byFac是本案例的分組依據，包含四個條件。byFac也可以是外部參數，或者來自于數據庫中的視圖或表。A4中的最終結果如下：

?

案例五：

??????? 上述條件分組中，條件恰好都沒有發生重疊，但實際情況中發生重疊的情況也很常見，比如將訂單金額按照如下規則分組：

??????? 1000至4000：常規訂單r14

??????? 2000以下：非重點訂單r2

??????? 3000以上：重點訂單r3

這時，r2和r3都會和r14發生條件重疊。條件發生重疊時，我們有時希望數據不重疊，即先取出符合r14的數據，再從剩下的數據中篩選出r2，以此類推。

SPL的函數enum支持數據重疊的條件分組，如下：

A3中的分組結果如下：

A4計算結果如下：

而有時，我們希望分組結果中包含重疊數據，即先從sales中取出符合r14的數據，再從完整的sales中取出符合r2的數據，以此類推。此時，只需要在函數enum中使用函數選項@r，即將A3中的代碼改為：=sales.enum@r(byFac,AMOUNT)，此時分組結果如下：

如圖中紅框所標注的，第二組數據中出現了滿足1000~4000條件的數據。最后的計算結果如下：

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