SQL数据筛选：排除当日同维度后续有成功记录的初始失败行

数据集与规则 #

现有报表记录表结构与示例数据如下：

| ReportId  | Method | Status | OrganizationId | StartedAt                     |
|-----------|--------|--------|----------------|-------------------------------|
| 38373bfk8 | Email  | 0      | ABC            | 2022-06-10 00:00:53.794 +0000 |
| 78687fea  | Email  | 0      | XYZ            | 2022-06-10 00:03:51.432 +0000 |
| 48978kd   | Email  | 100    | POD            | 2022-06-10 00:02:45.532 +0000 |
| 38373bfk8 | Email  | 100    | ABC            | 2022-06-10 00:00:22.654 +0000 |
| 86887dhd  | Csv    | 100    | FGH            | 2022-06-10 00:03:12.541 +0000 |
| 78687fea  | Email  | 100    | XYZ            | 2022-06-11 00:04:51.352 +0000 |

字段规则：

• Status=0代表报表生成失败，Status=100代表报表生成成功
• 筛选逻辑要求：
  • 所有成功记录全部保留
  • 失败记录仅当同ReportId/Method/OrganizationId组合、同一自然日内、晚于该记录的时间点不存在成功记录时保留
  • 若失败记录满足「同组合、当日、后续时间点存在成功记录」，则剔除该条失败记录

示例数据预期输出：仅剔除第1条记录（同组合当日后续存在成功记录），第2条失败记录对应的成功记录在次日，不符合剔除条件，需保留。

原有逻辑问题 #

之前编写的CTE直接按组内排序过滤掉了当日同组合的第一条失败记录，没有判断该记录当日后续是否真的存在成功记录，会误删「当日同组合一直没有生成成功的首条失败记录」，不符合需求。

核心是通过窗口函数直接判断每条失败记录的同组当日后续是否存在成功记录，再做过滤：

with Ranked as (
    select
        ReportId,
        Method,
        Status,
        OrganizationId,
        StartedAt,
        -- 标记同组合当日、当前记录之后是否存在成功记录
        max(Status) over (
            partition by ReportId, Method, OrganizationId, cast(StartedAt as date)
            order by StartedAt asc
            rows between 1 following and unbounded following
        ) as later_success_flag
    from
        MyTable
)
select 
    ReportId,
    Method,
    Status,
    OrganizationId,
    StartedAt
from Ranked
where
    -- 保留所有成功记录
    Status = 100
    -- 失败记录仅当后续无成功记录时保留
    or (Status = 0 and (later_success_flag is null or later_success_flag != 100))

逻辑说明 #

• 窗口范围rows between 1 following and unbounded following限定为：同ReportId+Method+OrganizationId+同一日期的分组内，启动时间晚于当前记录的所有行
• 取该范围内的最大Status值，若等于100则说明当前记录之后存在成功记录，该条失败记录需要剔除
• 若范围内无记录（当前是同组当日最后一条）、或范围内最大Status不等于100（后续全为失败记录），则该条失败记录保留
• 该写法完全匹配需求，不会误删无后续成功的失败记录，也能正确过滤掉有后续成功的失败记录

内容的提问来源于stack exchange，提问作者stackq