SPL 簡化 SQL 案例詳解：行間計算

在數據庫應用開發中，我們經常需要面對復雜的SQL計算，行間計算就是其中一種。比如：用每月的銷售額除以上月的銷售額，求比上期，或用每月銷售額除以去年同月的銷售額，求同期比。有些數據庫系統沒有提供SQL2003標準的窗口函數，或者支持得不完備，那么完成行間計算就需要改變思路，使用連接（join）運算替代，不僅難以理解而且運算效率低下。即使可以使用窗口函數，計算時仍要面臨嵌套子查詢等問題，SQL語句仍舊復雜、冗長。而SPL則可以通過更簡單、直觀的代碼來實現行間計算，下面就用一個例子來說明。

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

現在要求根據該表計算出指定時間段內各月份銷售額的比上期和同期比。

SPL代碼：

A1：按時間段從數據庫查詢數據，begin和end是網格參數，比如begin="2013-07-01"，end="2015-03-31"。部分查詢結果如下：

?????? A2：=A1.groups(year(ORDERDATE):y,month(ORDERDATE):m;sum(AMOUNT):mAmount)

?????? 這句代碼對訂單按照年份和月份進行分組，并匯總求得每月的銷售額。函數groups可進行分組匯總運算，其參數分為兩部分，分號之前是分組表達式，即：year(ORDERDATE):y,month(ORDERDATE):m，分號之后是匯總表達式，即sum(AMOUNT):mAmount。匯總后的字段命名為mAmount。部分計算結果如下：

?????? A3：=A2.derive(mAmount/mAmount[-1]:lrr)

?????? 這句代碼在A2基礎上增加一個新的字段lrr，即按月比上期，其表達式為mAmount/mAmount[-1]。SPL可以用 [N]或[-N]來表達相對于當前記錄的之后第N條記錄，或之前第N條記錄，因此代碼中mAmount代表當期銷售額，mAmount[-1]代表上期銷售額。計算結果如下：

?????? 需要注意的是，初始月份的比上期值為空（即2013年7月）。

?????? A4：=A3.derive(mAmount/mAmount[-12]:yoy)

?????? 這句代碼在A4的基礎上增加一個新的字段yoy，即月銷售額的同期比。需要注意的是，初始年份（即2013年）各月份的同期比衡為空。部分結果如下：

?????? A5：=A4.sort(y:-1,m:-1)

為了看起來更清晰，我們將A5按照年月逆序進行排序。結果如下：

?????? A6：>file("sales.csv").export@ct(A5)

?????? 這句代碼將計算結果導出到" sales.csv "文件，以便通過excel等工具來查看：

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