从 SQL 到 SPL:组内查找最近的匹配记录
MSSQL 数据库的表 mytable,每个 ID 有一个 ConfirmationStarted 和多个 Closed 状态。
CreatedAt |
ID |
NewStatus |
2022-05-25 23:17:44.000 |
147 |
Active |
2022-05-28 05:59:02.000 |
147 |
Closed |
2022-05-30 20:48:53.000 |
147 |
Active |
2022-06-18 05:59:01.000 |
147 |
Closed |
2022-06-21 20:09:48.000 |
147 |
Active |
2022-06-25 05:59:01.000 |
147 |
Closed |
2022-07-13 00:02:47.000 |
147 |
ConfirmationStarted |
2022-07-15 15:33:30.000 |
147 |
ConfirmationDone |
2022-08-25 05:59:01.000 |
147 |
Closed |
2023-03-08 13:34:57.000 |
1645 |
Draft |
2023-03-22 19:58:51.000 |
1645 |
Active |
2023-04-29 05:59:02.000 |
1645 |
Closed |
2023-05-08 14:50:29.000 |
1645 |
Awarded |
2023-05-08 14:53:34.000 |
1645 |
ConfirmationStarted |
2023-05-08 17:53:55.000 |
1645 |
ConfirmationDone |
现在要在每个 ID 里,找到 ConfirmationStarted 之前的所有的 Closed 中,离 ConfirmationStarted 最近的那条记录,取出记录的 ID 和时间字段。
ID |
xdate |
147 |
2022-06-25 05:59:01.000 |
1645 |
2023-04-29 05:59:02.000 |
SQL 解法:
With cte AS (
SELECT ID, CreatedAt, NewStatus,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CreatedAt DESC) AS rn
FROM mytable
WHERE NewStatus = 'Closed'
AND CreatedAt < (
SELECT CreatedAt FROM mytable AS sub
WHERE sub.ID = mytable.ID AND sub.NewStatus = 'ConfirmationStarted'
)
)
SELECT ID, CreatedAt as xdate
FROM cte
WHERE rn = 1
ORDER BY ID;
SQL没有天然序号,需要先用窗口函数生成序号。SQL分组后必须立刻汇总,不能对组内记录进行过滤,只能绕道用多层子查询反复过滤。整体代码有点繁琐又难懂。
SPL有天然序号,还提供有丰富的与位置相关的计算。SPL分组后可以保持分组子集,便于处理组内数据。
A |
|
1 |
=mssql.query("select ID,CreatedAt,NewStatus from mytable order by CreatedAt”) |
2 |
=A1.group(ID) |
3 |
=A2.(~.select@c(NewStatus!="ConfirmationStarted").select@z1(NewStatus=="Closed")) |
4 |
=A3.new(ID,CreatedAt:xdate) |
A1:从数据库加载数据,按时间排序。
A2:按 ID 分组,但不汇总。
A3:过滤每组数据,先找到 ConfirmationStarted 之前的记录,再从中过滤出 Closed,取倒数第 1 条。函数 select 用于条件过滤,过滤时支持与位置相关的计算,@c 表示从第一个使条件为真的记录开始取,直到遇到使条件为假的记录时停止,@1 表示取结果的第 1 条,@z 表示从后往前过滤。
A2-A4 可以合成一句:=A1.group(ID;~.select@c(NewStatus!="ConfirmationStarted").select@z1(NewStatus=="Closed").CreatedAt:xdate)
问题来源:https://stackoverflow.com/questions/78279330/how-do-i-find-the-date-before-a-value-changes-in-t-sql
跟着练一下,利用 iterate 第 3 参数满足条件时跳出的特性可以少遍历几次。
顺便问一下,spl 中的 iterate 可不可以实现从右往左迭代? 比如像其它一些编程语言中有 reduce 和 reduceRight。
英文版 https://c.scudata.com/article/1736296456809