乾学院 53 号会员
2 回帖  •  173 浏览  • 
     ...

从 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 解法：


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分组后可以保持分组子集，便于处理组内数据: https://try.esproc.com/splx?4hD


 　A
1 $select ID,CreatedAt,NewStatus from mytable.txt 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