简化SQL式计算之行间计算

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

 

         表sales存储着多年的订单数据，请根据该表计算出指定时间段内各月份的销售额比上期和同期比。部分源数据如下：

 

         集算器代码：

 

         A1：按时间段从数据库查询数据，begin和end是外部参数，比如begin="2011-01-01 00:00:00"，end="2014-07-08 00:00:00"。部分查询结果如下：

 

         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]。集算器可以用 [N]或[-N]来表达相对于当前记录之后的第N条记录，或之前的第N条记录，因此代码中mAmount代表当期销售额，mAmount[-1]代表上期销售额。计算结果如下：

 

         需要注意的是，初始月份的比上期值为空（即2011年1月）。

         A4=A3.sort(m)

 

         这句代码将A3按照月、年排序，以便计算同期比。完整的代码应当是：=A3.sort(m,y)，由于A3本来就是按年排序的，因此只需按月排序就可以达到目的，即A3.sort(m)，这样性能也高。部分计算结果如下：

 

         A5=A4.derive(if(m==m[-1],mAmount/mAmount[-1],null):yoy)

 

         这句代码在A4的基础上增加一个新的字段yoy，即月销售额的同期比，其表达式为if(m==m[-1],mAmount/mAmount[-1],null)，这表示月份相同时才进行同期比计算。函数if有三个参数，其中m==m[1-]为布尔表达式，当该条件为True时，返回mAmount/mAmount[-1]，当该条件为False时，返回null。需要注意的是，初始年份（即2011年）各月份的同期比衡为空。部分结果如下：

 

         如果观察不便，可以加一行代码A6=A5.sort(y:-1,m)，即将A5按照年逆序月正序进行排序。需要注意的是，数据只到2014年7月为止。结果如下：

 

另外，集算器可被报表工具或java程序调用，调用的方法也和普通数据库相似，使用它提供的JDBC接口即可向java主程序返回ResultSet形式的计算结果，具体方法可参考相关文档。