如何按每3天对数据表的datetime与value字段进行聚合？

原始数据表 #

期望聚合结果 #

实现思路 #

核心就是给每条数据分配一个分组ID，让每连续3天的记录归为同一组，最后按这个ID聚合计算总和，再拼接时间范围即可。

MySQL 实现代码 #

SELECT
  CONCAT(MIN(DATE(datetime)), ' - ', MAX(DATE(datetime))) AS datetime,
  SUM(value) AS value
FROM (
  SELECT
    datetime,
    value,
    -- 以表中最早日期为基准，每3天划一个分组
    FLOOR(DATEDIFF(DATE(datetime), (SELECT MIN(DATE(datetime)) FROM your_table)) / 3) AS group_id
  FROM your_table
) AS grouped_data
GROUP BY group_id
ORDER BY group_id;

PostgreSQL 实现代码 #

SELECT
  CONCAT(MIN(DATE(datetime)), ' - ', MAX(DATE(datetime))) AS datetime,
  SUM(value) AS value
FROM (
  SELECT
    datetime,
    value,
    -- 以表中最早日期为基准，每3天划一个分组
    FLOOR((DATE(datetime) - (SELECT MIN(DATE(datetime)) FROM your_table)) / INTERVAL '3 days') AS group_id
  FROM your_table
) AS grouped_data
GROUP BY group_id
ORDER BY group_id;

注意事项 #

• 子查询里的分组ID默认以表中最早日期为起点，若想从指定日期开始分组，直接把MIN(DATE(datetime))替换成目标起始日期即可
• 用DATE(datetime)是忽略时分秒仅按日期分组，若需保留时间精度，可去掉DATE()函数并调整日期差的计算逻辑

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