乾学院 53 号会员
22 浏览  • 
     ...

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)