您好,登录后才能下订单哦!
在开发数据库应用时,经常在分组后需要对组内数据进行计算,例如:列出近3年每年都发表过论文的学生名单(按论文发表年分组后列出每年都出现的学生清单),统计全部参加了历次培训的员工(按培训分组后统计每次都有的员工人数),选出每位客户的高尔夫成绩最高的三天(按客户分组后按天计算成绩的Top3)等等。
对于SQL来说,完成这类运算一般较为复杂,需要嵌套多层,往往导致代码难以理解和维护困难。而SPL非常擅长这类组内计算,并且很容易和JAVA、报表工具集成。下面用一个例子来进行说明。
表sales存储着多年的订单数据,部分数据如下:
OrderID | Client | SellerId | OrderDate | Amount |
10808 | OLDWO | 2 | 2015/1/1 | 1660 |
10809 | WELLI | 7 | 2015/1/1 | 140 |
10810 | LAUGB | 2 | 2015/1/1 | 187 |
10811 | LINOD | 8 | 2015/1/2 | 852 |
10812 | REGGC | 5 | 2015/1/2 | 1852 |
10813 | RICAR | 1 | 2015/1/5 | 648 |
10814 | VICTE | 3 | 2015/1/5 | 2070 |
10815 | SAVEA | 2 | 2015/1/5 | 40 |
10816 | GREAL | 4 | 2015/1/6 | 8891 |
要求是请根据该表统计出指定年份(例如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中,只需要对每个月的数据进行循环,分别按客户分组就可以了。循环组内成员时,还可以使用简洁的A.(x)来执行,而不必再去编写循环代码。
A4:=A3.(~.group(CLIENT))
再次分组后,A4中,每个月的数据就都是分组的分组了:
其中3月按客户分组的数据如下:
可以看到,3月数据中的每个分组,都是某个客户的交易数据。
注意,上述代码中的“~”表示分组中的每个成员,针对“~”书写的代码就是组内运算代码,比如上面的~.group(CLIENT)。
接下里,继续通过组内运算求出每月排名前20的大客户:
A5:=A4.(~.top(-20;sum(AMOUNT)))
A6:=A5.(~.new(CLIENT,sum(AMOUNT):MONTH_AMOUNT))
在A5中,循环每个月的数据,使用top函数计算出了每月销售额最大的前20个客户。在A6中列出了这些客户的名称及月销售额。A6中计算的结果和3月份的统计数据如下:
最后列出分组内的Client字段,并对各分组求交集:
A7:==A6.(~.(CLIENT))
A8:=A7.isect()
在A7中列出每月销售额最大的20个客户名称。最后在A8中求12个月的客户名称交集,得到我们需要的结果如下:
从这个问题中可以看到,SPL可以轻松实现结构化数据的组内计算,解决问题时的思路非常直观。组内计算时也能够轻松地完成再分组、排序等计算,每一步的数据处理更加清晰自然。此外,SPL提供的组成员循环、求交集等运算也使得计算变得更为简易,大大减少了代码量。
对于计算结果,除了导出数据,SPL还可以直接以被调用的方式向报表工具或java程序提供数据,调用方法和普通数据库相似,使用它提供的JDBC接口即可向java主程序返回ResultSet形式的计算结果,具体方法可参考相关文档。【Java如何调用SPL脚本】
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。