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。