如何对带时间戳的记录按固定时间间隔进行采样

需求是从含timestamp列的数据集中，按每x秒间隔生成时间点，并为每个时间点匹配原表中不晚于该时间点的最新记录，示例如下：

原表数据 #

name    timestamp
bob    05-05-2024 15:00:00
Ted    05-05-2024 15:06:00
Alice  05-05-2024 15:07:00
John   05-05-2024 15:08:00
Denver 05-05-2024 15:11:00

当x=5时的期望结果 #

name    target_time
bob    05-05-2024 15:00:00
bob    05-05-2024 15:05:00
John   05-05-2024 15:10:00
Denver 05-05-2024 15:15:00

核心思路 #

1. 生成从原表最小时间开始、每隔x秒的时间序列，直到超过原表最大时间；
2. 对每个生成的时间点，关联原表找到该时间点之前的最新记录。

分数据库实现 #

1. MySQL 8.0+（支持CTE和递归）

WITH RECURSIVE time_intervals AS (
    -- 起始时间：原表最小timestamp
    SELECT MIN(timestamp) AS target_time
    FROM your_table
    UNION ALL
    -- 递归生成每隔x秒的时间点，这里x=5
    SELECT DATE_ADD(target_time, INTERVAL 5 SECOND)
    FROM time_intervals
    WHERE DATE_ADD(target_time, INTERVAL 5 SECOND) <= (SELECT MAX(timestamp) + INTERVAL 5 SECOND FROM your_table)
)
SELECT 
    t2.name,
    ti.target_time
FROM time_intervals ti
LEFT JOIN (
    -- 为每条记录标记同组内的最新记录（按时间倒序排名）
    SELECT 
        name,
        timestamp,
        ROW_NUMBER() OVER (ORDER BY timestamp DESC) AS rn
    FROM your_table t1
    WHERE t1.timestamp <= ti.target_time
) t2 ON t2.rn = 1
ORDER BY ti.target_time;

2. PostgreSQL

WITH time_intervals AS (
    -- 生成时间序列，x=5秒
    SELECT generate_series(
        (SELECT MIN(timestamp) FROM your_table),
        (SELECT MAX(timestamp) + INTERVAL '5 seconds' FROM your_table),
        INTERVAL '5 seconds'
    ) AS target_time
)
SELECT 
    t2.name,
    ti.target_time
FROM time_intervals ti
LEFT JOIN LATERAL (
    -- 取当前时间点之前的最新记录
    SELECT name, timestamp
    FROM your_table
    WHERE timestamp <= ti.target_time
    ORDER BY timestamp DESC
    LIMIT 1
) t2 ON TRUE
ORDER BY ti.target_time;

3. 通用兼容方案（无递归/CTE支持）

如果数据库不支持递归或CTE，可以预先生成时间点临时表，再关联查询：

-- 先手动或通过脚本生成时间点临时表
CREATE TEMP TABLE time_intervals (target_time DATETIME);
INSERT INTO time_intervals VALUES 
('2024-05-05 15:00:00'),
('2024-05-05 15:05:00'),
('2024-05-05 15:10:00'),
('2024-05-05 15:15:00');

-- 关联查询
SELECT 
    (SELECT name FROM your_table WHERE timestamp <= ti.target_time ORDER BY timestamp DESC LIMIT 1) AS name,
    ti.target_time
FROM time_intervals ti
ORDER BY ti.target_time;

逻辑说明 #

• 时间序列生成：确保覆盖从最早记录到最晚记录之后的第一个间隔点，避免遗漏最后一个区间；
• 匹配最新记录：通过ORDER BY timestamp DESC LIMIT 1或窗口函数ROW_NUMBER()，确保每个时间点取到的是不晚于它的最近一条数据；
• 灵活性：只需修改INTERVAL '5 seconds'中的数值，即可适配任意x秒间隔，无需修改表结构或写入逻辑。

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