PostgreSQL按10分钟时间间隔计算traveltime平均值的方法

这问题我之前处理类似的时间维度统计时也碰到过，核心就是把你生成的10分钟时间序列和业务表做区间匹配关联，再按每个区间聚合计算平均值。我给你两种常见场景的实现方案，你可以根据需求选择：

场景1：按带日期的完整10分钟区间统计（覆盖数周的每个独立区间） #

比如2019-12-24 08:00-08:10、2019-12-26 08:00-08:10这类独立区间分别计算平均值，用这个SQL：

WITH time_intervals AS (
    -- 生成每个10分钟区间的起始和结束时间
    SELECT 
        i AS interval_start,
        i + INTERVAL '10 minutes' AS interval_end
    FROM generate_series('2019-11-23', '2020-01-18', '10 minutes'::interval) i
)
SELECT
    ti.interval_start,  -- 区间起始时间
    ti.interval_end,    -- 区间结束时间
    ROUND(AVG(b.traveltime)::numeric, 2) AS avg_traveltime  -- 保留两位小数的平均值
FROM time_intervals ti
-- 左连接保证即使区间无数据也会返回（值为NULL）
LEFT JOIN belt b 
    ON b.departuredate >= ti.interval_start 
    AND b.departuredate < ti.interval_end  -- 和你单个查询的逻辑一致，左闭右开区间
GROUP BY ti.interval_start, ti.interval_end
ORDER BY ti.interval_start;

关键细节说明： #

• 用WITH子句（CTE）先生成所有需要统计的时间区间，同时计算出每个区间的结束时间，避免后续重复计算；
• LEFT JOIN确保时间序列的连续性，哪怕某个10分钟区间没有任何出行记录，也会返回该区间的行，平均值为NULL；
• 用ROUND函数可以把平均值格式化得更友好，根据你的需求调整小数位数即可。

场景2：按一天内的时间段聚合（合并所有日期的相同时间段） #

如果你想把所有日期的8:00-8:10区间数据合并计算平均值（比如统计早高峰8点到8点10分的整体平均耗时），可以用这个方案：

WITH daily_time_intervals AS (
    -- 生成一天内所有10分钟的时间区间（仅时间部分，不带日期）
    SELECT 
        i AS interval_start,
        i + INTERVAL '10 minutes' AS interval_end
    FROM generate_series('00:00:00'::time, '23:50:00'::time, '10 minutes'::interval) i
)
SELECT
    di.interval_start,
    di.interval_end,
    ROUND(AVG(b.traveltime)::numeric, 2) AS avg_traveltime
FROM daily_time_intervals di
LEFT JOIN belt b 
    -- 提取departurehour的时间部分做区间匹配，注意时区统一
    ON (b.departurehour AT TIME ZONE 'UTC')::time >= di.interval_start
    AND (b.departurehour AT TIME ZONE 'UTC')::time < di.interval_end
GROUP BY di.interval_start, di.interval_end
ORDER BY di.interval_start;

根据你给出的样本数据和需求描述，场景1应该是你需要的方案，它会精准匹配每个日期的10分钟区间，计算对应区间内的traveltime平均值。

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