SPL 查询与报表计算实战指南 - 3 在有序情况下引用相邻的成员

3 在有序情况下引用相邻的成员

这类任务是指在遍历或循环中,访问相对于当前成员的其他位置或区间的成员,属于较复杂的有序计算。SQL 要通过窗口函数(lag/lead)获取相对位置的成员,通常会配合子查询,代码繁琐,如果涉及分组子集中的相对位置时,代码会更难写。

1:计算月增长额的最大值

源数据:按月份排序的一年12个月的订单金额。

目标:计算出每个月相对于上个月的增长额,求其中最大的增长额。

Picture29png

SPL代码:


A

1

$select month(OrderDate) as month,sum(Amount) as subTotal from Orders.txt where year(OrderDate)=2022 group by month(OrderDate)

2

=A1.(if(#>1,subTotal - subTotal[-1],0)).max()

A1:加载数据。

Picture30png
A2=A1.(if(#>1,subTotal - subTotal[-1],0))… 循环 A1 中的每条记录,如果不是第 1 行,当月增长额 = 当前记录的金额 - 上一条记录的金额,如果是第 1 行,当月增长额为 0。~[-1].subTotal 是相对位置语法,表示上一条记录的金额,简写做 subTotal[-1]。

Picture31png

l 知识点:相对位置

SPL可以用中括号表示相对位置,形如~[n]F[n]

Picture32png
~: 循环函数中的当前成员或当前记录。
F: 循环函数中当前记录的字段。
n: 相对于当前成员的序号,n>0 时,表示后面的第 n 个成员;n<0 时,表示前面的第 n 个成员;n=0 时表示当前成员,可以省略不写。

…max() 求最大值,结果是 6094.9

Picture33png

2:统计各部门大订单的累积值

源数据:订单表、部门表。

目标:规定金额大于2000的订单为大订单,否则为小定单。大订单的累积值是指按部门分组,组内按日期排序后,从第一个订单或前一个大订单后的小订单开始,一直到当前大订单的累积值。现在要找出每个大订单及其累积值。

Picture34png

SPL代码:


A

1

$select o.OrderId,o.Amount,o.OrderDate,e.Dept,null as SubTotal from Orders.txt o,Employee.txt e where o.SellerId=e.EId order by e.Dept,o.OrderDate

2

=A1.run(SubTotal+=if(Dept==Dept[-1] && Amount[-1]<=2000, SubTotal[-1]+Amount, Amount))

3

=A2.select(Amount>2000)

A1:加载数据,关联订单表和部门表,按部门和日期排序,新增计算列累积值SubTotal,初值为空。

Picture35png
A2:用 run 函数循环修改 A1 的每条记录,返回修改结果。当 部门不变且上个订单是小订单时,用 SubTotal 累积订单金额,也就是用上一个 SubTotal+ 当前的订单金额;否则(部门变化时或上个订单是大订单时)将 SubTotal 重置为当前的订单金额。[-1] 表示上一条记录。

Picture36png

A3:选出回款月份所在的记录。

3获取各部门的订单中前后区间金额之和大于5000的订单

数据源:订单表、部门表。

目标:按部门分组,组内按日期字段排序后,找到每个订单之前和之后区间为1的记录集合,统计这些记录的总金额,过滤出总金额大于5000的订单。

Picture37png

SPL代码:


A

1

$select o.OrderId,o.Amount,o.OrderDate,e.Dept from Orders.txt o,Employee.txt e where o.SellerId=e.EId order by e.Dept,o.OrderDate

2

=A1.group(Dept)

3

=A2.(~.select(Amount[-1:1].sum()>5000))

4

=A3.conj()

A1:加载数据,关联订单表和部门表,按部门和日期排序。

A2:分组但不汇总,每组是一个集合。如图是第1组。

Picture38png
A3:处理每组数据,过滤当前组的记录,找出前后区间是 1 的记录集合里,总金额大于 5000 的记录集合对应的订单。Amount[-1:1] 是相对区间语法,表示相对于当前记录的前一条和后一条的记录范围内的 Amount 字段值组成的集合。

Picture39png

l 知识点:相对区间

SPL可以表示相对区间的成员组成的集合,形如~[a:b]F[a:b]

Picture40png
: 循环函数中的当前成员或当前记录。
F: 循环函数中当前记录的字段。
a,b: 区间的起止位置,a 是整数,负数时表示倒数位置,b 和 a 的规则一样,a<=b。a 可以省略,表示起始位置是第一条;b 可以省略,表示终止位置是最后一条。注意
[0:0] 和 ~[0] 都只有一条记录,但数据类型不同,前者是记录集合,后者是记录。

A4:合并各组成员。

Picture41png

扩展阅读

(https://c.raqsoft.com.cn/article/1740386718048)
(https://c.raqsoft.com.cn/article/1736157120197)
(https://c.raqsoft.com.cn/article/1744275268499)
(https://c.raqsoft.com.cn/article/1745800641250)
(https://c.raqsoft.com.cn/article/1741096770958)
(https://c.raqsoft.com.cn/article/1735093433084)
(https://c.raqsoft.com.cn/article/1736566315971)