SPL 简化 SQL 案例详解:计算各组前 N 行

取出各组的前N行数据是较常见的运算,比如:每个月每种产品销量最高的五天是哪五天,每位员工涨薪最多的一次是哪次,高尔夫会员成绩最差的三次是哪三次,等等。在SQL中,这类运算要用窗口函数以及keep/top/rownumber等高级技巧来间接处理,代码难度较大。而且许多数据库(如MySQL)还没有这些高级功能,就只能用更复杂的JOIN语句和嵌套的子查询来实现了。如果还涉及多层分组,多级关联,计算过程会更加复杂。

而在SPL中,由于top函数可以按行号、最大值、最小值等方式取分组中的前N,因此解决此类问题更加容易、清晰。下面就用一个例子来说明。

数据库表golf存储着多位会员的高尔夫得分情况,部分数据如下:

ID

USER_ID

SCORE

DATETIME

1

1

35

2014-07-01 11:00:00

2

1

17

2014-07-01 12:00:00

3

2

36

2014-07-01 11:00:00

4

2

27

2014-07-01 12:00:00

5

1

66

2014-07-02 11:00:00

6

1

77

2014-07-02 12:00:00

7

2

93

2014-07-02 12:00:00

8

1

27

2014-07-03 12:00:00

9

1

48

2014-07-03 18:00:00

10

1

36

2014-07-04 18:00:00

11

3

77

2014-07-01 12:00:00

12

3

68

2014-07-02 13:00:00

13

4

25

2014-07-02 13:00:00

请取出每位会员成绩最好的三次得分情况。

SPL代码:


A

1

=db.query("select * from golf")

2

=A1.group(USER_ID)

3

=A2.(~.top(-3;SCORE))

4

=A3.union()

5

>file("golf.csv").export@ct(A4)

A1:从数据库取数。点击该单元格,可以看到取数结果:

A2=A1.group(USER_ID)。将A1的结果按照USER_ID,也就是会员分组,结果如下:

每行代表一组,其中是一个会员的所有得分记录,双击浅蓝色格子,可以看到组内成员,如下:

A3= A2.(~.top(-3;SCORE))。计算出每组数据SCORE字段前三的记录。这里的“~”表示每组数据,~.top()表示依次对每组数据应用函数top。函数top可以取得数据集的前N条记录,比如top(3;SCORE)表示按SCORE升序排列,取前3条(即最小值);top(-3;SCORE)表示按降序排列,取前3条(即最大值,也就是这里的最好成绩)。这一步的计算结果如下:

A4=A3.union()。将各组数据合并,结果如下:

A5>file("golf.csv").export@ct(A4)

将计算结果导出到" golf.csv "文件,以便通过excel等工具查看:

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

以下是广告时间

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



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