PostgreSQL如何按15分钟间隔分组计算各线路mw平均值

你之前写的查询无法生效的核心原因有两个：

• 生成的generate_series时间序列没有和表内存储的采集时间字段做关联，直接作为分组字段会出现数据和时间窗口完全不匹配的问题
• 序列设置的步长为5000毫秒（5秒），和你需要的15分钟聚合粒度不匹配

基础实现（仅返回存在采集数据的时间窗口） #

你的表中time字段为毫秒级epoch时间戳，首先需要转换为数据库可识别的时间类型，再通过时间分桶逻辑把每条采集数据归属到对应的15分钟窗口，最后按维度分组求平均值即可，不需要额外生成时间序列：

SELECT
  station,
  line_name,
  -- 将采集时间对齐到所属15分钟窗口的起点
  date_trunc('minute', to_timestamp(time / 1000))
    - INTERVAL '1 minute' * (EXTRACT(MINUTE FROM to_timestamp(time / 1000))::INT % 15)
    AS fifteen_min_window,
  AVG(mw) AS avg_mw
FROM lines_table
-- 如需限定统计的站点、线路、时间范围，可放开WHERE条件
-- WHERE station = 'station_name'
--   AND line_name = 'tr1'
--   AND time BETWEEN 1646676821000 AND 1646676841000
GROUP BY station, line_name, fifteen_min_window
ORDER BY station, line_name, fifteen_min_window;

这个查询默认按每小时0分、15分、30分、45分作为窗口分割点，比如12:07的采集数据会归到12:00的窗口，12:22的采集数据会归到12:15的窗口。

补全空窗口实现（无数据的时间区间也会返回） #

如果需要展示指定时间范围内所有15分钟窗口的结果，哪怕某个窗口内没有对应线路的采集数据（平均值返回NULL），可以通过先生成全量时间窗口+线路组合、再左连原始数据的方式实现：

WITH
-- 生成统计周期内所有15分钟间隔的时间窗口
time_windows AS (
  SELECT generate_series(
    to_timestamp(1646676821000 / 1000), -- 替换为统计开始时间（毫秒戳转秒）
    to_timestamp(1646676841000 / 1000), -- 替换为统计结束时间
    INTERVAL '15 minutes'
  ) AS fifteen_min_window
),
-- 取出所有需要统计的站点、线路组合
target_lines AS (
  SELECT DISTINCT station, line_name
  FROM lines_table
  -- 如需限定统计范围可在此加筛选条件
  -- WHERE station = 'station_name' AND line_name = 'tr1'
)
SELECT
  tl.station,
  tl.line_name,
  tw.fifteen_min_window,
  AVG(lt.mw) AS avg_mw
FROM target_lines tl
-- 笛卡尔积生成所有线路+所有时间窗口的全量组合
CROSS JOIN time_windows tw
-- 左连原始采集数据，匹配对应窗口内的记录
LEFT JOIN lines_table lt
  ON lt.station = tl.station
  AND lt.line_name = tl.line_name
  AND date_trunc('minute', to_timestamp(lt.time / 1000))
    - INTERVAL '1 minute' * (EXTRACT(MINUTE FROM to_timestamp(lt.time / 1000))::INT % 15) = tw.fifteen_min_window
GROUP BY tl.station, tl.line_name, tw.fifteen_min_window
ORDER BY tl.station, tl.line_name, tw.fifteen_min_window;

注意事项 #

• 如果你的time字段存储的是秒级epoch而非毫秒级，把所有time / 1000的写法直接替换为time即可
• 表中存储的hour/minute/seconds/date属于冗余字段，所有时间维度计算都可以通过time字段直接转换得到，不需要额外维护
• 如果需要调整窗口对齐起点，只需要修改时间偏移的计算逻辑即可，比如要从每小时的5分开始切分15分钟窗口，在分桶计算时额外减5分钟偏移量即可

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