SPL 查询与报表计算实战指南 - 5 对齐外部集合的成员并进行分组
5 对齐外部集合的成员并进行分组
这类任务是指分组依据是外部的另一个集合的成员，而不是记录集合自己的字段值及计算列组成的集合。SQL不能直接按照外部集合进行分组，要通过关联外部表或union多个子查询来间接实现，灵活性差，代码难写。记录集合和外部集合的成员不匹配，需要处理剩下的数据时，SQL就更难写了。
例1：按连续月份统计不连续的订单
数据源：不保证每个月份都有记录的订单表（5\OrdersUncontinuous.txt）
目标：将数据按照连续的月份分组，统计每个月的订单数量、第一笔订单的金额、最后一笔订单的金额、最小订单的金额、最大订单的金额。当某个月没有订单时，仍然要正常统计该月的数据。
SPL代码：
A |
|
1 |
$select * from 5\OrdersUncontinuous.txt order by OrderDate |
2 |
=A1.run(OrderDate=pdate@m(OrderDate)) |
3 |
=list=periods@m(A1.min(OrderDate),A1.max(OrderDate)) |
4 |
=A1.align@a(list,OrderDate) |
5 |
=A4.new(list(#):Month,~.count():cnt, ~.m(1).Amount:first, ~.m(-1).Amount:last, ~.min(Amount):min, ~.max(Amount):max) |
A1：加载数据，按订单日期排序。
A2：将 订单日期修改为当月的第1天。
A3：生成连续的月份序列 list，每个成员是一个月。函数 periods 可以用起止日期生成连续日期，默认间隔单位是天，@m 表示间隔单位是月；默认间隔为 1。
A4：将订单按 list 对齐，每组是一个月的数据，有些月份为空。函数 align@a 可将订单按 list 对齐并分组。
l 知识点：对齐分组
SPL有align函数可以使记录集合对齐外部集合并分组，如果记录集合有多出的记录，则没有对应的分组；如果外部集合有多出的成员，则对应的分组为空。默认返回各组的第一个成员，@a表示返回所有成员。
参数 A：外部集合，可以是外部记录集合的字段或表达式，此时可以写作”集合名: 字段名或表达式”的形式。
参数 y：待分组的记录集合的字段或表达式。
A5：生成二维表， A4 的每组记录对应一条新记录。Month 取自 list 里对应序号的成员，# 是记录序号；其他字段分别是每个月的订单数量、第一笔订单的金额、最后一笔订单的金额、最小订单的金额、最大订单的金额。函数 m 可以按序号取成员，序号为负数表示倒数，为空时取得的成员也是空。
例2：按重点州和其他州统计各部门的员工
数据源：员工表。
目标：用交叉表呈现各部门各州的员工情况。左表头是部门，上表头是3个重点州和其他州，在3列重点州里统计员工数，在最后一列里用逗号把所有其他州的员工名拼起来。
SPL代码：
A |
|
1 |
$select Dept,State,Name from Employee.txt |
2 |
=A1.group(Dept) |
3 |
=A2.(~.align@n(["Florida","California","Texas"],State)) |
4 |
=A3.new(~.ifn(Dept):Department,~(1).count():Florida,~(2).count():California,~(3).count():Texas, ~(4).(Name).concat@c():Others) |
A1：加载数据。
A2：按部门分组，每组是一个集合。第4组如图。
A3：处理每组数据，将当前组对齐重点州，对不上的记录分到最后一组。第 4 组如图。
函数align@n表示对齐分组时，将对不上的记录分到最后一组。
l 知识点：对齐分组的@n选项
用align函数进行对齐分组时，可以使用 @n选项将记录集合中对不上外部集合的记录，也就是多出的记录，分到最后一组。
A4：生成新二维表，每组数据对应一条。Department 列取自当前组的部门集合中首个不为空的成员。Florida 列取自当前组的第 1 个小组的成员数量，其他重点州类似处理。Others 取自当前组的第 4 个小组的员工名，员工名用逗号拼起来。函数 ifn 返回集合中首个不为空的成员；函数 concat 可以将集合成员按指定分隔符拼起来，@c 表示分隔符为逗号。
扩展阅读
(https://c.raqsoft.com.cn/article/1744850845456)
(https://c.raqsoft.com.cn/article/1549182237531)
(https://c.raqsoft.com.cn/article/1547520280708)
(https://c.raqsoft.com.cn/article/1571412914244)
(https://c.raqsoft.com.cn/article/1572224318205)
