SPL 简化 SQL 案例详解：行间计算

在数据库应用开发中，我们经常需要面对复杂的SQL计算，行间计算就是其中一种。比如：用每月的销售额除以上月的销售额，求比上期，或用每月销售额除以去年同月的销售额，求同期比。有些数据库系统没有提供SQL2003标准的窗口函数，或者支持得不完备，那么完成行间计算就需要改变思路，使用连接（join）运算替代，不仅难以理解而且运算效率低下。即使可以使用窗口函数，计算时仍要面临嵌套子查询等问题，SQL语句仍旧复杂、冗长。而SPL则可以通过更简单、直观的代码来实现行间计算，下面就用一个例子来说明。

       表sales存储着多年的订单数据，部分数据如下：

OrderID	Client	SellerId	OrderDate	Amount
10248	VINET	5	2013/7/4	2440
10249	TOMSP	6	2013/7/5	1863.4
10250	HANAR	4	2013/7/8	1813
10251	VICTE	3	2013/7/8	670.8
10252	SUPRD	4	2013/7/9	3730
10253	HANAR	3	2013/7/10	1444.8
10254	CHOPS	5	2013/7/11	625.2
10255	RICSU	9	2013/7/12	2490.5
10256	WELLI	3	2013/7/15	517.8

现在要求根据该表计算出指定时间段内各月份销售额的比上期和同期比。

SPL代码：

	A
1	=db.query("select * from sales where   ORDERDATE>=? and ORDERDATE<=?",begin,end)
2	=A1.groups(year(ORDERDATE):y,month(ORDERDATE):m;sum(AMOUNT):mAmount)
3	=A2.derive(mAmount/mAmount[-1]:lrr)
4	=A3.derive(mAmount/mAmount[-12]:yoy)
5	=A4.sort(y:-1,m)
6	>file("sales.csv").export@ct(A5)

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脚本】