Oracle中基于timestamp时间戳分组统计每5分钟产量的实现问询

核心逻辑是将每条记录的timestamp向下对齐到所属的5分钟时间窗口，再按窗口分组对produced字段求和即可，不同数据库的具体实现语法如下：

MySQL #

SELECT 
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`timestamp`) / 300) * 300) AS five_minute_window,
    SUM(produced) AS total_produced
FROM your_table_name
GROUP BY five_minute_window
ORDER BY five_minute_window;

代码中300是5分钟对应的秒数，your_table_name替换为实际表名即可

PostgreSQL #

SELECT 
    date_trunc('hour', "timestamp") + (date_part('minute', "timestamp")::int / 5) * interval '5 min' AS five_minute_window,
    SUM(produced) AS total_produced
FROM your_table_name
GROUP BY five_minute_window
ORDER BY five_minute_window;

SQL Server #

SELECT 
    DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [timestamp]) / 5 * 5, 0) AS five_minute_window,
    SUM(produced) AS total_produced
FROM your_table_name
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [timestamp]) / 5 * 5, 0)
ORDER BY five_minute_window;

优化&注意事项 #

• 若需要调整窗口起始规则（比如从每小时第1分钟开始统计窗口为1-5、6-10），只需在计算分钟偏移量时先加1再做整除运算即可
• 时间字段带时区的场景，需要先将timestamp统一转换为业务所属时区后再做窗口对齐，避免跨时区统计出现数据偏差
• 数据量较大时可以给timestamp字段加索引，也可以预生成5分钟窗口字段做冗余存储，大幅提升查询效率

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