如何按相似时间戳（非固定间隔）提取数据库唯一条目？

要实现相对于每行的动态5分钟间隔去重（仅保留连续±5分钟范围内的第一条记录），核心思路是基于已保留的记录动态判断后续条目是否超出间隔，而非使用固定时间区间。以下是两种可靠的实现方案：

方案1：递归CTE（最精准，适配所有主流数据库） #

递归CTE会从第一条记录开始，逐步筛选出与上一条保留记录时间差超过5分钟的最早条目，完美贴合动态间隔需求。

PostgreSQL 示例 #

假设表名为events，时间戳字段为event_time，包含id、data等业务字段：

WITH RECURSIVE ranked_events AS (
    -- 先按时间戳排序，给每条记录分配行号
    SELECT 
        id, 
        event_time, 
        data,
        ROW_NUMBER() OVER (ORDER BY event_time) AS rn
    FROM events
),
unique_events AS (
    -- 初始步骤：取出第一条记录
    SELECT id, event_time, data, rn
    FROM ranked_events
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归筛选：找到比上一条保留记录晚5分钟以上的最早条目
    SELECT re.id, re.event_time, re.data, re.rn
    FROM ranked_events re
    JOIN unique_events ue ON re.rn > ue.rn
    WHERE re.event_time > ue.event_time + INTERVAL '5 minutes'
    -- 确保只取符合条件的第一条，避免重复筛选
    AND NOT EXISTS (
        SELECT 1
        FROM ranked_events re2
        WHERE re2.rn > ue.rn
        AND re2.event_time > ue.event_time + INTERVAL '5 minutes'
        AND re2.rn < re.rn
    )
)
SELECT id, event_time, data FROM unique_events ORDER BY event_time;

MySQL 示例 #

语法与PostgreSQL类似，仅时间间隔函数略有差异：

WITH RECURSIVE ranked_events AS (
    SELECT 
        id, 
        event_time, 
        data,
        ROW_NUMBER() OVER (ORDER BY event_time) AS rn
    FROM events
),
unique_events AS (
    SELECT id, event_time, data, rn
    FROM ranked_events
    WHERE rn = 1
    
    UNION ALL
    
    SELECT re.id, re.event_time, re.data, re.rn
    FROM ranked_events re
    JOIN unique_events ue ON re.rn > ue.rn
    WHERE re.event_time > DATE_ADD(ue.event_time, INTERVAL 5 MINUTE)
    AND NOT EXISTS (
        SELECT 1
        FROM ranked_events re2
        WHERE re2.rn > ue.rn
        AND re2.event_time > DATE_ADD(ue.event_time, INTERVAL 5 MINUTE)
        AND re2.rn < re.rn
    )
)
SELECT id, event_time, data FROM unique_events ORDER BY event_time;

方案2：窗口函数+累积分组（适合简单场景） #

如果数据中不存在“与前一条记录间隔超5分钟，但与更早的保留记录间隔在5分钟内”的情况，可使用窗口函数快速实现：

WITH ranked_events AS (
    SELECT 
        id, 
        event_time, 
        data,
        -- 标记是否需要保留：第一条记录，或与上一条记录间隔超5分钟
        CASE 
            WHEN LAG(event_time) OVER (ORDER BY event_time) IS NULL THEN 1
            WHEN TIMESTAMPDIFF(MINUTE, LAG(event_time) OVER (ORDER BY event_time), event_time) > 5 THEN 1
            ELSE 0
        END AS keep_flag
    FROM events
),
cumulative_groups AS (
    -- 用累积求和生成分组ID，同一5分钟窗口内的记录会被分到同一组
    SELECT 
        *,
        SUM(keep_flag) OVER (ORDER BY event_time) AS group_id
    FROM ranked_events
)
-- 每个分组仅保留第一条记录
SELECT id, event_time, data
FROM cumulative_groups
WHERE keep_flag = 1
ORDER BY event_time;

注意：此方案仅适用于时间序列连续递增且无“跳步”的场景，若存在某条记录与前一条间隔超5分钟，但与更早的保留记录间隔在5分钟内，会错误保留该记录，此时优先选择递归CTE方案。

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