SPL 查询与报表计算实战指南 - 3 在有序情况下引用相邻的成员
3 在有序情况下引用相邻的成员
这类任务是指在遍历或循环中,访问相对于当前成员的其他位置或区间的成员,属于较复杂的有序计算。SQL 要通过窗口函数(lag/lead)获取相对位置的成员,通常会配合子查询,代码繁琐,如果涉及分组子集中的相对位置时,代码会更难写。例1:计算月增长额的最大值
源数据:按月份排序的一年12个月的订单金额。
目标:计算出每个月相对于上个月的增长额,求其中最大的增长额。
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:加载数据。
A2=A1.(if(#>1,subTotal - subTotal[-1],0))… 循环 A1 中的每条记录,如果不是第 1 行,当月增长额 = 当前记录的金额 - 上一条记录的金额,如果是第 1 行,当月增长额为 0。~[-1].subTotal 是相对位置语法,表示上一条记录的金额,简写做 subTotal[-1]。
l 知识点:相对位置
SPL可以用中括号表示相对位置,形如~[n]或F[n]
~: 循环函数中的当前成员或当前记录。
F: 循环函数中当前记录的字段。
n: 相对于当前成员的序号,n>0 时,表示后面的第 n 个成员;n<0 时,表示前面的第 n 个成员;n=0 时表示当前成员,可以省略不写。
…max() 求最大值,结果是 6094.9
例2:统计各部门大订单的累积值
源数据:订单表、部门表。
目标:规定金额大于2000的订单为大订单,否则为小定单。大订单的累积值是指按部门分组,组内按日期排序后,从第一个订单或前一个大订单后的小订单开始,一直到当前大订单的累积值。现在要找出每个大订单及其累积值。
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,初值为空。
A2:用 run 函数循环修改 A1 的每条记录,返回修改结果。当 部门不变且上个订单是小订单时,用 SubTotal 累积订单金额,也就是用上一个 SubTotal+ 当前的订单金额;否则(部门变化时或上个订单是大订单时)将 SubTotal 重置为当前的订单金额。[-1] 表示上一条记录。
A3:选出回款月份所在的记录。
例3:获取各部门的订单中前后区间金额之和大于5000的订单
数据源:订单表、部门表。
目标:按部门分组,组内按日期字段排序后,找到每个订单之前和之后区间为1的记录集合,统计这些记录的总金额,过滤出总金额大于5000的订单。
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组。
A3:处理每组数据,过滤当前组的记录,找出前后区间是 1 的记录集合里,总金额大于 5000 的记录集合对应的订单。Amount[-1:1] 是相对区间语法,表示相对于当前记录的前一条和后一条的记录范围内的 Amount 字段值组成的集合。
l 知识点:相对区间
SPL可以表示相对区间的成员组成的集合,形如~[a:b]或F[a:b]
: 循环函数中的当前成员或当前记录。
F: 循环函数中当前记录的字段。
a,b: 区间的起止位置,a 是整数,负数时表示倒数位置,b 和 a 的规则一样,a<=b。a 可以省略,表示起始位置是第一条;b 可以省略,表示终止位置是最后一条。注意[0:0] 和 ~[0] 都只有一条记录,但数据类型不同,前者是记录集合,后者是记录。
A4:合并各组成员。
扩展阅读
(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)