SPL 查询与报表计算实战指南 -12 按规则扩展出多条记录
12 按规则扩展出多条记录
这类任务是指按分隔符拆分字符串、按区间展开日期、按固定数量等规则，将一条记录扩展成多条记录。SQL要用JOIN语句+嵌套结构或递归结构来实现，代码复杂易错。
例1：拆分邮件列表并统计每个邮件地址被多少个销售员收集过
数据源：客户邮件表（12/ClientMailFromSeller.txt）存储了销售员从客户处收集到的多个邮件地址，邮件地址之间以竖线分隔，形成邮件列表。同一个邮件可能被多个销售员收集过，这通常表示该邮件更常用。
目标：统计每个客户的每个邮件地址被多少个销售员收集过。
SPL代码：
A |
|
1 |
$select * from 12/ClientMailFromSeller.txt |
2 |
=A1.news(MailList.split("|");Client,~:Mail) |
3 |
=A2.groups(Client,Mail;count(1):Count) |
A1：加载数据。
A2：循环扩展A1的每条记录，再合并扩展结果。先将邮件列表按竖线拆分成集合，每个邮件地址是1个成员，再按集合成员将当前记录扩展成2字段的新序表，其中Client来自原记录，Mail来自于集合的当前成员~。函数news可将一条记录扩展成多条记录。
l 知识点：扩展记录
SPL有news函数可以将一条记录扩展成多条，最后合并成新的序表。扩展的规则：可以将原记录按任意规则计算成序列，每个成员对应一条新记录；也可以按外部序列扩展，每个成员对应一条新纪录；当外部序列是从1到N的自然数时，可简写做N。函数news可以用conj和new组合出来，前者简化了扩展前后引用字段和序列的语法。
参数X：用于扩展的序列。原序表的每条记录将按X扩展，生成X.len()条新记录。X可以是原序表的字段或表达式，比如MailList.split("|")或Client|SellerId；可以直接是外部序列["JFS","UFS"]，可以是自然数，比如3（相当于[1,2,3]）。
参数xi：扩展后的字段，可以是多个。xi可以是~，表示引用X的当前成员；可以是字段或表达式，表示引用序表~或原序表里的字段值，比如Client，优先引用~里的字段，字段名重复时可以用原序表名字做前缀来区分，比如A1.Client。
A3：按客户和邮件地址分组，统计每组的记录数。
例2：根据起止区间统计每个客户每个月接受了多少天的售后服务
数据源：客户的售后服务表（11/after_sales_service.txt），每个客户可能同时接受多次售后服务，售后服务之间的起止日期（区间）可能有重叠。
目标：统计每个客户每个月接受了多少天的售后服务。
SPL代码：
A |
|
1 |
$select * from 11/after_sales_service.txt |
2 |
=A1.news(periods(StartDate,EndDate);Client,~:Date) |
3 |
=A2.groups(Client, year(Date):Year,month(Date):Month; count(1):days) |
A1：加载数据。
A2：循环扩展A1的每条记录，再合并扩展结果。先按当前记录的起止日期拆分成日期序列，再按日期序列扩展成2字段的新序表，Client字段来自原记录，Date字段来自日期序列的当前成员~。
A3：按客户、年、月分组，统计各组的天数。
例3：根据当前和历史列出员工每天的岗位状态
数据源：员工当前岗位表（Employee_Current.txt），存储员工当前的部门、薪水，以及状态变化的原因，其中HireDate是雇佣时间，不随状态变化而变，比较特殊。员工历史岗位表（12/Employee_History.txt），存储员工岗位状态的历次变化情况，包括发生变化的日期ChangeDate。
目标：给定查询日期（比如2024-03-01），列出今天（2024-03-14）到查询日期这段日期区间内，每个员工每天的岗位状态。要合理地补充上空白日期的状态，比如，今天到最近的历史变更日，要补成当前的岗位状态；最近的变更日到次近的变更日，要补成最近的历史变更日的状态。特殊地，要补上雇佣时间。
SPL代码：
A |
|
1 |
$select date('2024-03-14') as Date,EId,Dept,Salary,Status,HireDate from 12/Employee_Current.txt |
2 |
$select ChangeDate as Date,EId,Dept,Salary,Status,null as HireDate from 12/Employee_History.txt order by Date desc |
3 |
=(A1|A2).group(EId) |
4 |
=A3.(~.news(periods@x(~.Date,ifn(~[1].Date,date("2024-02-29")),-1);date(~):Date,EId,Dept,Salary,Status,A3.HireDate)) |
5 |
=A4.conj() |
A1：加载当前状态，新增计算列Date，值是今天。
A2：加载历史状态，将字段名 ChangeDate 改为 Date，逆序排序，新增计算列 HireDate，值为 null。加载数据后，当前状态和历史状态的表结构一致。
A3：合并当前和历史，再按员工分组，但不汇总，每组是一个集合，成员将按逆序排序。第 1 组如图。
A4=A3.(~.(periods@x(~.Date,ifn(~[1].Date,date("2024-02-29")),-1))) 循环处理A3的每个员工，对当前员工，循环处理每条记录，先用当前状态的日期和上次状态的日期算出区间内的日期序列，序列中不含上次状态的日期。注意，记录按逆序排列，所以上次改变状态时就是下1条记录；最后1条记录应当和指定的查询日期计算序列。函数peorids用起止区间计算日期序列，间隔单位可以为负，@x表示不含末端点。函数ifn返回第一个不为空的成员。第1个员工的第1和最后1个日期序列如图：
A4=A3.(~.news(…;~:Date,EId,Dept,Salary,Status,A3.HireDate)) 继续处理当前员工，用news函数将每个日期序列扩展成多条记录，再合并扩展的结果。Date字段来自序列的当前成员~，HireDate来自当前员工/组，其他字段来自当前员工的当前记录（扩展前的记录）。
A5：最后合并各组成员。
扩展阅读
(https://c.raqsoft.com.cn/article/1620815256603)
(https://c.raqsoft.com.cn/article/1740647973909)
(https://c.raqsoft.com.cn/article/1737075798976)
(https://c.raqsoft.com.cn/article/1741059619930)
