Snowflake中按分区过滤：剔除每个id下早于created状态start_time的记录

实现逻辑 #

• 第一步：按ID分组，计算每个ID下最早的created状态对应的start_time
• 第二步：同时计算每个ID下所有记录的全局最早start_time
• 第三步：如果两个时间相等，说明该ID的第一条记录就是created状态，符合保留要求；否则过滤掉该ID的全部记录
  该逻辑仅需要1次全表扫描+分组计算，时间复杂度O(n)，数千行数据可以秒出结果，扩展性极强。

可直接运行的SQL代码 #

WITH t1 AS (
SELECT 'A' AS id, 'failed' AS status, '2021-05-18 18:30:00'::timestamp AS start_time UNION ALL
SELECT 'A' AS id, 'created' AS status, '2021-05-24 11:30:00'::timestamp AS start_time UNION ALL
SELECT 'A' AS id, 'created' AS status, '2021-05-24 12:00:00'::timestamp AS start_time UNION ALL
SELECT 'B' AS id, 'created' AS status, '2021-05-19 18:30:00'::timestamp AS start_time UNION ALL
SELECT 'B' AS id, 'successful' AS status, '2021-05-20 11:30:00'::timestamp AS start_time
),
-- 计算每个ID的校验标记
id_valid_flag AS (
    SELECT 
        id,
        -- 最早的created状态时间
        MIN(CASE WHEN status = 'created' THEN start_time END) AS min_created_time,
        -- 该ID所有记录的最早时间
        MIN(start_time) AS min_overall_time
    FROM t1
    GROUP BY id
)
-- 仅保留校验通过的ID的全部记录
SELECT t1.* 
FROM t1
INNER JOIN id_valid_flag f 
    ON t1.id = f.id
WHERE f.min_created_time = f.min_overall_time
-- 可选：如果需要过滤掉没有created状态的ID，保留下面的条件
AND f.min_created_time IS NOT NULL

运行后输出仅包含ID为B的全部两条记录，完全符合预期要求。

扩展说明 #

• 数据量上升到百万级别时，仅需要给id、status、start_time加联合索引，性能不会有明显衰减
• 规则调整非常方便，如需新增状态校验、时间区间判断，仅修改id_valid_flag里的聚合逻辑即可，不需要改动整体结构
• 兼容所有主流关系型数据库（PostgreSQL/MySQL/Oracle等，仅需要根据数据库语法调整timestamp类型声明即可）

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