Snowflake SQL 如何删除时间序列中长于1天间隔之前的记录

核心逻辑 #

• 按GROUP字段分组统计每组的最新事件时间
• 计算每组最新时间往前推1天的临界时间点
• 删除分组内所有早于该临界时间点的记录，完全匹配你给出的示例需求

实现代码 #

直接删除不符合要求的记录 #

DELETE FROM your_table_name t
USING (
    SELECT 
        "GROUP",
        MAX(CASE_TIMESTAMP) AS max_ts
    FROM your_table_name
    GROUP BY "GROUP"
) g
WHERE t."GROUP" = g."GROUP"
AND t.CASE_TIMESTAMP < DATEADD(day, -1, g.max_ts);

先查询验证待保留的记录（建议删除前先执行确认） #

WITH group_max_time AS (
    SELECT 
        "GROUP",
        MAX(CASE_TIMESTAMP) AS max_ts
    FROM your_table_name
    GROUP BY "GROUP"
)
SELECT t.*
FROM your_table_name t
JOIN group_max_time g 
    ON t."GROUP" = g."GROUP"
WHERE t.CASE_TIMESTAMP >= DATEADD(day, -1, g.max_ts);

注意事项 #

• GROUP是SQL保留关键字，在Snowflake中引用该字段时需要用双引号包裹，避免触发语法错误
• 如果你需要的是按相邻记录间隔截断的逻辑（即从分组最新记录往回遍历，遇到第一条和后一条记录间隔超过1天的位置就截断，删除该位置之前的所有记录），可以使用窗口函数版本实现：

WITH ordered_records AS (
    SELECT 
        *,
        -- 按时间倒序排列，取当前记录的下一条（时间更晚的）记录时间
        LAG(CASE_TIMESTAMP) OVER (PARTITION BY "GROUP" ORDER BY CASE_TIMESTAMP DESC) AS next_ts
    FROM your_table_name
),
calc_cutoff AS (
    SELECT 
        *,
        -- 标记相邻间隔超过1天的记录位置
        CASE WHEN DATEDIFF(day, CASE_TIMESTAMP, next_ts) > 1 THEN 1 ELSE 0 END AS is_cutoff
    FROM ordered_records
),
group_cutoff AS (
    SELECT 
        "GROUP",
        -- 取首个截断点的时间作为临界值
        COALESCE(MIN(CASE WHEN is_cutoff = 1 THEN next_ts END), MIN(CASE_TIMESTAMP)) AS cutoff_ts
    FROM calc_cutoff
    GROUP BY "GROUP"
)
-- 保留临界值及之后的记录
SELECT t.*
FROM your_table_name t
JOIN group_cutoff g ON t."GROUP" = g."GROUP"
WHERE t.CASE_TIMESTAMP >= g.cutoff_ts;

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