SPL 简化 SQL 案例详解:组内运算

发布时间:2020-08-06 11:23:13 作者:raqsoft
来源:网络 阅读:149

在开发数据库应用时,经常在分组后需要对组内数据进行计算,例如:列出近3年每年都发表过论文的学生名单(按论文发表年分组后列出每年都出现的学生清单),统计全部参加了历次培训的员工(按培训分组后统计每次都有的员工人数),选出每位客户的高尔夫成绩最高的三天(按客户分组后按天计算成绩的Top3)等等。

对于SQL来说,完成这类运算一般较为复杂,需要嵌套多层,往往导致代码难以理解和维护困难。而SPL非常擅长这类组内计算,并且很容易和JAVA、报表工具集成。下面用一个例子来进行说明。

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

OrderIDClientSellerIdOrderDateAmount
10808OLDWO22015/1/11660
10809WELLI72015/1/1140
10810LAUGB22015/1/1187
10811LINOD82015/1/2852
10812REGGC52015/1/21852
10813RICAR12015/1/5648
10814VICTE32015/1/52070
10815SAVEA22015/1/540
10816GREAL42015/1/68891

要求是请根据该表统计出指定年份(例如2015)中,每个月销售金额均排在前20名的客户名称。

解决这个问题的思路是,首先选出2015年销售数据,按月分组统计,然后循环选出每月销售前20名的客户,最后求各组的交集。

这样的计算在SQL很难直接表达,而SPL则可以很自然地逐步计算,将复杂问题拆分后得出最终结果。

SPL代码:


A
1=db.query("select   * from sales")
2=A1.select(year(ORDERDATE)==YEAR)
3=A2.group(month(ORDERDATE))
4=A3.(~.group(CLIENT))
5=A4.(~.top(-20;sum(AMOUNT)))
6=A5.(~.new(CLIENT,sum(AMOUNT):MONTH_AMOUNT))
7=A6.(~.(CLIENT))
8=A7.isect()

A1:从数据库中读出销售表;

A2:=A1.select(year(ORDERDATE)==YEAR),从销售数据中取出指定年份的数据。通过网格参数YEAR灵活适应不同的查询需求,此例中YEAR=2015。当然,这里的过滤也可以直接通过A1中的SQL来完成。

A3:=A2.group(month(ORDERDATE)),使用group函数,将2015年的数据按照月份分组。这里需要特别说明的是,SPL的数据分组,是真实的分组,会根据需要将数据实际分为多个组。这和SQL中的情况不同,SQL中的group by命令是直接计算分组的汇总值,并不能保留中间的分组结果。因此也就无法对分组的数据进一步计算了。分组后,A3中的数据如下:

SPL 简化 SQL 案例详解:组内运算

我们还可以继续通过双击来查看详细数据,例如下面是三月份的数据:

SPL 简化 SQL 案例详解:组内运算

为了统计每个月中,每个客户的月销售总额,需要再按客户分组。在SPL中,只需要对每个月的数据进行循环,分别按客户分组就可以了。循环组内成员时,还可以使用简洁的A.(x)来执行,而不必再去编写循环代码。

A4:=A3.(~.group(CLIENT))

再次分组后,A4中,每个月的数据就都是分组的分组了:

SPL 简化 SQL 案例详解:组内运算

其中3月按客户分组的数据如下:

SPL 简化 SQL 案例详解:组内运算

可以看到,3月数据中的每个分组,都是某个客户的交易数据。

注意,上述代码中的“~”表示分组中的每个成员,针对“~”书写的代码就是组内运算代码,比如上面的~.group(CLIENT)。

接下里,继续通过组内运算求出每月排名前20的大客户:

A5:=A4.(~.top(-20;sum(AMOUNT)))

A6:=A5.(~.new(CLIENT,sum(AMOUNT):MONTH_AMOUNT))

在A5中,循环每个月的数据,使用top函数计算出了每月销售额最大的前20个客户。在A6中列出了这些客户的名称及月销售额。A6中计算的结果和3月份的统计数据如下:

SPL 简化 SQL 案例详解:组内运算

最后列出分组内的Client字段,并对各分组求交集:

A7:==A6.(~.(CLIENT))

A8:=A7.isect()

在A7中列出每月销售额最大的20个客户名称。最后在A8中求12个月的客户名称交集,得到我们需要的结果如下:

        SPL 简化 SQL 案例详解:组内运算

从这个问题中可以看到,SPL可以轻松实现结构化数据的组内计算,解决问题时的思路非常直观。组内计算时也能够轻松地完成再分组、排序等计算,每一步的数据处理更加清晰自然。此外,SPL提供的组成员循环、求交集等运算也使得计算变得更为简易,大大减少了代码量。

对于计算结果,除了导出数据,SPL还可以直接以被调用的方式向报表工具或java程序提供数据,调用方法和普通数据库相似,使用它提供的JDBC接口即可向java主程序返回ResultSet形式的计算结果,具体方法可参考相关文档。【Java如何调用SPL脚本】


推荐阅读:
  1. SPL 简化 SQL 案例详解:固定分组
  2. SPL 简化 SQL 案例详解:行间计算

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

sql 组内运算

上一篇:Python中断言的使用方法

下一篇:microsoftonedrive的意思是什么

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》