SPL 簡(jiǎn)化 SQL 案例詳解：行間計(jì)算

在數(shù)據(jù)庫(kù)應(yīng)用開發(fā)中，我們經(jīng)常需要面對(duì)復(fù)雜的SQL計(jì)算，行間計(jì)算就是其中一種。比如：用每月的銷售額除以上月的銷售額，求比上期，或用每月銷售額除以去年同月的銷售額，求同期比。有些數(shù)據(jù)庫(kù)系統(tǒng)沒有提供SQL2003標(biāo)準(zhǔn)的窗口函數(shù)，或者支持得不完備，那么完成行間計(jì)算就需要改變思路，使用連接（join）運(yùn)算替代，不僅難以理解而且運(yùn)算效率低下。即使可以使用窗口函數(shù)，計(jì)算時(shí)仍要面臨嵌套子查詢等問(wèn)題，SQL語(yǔ)句仍舊復(fù)雜、冗長(zhǎng)。而SPL則可以通過(guò)更簡(jiǎn)單、直觀的代碼來(lái)實(shí)現(xiàn)行間計(jì)算，下面就用一個(gè)例子來(lái)說(shuō)明。

?????? 表sales存儲(chǔ)著多年的訂單數(shù)據(jù)，部分?jǐn)?shù)據(jù)如下：

現(xiàn)在要求根據(jù)該表計(jì)算出指定時(shí)間段內(nèi)各月份銷售額的比上期和同期比。

SPL代碼：

A1：按時(shí)間段從數(shù)據(jù)庫(kù)查詢數(shù)據(jù)，begin和end是網(wǎng)格參數(shù)，比如begin="2013-07-01"，end="2015-03-31"。部分查詢結(jié)果如下：

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

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

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

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

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

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

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

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

為了看起來(lái)更清晰，我們將A5按照年月逆序進(jìn)行排序。結(jié)果如下：

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

?????? 這句代碼將計(jì)算結(jié)果導(dǎo)出到" sales.csv "文件，以便通過(guò)excel等工具來(lái)查看：

除了導(dǎo)出數(shù)據(jù)，SPL還可以直接被報(bào)表工具或java程序調(diào)用，調(diào)用方法和普通數(shù)據(jù)庫(kù)相似，使用它提供的JDBC接口即可向java主程序返回ResultSet形式的計(jì)算結(jié)果，具體方法可參考相關(guān)文檔?！綣ava如何調(diào)用SPL腳本】