SPL 查询与报表计算实战指南 - 4 与成员次序有关的分组
4 与成员次序有关的分组
与简单的等值分组不同，有序分组是指保持数据有序的情况下，比较相邻的数据以分组，或满足条件以分组的任务。SQL不支持有序分组，通常要用打标记的办法间接实现，代码很复杂，涉及保持子集的有序分组时，代码更加复杂。
例1：找出连续多月蝉联榜首的销售员的起止月份。
数据源：每年每月每个销售员的销售额。
目标：先找出每个月订单金额最多的销售员，按月份排序后，如果相邻的销售员是同一个人，则分到同一组，取出组内第一条和最后一条的月份。
SPL代码：
A |
|
1 |
$select year(OrderDate)*100+month(OrderDate) YM,SellerId,sum(Amount) SubTotal from 4\Orders41.txt group by year(OrderDate)*100+month(OrderDate),SellerId |
2 |
=A1.group(YM).(~.maxp(SubTotal)) |
3 |
=A2.group@o(SellerId) |
4 |
=A3.new(SellerId,~.count():cnt,~.YM:begin,~.m(-1).YM:end) |
5 |
=A4.select(cnt>1) |
A1：加载数据，用SQL计算每年每月每个销售员的订单金额。
A2：按月份分组，但不汇总，取出每个分组子集里销售金额最大的记录。 函数 maxp 用于查找使表达式的值达到最大的记录。
A3：将销售员相同的记录分到同一组，但不汇总，每组是一个集合。函数 group@o(SellerId) 表示将销售员相同的相邻订单分到同一组。
l 知识：相邻分组
SPL的groups函数和group有@o选项，可将相同的相邻记录分到同一组。
参数 x: 分组表达式，可以是多个。
参数 y: 聚合表达式，可以是多个。
A4：根据 A3 新建序表，每组生成一条记录，字段分别是：销售员、组内计数、组内第一条和最后一条记录的月份。是当前组；.m(1) 是组内第 1 条记录，简写做1 和；~.m(-1) 是组内最后一条记录，不能简写。
A5：过滤出记录数大于 2 的分组。
例2：找出销售额连续上涨最多的月份数
数据源：每年每月的订单销售额。
目标：将连续上涨的月份分到同一组，找出最大的组内记录数。反过来也可以说是：当销售额比上个月下降时开始新的分组。
SPL代码：
A |
|
1 |
$select year(OrderDate) year, month(OrderDate) month,sum(Amount) subTotal from Orders.txt group by year(OrderDate) , month(OrderDate) |
2 |
=A1.group@i(subTotal<=subTotal[-1]) |
3 |
=A2.max(~.len()) |
A1：加载数据。
A2：当本月销售额小于上月销售额时开始新的分组，相当于将连续上涨的月份分到同一个组，每组是一个集合。函数 group@i 用于条件分组。
l 知识点：条件分组
SPL的groups和group函数有@i选项，可以进行有序数据的条件分组。
参数x:分组条件表达式，可以是多个。表示当条件满足时开始新的分组。
参数y:聚合表达式，可以是多个。
另外，@o本质是条件简单的@i，即当前成员不等于上一个成员时开始新的分组。
A3：对组内成员计数，返回最大的计数4。
例3：计算累积的奖金池，但每到10000元重新累积
数据源：订单表2022年的数据
目标：按日期顺序累积订单的金额，每累积到最多10000元时，设立一个奖金池，金额为累积值的10%（奖金将分给这些订单对应的销售员）。现在要按顺序给奖金池编号，算出每个奖金池的金额和订单编号列表。注意，当累积值超出10000元时，该订单应当作为下一次奖金池的第一个订单。
SPL代码：
A |
|
1 |
$select OrderID,Amount,OrderDate from Orders.txt where year(OrderDate)=2022 order by OrderDate |
2 |
>cum=0 |
3 |
=A1.group@i(if( (cum+=Amount)>10000, cum=Amount, null)) |
4 |
=A3.new(#:gid,~.sum(Amount)*0.1:subTotal,~.(OrderID).concat@c():orderList) |
A1：加载数据。
A2：给累积变量或奖金池设初值。
A3：按奖金池分组。当累积值大于 10000 时，开始新的分组，并将累积值重置为当前订单的金额。如图是前2组。
A4：根据 A3 新建二维表，每组对应一个奖金池，字段分别是组号 #、组内订单总额的 10%、订单编号列表。函数 concat 可以按指定分隔符合并序列的成员，@c 表示分隔符是逗号。
扩展阅读
(https://c.raqsoft.com.cn/article/1747042169392)
(https://c.raqsoft.com.cn/article/1737690778254)
(https://c.raqsoft.com.cn/article/1724047689499)
(https://c.raqsoft.com.cn/article/1744883172421)
(https://c.raqsoft.com.cn/article/1745224008015)
(https://c.raqsoft.com.cn/article/1747194851995)
