PostgreSQL：如何用LAG()追踪距上次KEEP行时长并标记行状态

原始数据 #

需求 #

需要新增output列，值为KEEP或SKIP，逻辑规则：

• 第一行默认标记为KEEP
• 后续每行与最近的已标记为KEEP的行比较，若时间间隔超过5分钟则标记为KEEP，否则标记为SKIP

期望输出：

现有问题 #

尝试的SQL仅能和上一行比较，无法关联到最近的KEEP行：

SELECT *, 
     CASE 
        WHEN (date - LAG(date) OVER (ORDER BY date ASC)) > INTERVAL '5 minutes' THEN 'KEEP',
        WHEN LAG(date) OVER (ORDER BY date) IS NULL THEN 'PUSH'
     ELSE 'SKIP'
     END AS output
from table;

比如第5行（00:05:59）和第一行间隔5分14秒，符合KEEP条件，但现有SQL和第四行比较间隔不足5分钟，导致错误标记。

解决方案 #

这个需求属于递归标记场景，需要用递归CTE来追踪最近的KEEP行时间：

WITH RECURSIVE ranked_data AS (
    -- 先给数据按时间排序，生成行号
    SELECT 
        date, 
        value, 
        ROW_NUMBER() OVER (ORDER BY date) AS rn
    FROM your_table_name
),
keep_track AS (
    -- 初始化：第一行标记为KEEP，记录其时间
    SELECT 
        date, 
        value, 
        rn,
        'KEEP' AS output,
        date AS last_keep_date
    FROM ranked_data
    WHERE rn = 1
    UNION ALL
    -- 递归处理后续行：和最近的KEEP行时间比较
    SELECT 
        rd.date, 
        rd.value, 
        rd.rn,
        CASE 
            WHEN rd.date - kt.last_keep_date > INTERVAL '5 minutes' THEN 'KEEP'
            ELSE 'SKIP'
        END AS output,
        -- 如果当前行是KEEP，更新last_keep_date为当前时间，否则沿用之前的
        CASE 
            WHEN rd.date - kt.last_keep_date > INTERVAL '5 minutes' THEN rd.date
            ELSE kt.last_keep_date
        END AS last_keep_date
    FROM ranked_data rd
    JOIN keep_track kt ON rd.rn = kt.rn + 1
)
SELECT date, value, output
FROM keep_track
ORDER BY date;

逻辑说明 #

1. ranked_data：先给原始数据按时间排序并生成行号，方便递归逐行处理。
2. keep_track递归CTE：
   • 锚点部分：取第一行，标记为KEEP，并记录该行的时间作为last_keep_date。
   • 递归部分：每次处理下一行，和上一步的last_keep_date比较：
     • 若间隔超5分钟，标记为KEEP，并更新last_keep_date为当前行时间。
     • 否则标记为SKIP，沿用之前的last_keep_date。
3. 最后从递归结果中取出需要的列，按时间排序输出。

适配不同数据库 #

如果使用MySQL，递归CTE语法一致，但时间间隔计算可能需要调整为TIMESTAMPDIFF(MINUTE, kt.last_keep_date, rd.date) > 5；如果是PostgreSQL，当前语法直接可用。

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