SQL集合无序使得完成有序计算比较困难,而这类计算在实际业务中又非常常见,网上有很多讨论,比如:
http://bbs.csdn.net/topics/390959904
http://bbs.csdn.net/topics/390994046
http://bbs.csdn.net/topics/390980889
http://bbs.csdn.net/topics/390998839
http://bbs.csdn.net/topics/390813196
http://bbs.csdn.net/topics/390977682
http://bbs.csdn.net/topics/391020551
http://bbs.csdn.net/topics/390976752
http://bbs.csdn.net/topics/391028980
SQL在完成这类运算时往往采用如下手段:
1、使用窗口函数
对于支持窗口函数的数据库(如Oracle MSSQL),使用窗口函数多数情况下可以简化SQL写法,但需要数据库支持,使用有一定局限。
2、使用复杂SQL
不支持窗口函数的数据库往往要编写复杂SQL,经常要嵌套多层子查询才能完成,书写十分复杂。其中,支持变量定义,按顺序计算(如MySQL)可以简化部分计算。
3、编写存储过程
一些比较复杂的场景,就需要在存储过程中一步步来完成,实现并不简单。
如果主程序是Java的,可以使用免费的集算器来协助,集算器支持有序计算,比SQL要简单许多,下面来看一些具体实例。
同比环比计算
行间计算(如比上期、比同期)在不支持窗口函数的数据库下实现尤为困难,需要更换思路使用连接(join)运算替代,不仅难以理解而且效率低下。即使可以使用窗口函数仍要面临嵌套子查询等问题,SQL语句仍较为冗长。
来看集算器的解法,表sales存储着多年的订单数据,请根据该表计算出指定时间段内各月份的销售额比上期和同期比。部分源数据如下:
集算器脚本:
A1:按时间段从数据库查询数据,并按OrderDate的年月分组汇总Amount,begin和end是外部参数,比如begin=”2011-01-01 00:00:00″,end=”2014-07-08 00:00:00″
A2:在A1基础上增加一个新的字段lrr,即按月比上期和比去年同期,前者表达式为mAmount/mAmount[-1]。集算器可以用 [N]或[-N]来表达相对于当前记录之后的第N条记录,或之前的第N条记录,因此代码中mAmount代表当期销售额,mAmount[-1]代表上期销售额。需要注意的是,初始月份的比上期值为空(即2011年1月)。
A3:将A2按照月、年排序,以便计算同期比。完整的代码应当是:=A3.sort(m,y),由于A3本来就是按年排序的,因此只需按月排序就可以达到目的,即A3.sort(m),这样性能也高。
A4:在A3的基础上计算销售额的同期比,月份相同时才进行同期比计算。