如何在PostgreSQL 9.6中按300秒时间区间计算字段平均值

看起来你已经搞定了数据筛选的部分，接下来的核心是从第一条记录的时间开始，每300秒（5分钟）划分一个动态区间——这和常规的整点对齐区间不同，不能直接用date_trunc，得用动态起点的分组逻辑。

我给你整理了一套完整的解决方案，完美匹配你的需求：

核心思路拆解 #

1. 先把分散的date和time字段合并成完整的时间戳，方便计算时间差
2. 获取筛选后数据集的第一条记录时间，作为区间的起始基准点
3. 计算每条记录到基准点的秒数差，除以300取整得到分组ID，同一ID的记录属于同一个5分钟区间
4. 按分组ID和其他维度分组，计算vin字段的平均值

完整SQL语句 #

记得把your_table_name替换成你实际的表名：

WITH filtered_data AS (
    -- 筛选目标数据，合并date和time为完整时间戳
    SELECT 
        mac,
        sn,
        loc,
        -- 将字符串格式的日期时间转换为timestamp类型
        to_timestamp(concat(date, ' ', time), 'MM/DD/YYYY HH24:MI:SS') AS record_time,
        vin1,
        vin2,
        vin3
    FROM your_table_name
    WHERE sn = '4as11111111' 
      -- 把字符串date转为date类型，方便日期范围筛选
      AND to_date(date, 'MM/DD/YYYY') BETWEEN '2018-01-01' AND '2018-01-02'
),
first_record AS (
    -- 获取筛选后数据的最早时间，作为区间起始基准
    SELECT min(record_time) AS start_time
    FROM filtered_data
)
SELECT 
    fd.mac,
    fd.sn,
    fd.loc,
    -- 格式化区间起始时间为你需要的time格式
    to_char(fr.start_time + (floor(extract(epoch FROM (fd.record_time - fr.start_time)) / 300) * interval '300 seconds'), 'HH24:MI:SS') AS time,
    -- 格式化区间起始时间为你需要的date格式
    to_char(fr.start_time + (floor(extract(epoch FROM (fd.record_time - fr.start_time)) / 300) * interval '300 seconds'), 'MM/DD/YYYY') AS date,
    -- 计算平均值并保留两位小数
    round(avg(fd.vin1), 2) AS vin1_av,
    round(avg(fd.vin2), 2) AS vin2_av,
    round(avg(fd.vin3), 2) AS vin3_av
FROM filtered_data fd
-- 关联基准起始时间，让所有数据都能基于同一个起点计算区间
CROSS JOIN first_record fr
-- 按分组ID和维度字段分组
GROUP BY 
    fd.mac,
    fd.sn,
    fd.loc,
    floor(extract(epoch FROM (fd.record_time - fr.start_time)) / 300)
-- 按区间顺序排序，保证结果按时间先后展示
ORDER BY 
    floor(extract(epoch FROM (fd.record_time - fr.start_time)) / 300);

关键细节说明 #

• 如果你的date或time本身是日期/时间类型（不是字符串），可以直接去掉to_timestamp和to_date的转换，直接使用字段即可。
• 分组逻辑里的floor(...)确保每条记录被分到对应的区间：比如基准时间是7:06:18，那么7:06:18_{7:11:17的记录都属于第0组，7:11:18}7:16:17属于第1组，以此类推。
• 你可以调整round函数的第二个参数，来控制平均值的小数位数。

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