绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
SPL 简化 SQL 案例详解:行间计算
2019-08-28 10:17:53

在数据库应用开发中,我们经常需要面对复杂的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:按时间段从数据库查询数据,beginend是网格参数,比如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]代表上期销售额。计算结果如下:

       需要注意的是,初始月份的比上期值为空(即20137月)。

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


分享好友

分享这个小栈给你的朋友们,一起进步吧。

数据分析与数据挖掘
创建时间:2020-06-17 15:23:29
数据分析是指用适当的统计分析方法对收集来的大量数据进行分析,将它们加以汇总和理解并消化,以求大化地开发数据的功能,发挥数据的作用。 数据挖掘是指从大量的数据中通过算法搜索隐藏于其中信息的过程。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • 栈栈
    专家
戳我,来吐槽~