问题描述

数据库表 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。

官方链接

SQLazy 在线体验：sqlazy.com（免费，无需注册）

SQLazy 项目仓库：github.com/SPLWare/SQLazy