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

在开发数据库应用时,经常在分组后需要对组内数据进行计算,例如:列出近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脚本】


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾