AI取 ConfirmationStarted 之前最近的一条 Closed
## 问题描述 数据库表 mytable 存储多个 ID 在不同时间点 CreatedAt 的状态 NewStatus,每个 ID 一定有一个 ConfirmationStarted 和一个或多个 Closed 状态。现在要在每个 ID 内,找到 ConfirmationStarted 之前的所有的 Closed 中,离 ConfirmationStarted 最近的那条记录,取出记录的 ID 和时间字段。 ### **源数据**  ### **期望结果**  以 ID=147 为例: ConfirmationStarted 出现在 2022-07-13,此前的三条 Closed 分别发生在 05-28、06-18、06-25,离它最近的一条是 2022-06-25 05:59:01,正是期望结果中的时间。 ID=1645 的 ConfirmationStarted 出现在 2023-05-08 14:53:34,此前只有一条 Closed(2023-04-29 05:59:02),所以结果取它。 ## SQLazy 分步实现 **核心思路:**把每个 ID 的记录按时间排序后,用 segment 在出现 ConfirmationStarted 的地方切段,第一个 ConfirmationStarted 之前的记录自然落在 seg=1;再过滤出 seg=1 且状态为 Closed 的记录,最后按 ID 汇总取 CreatedAt 的最大值,即离 ConfirmationStarted 最近的一条 Closed。 下面逐一解释这些步骤。  **第 1 步:按 ID 和时间升序排序** *sort ID, CreatedAt asc* 保证每个 ID 内部的记录按时间先后排列,后续分段和取 "最近" 才有依据。  **第 2 步:遇到 ConfirmationStarted 就新开一段** *segment condition (NewStatus = "ConfirmationStarted") partition ID as seg* 这是核心的一步。segment 按 partition ID 在每个 ID 内独立分段;分段条件指明:每当遇到 NewStatus 为 ConfirmationStarted 的记录,就开启新的一段并编号为 seg。这样,第一个 ConfirmationStarted 之前的所有记录都落在 seg=1,ConfirmationStarted 本身及之后的记录 seg 依次递增。一条语句就把 "目标状态之前" 的区间划了出来。  **第 3 步:过滤出目标记录** *filter (NewStatus = "Closed" and seg = 1)* 只保留 seg=1(第一个 ConfirmationStarted 之前)且状态为 Closed 的记录,这些就是每个 ID 在 ConfirmationStarted 之前的全部 Closed。  **第 4 步:按 ID 汇总取最近的 Closed 时间** *summarize CreatedAt max as CreatedAt; group ID* 在每个 ID 内对 CreatedAt 取最大值。因为前面已经按时间升序排列,最大值就是离 ConfirmationStarted 最近的那条 Closed。summarize 直接用 "按 ID 分组、取 CreatedAt 最大" 的业务语义描述聚合,不需要手工编写窗口函数。  ### **编译生成 SQL** 确认上述 4 步逻辑后,SQLazy 编译器自动生成原生 SQL(这里是 Oracle 语法): ``` WITH t2 AS ( SELECT CreatedAt, ID, NewStatus , 1 + SUM(CASE WHEN (NewStatus = 'ConfirmationStarted') THEN 1 ELSE 0 END) OVER (PARTITION BY ID ORDER BY ID ASC, CreatedAt ASC ROWS UNBOUNDED PRECEDING) AS seg FROM mytable ) SELECT ID, MAX(CreatedAt) AS CreatedAt FROM ( SELECT CreatedAt, ID, NewStatus, seg FROM t2 WHERE (NewStatus = 'Closed' AND seg = 1) ) t_3 GROUP BY ID ORDER BY ID ``` SQLazy 让你用业务语言描述逻辑,而不是用 SQL 语法写嵌套查询。这类 "按事件切段、再从指定区段取记录" 的问题,核心是给事件流打上区段标记:segment 条件分段功能直接用 "遇到 ConfirmationStarted 就切段" 描述业务语义,partition 让分段在每个 ID 内独立进行。先分段、再过滤、后汇总的分步计算,让每一步的中间结果都可以独立验证;summarize 用 "按 ID 分组、取最大时间" 这样直白的语句完成聚合,编译器自动生成可运行的 SQL。