MySQL如何将创建时间差不超过指定秒数的记录分组并对数值求和

该需求完全可以在MySQL中实现，如果使用的是MySQL 8.0及以上版本，可通过窗口函数快速完成计算；如果仍在使用5.x版本，也可通过自定义变量实现对应逻辑。

核心计算逻辑 #

• 首先将表中的年、月、日、时间字段拼接为完整的datetime类型，用于计算时间差
• 按时间升序排序所有记录，计算当前行与上一行的时间差
• 给时间差超过10秒的行打上新分组的起始标记
• 累加标记值生成每个时间窗口的唯一分组ID
• 按分组ID聚合，求和Value字段即可得到每个时间窗口的汇总结果

MySQL 8.0+ 实现代码 #

WITH full_time AS (
    -- 拼接得到完整的创建时间
    SELECT 
        STR_TO_DATE(CONCAT(Year,'-',Month,'-',Day,' ',Time), '%Y-%m-%d %H:%i:%s') AS create_time,
        Value
    FROM 你的实际表名
),
lag_cal AS (
    -- 取上一行记录的创建时间
    SELECT 
        create_time,
        Value,
        LAG(create_time, 1) OVER (ORDER BY create_time) AS prev_time
    FROM full_time
),
group_flag AS (
    -- 生成分组起始标记：第一行或和上一行时间差超过10秒则标记为1
    SELECT 
        create_time,
        Value,
        IF(TIMESTAMPDIFF(SECOND, prev_time, create_time) > 10 OR prev_time IS NULL, 1, 0) AS flag
    FROM lag_cal
),
group_id AS (
    -- 累加标记得到每个窗口的唯一分组ID
    SELECT 
        create_time,
        Value,
        SUM(flag) OVER (ORDER BY create_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS gid
    FROM group_flag
)
-- 按分组聚合求和
SELECT 
    MIN(create_time) AS 窗口起始时间,
    MAX(create_time) AS 窗口结束时间,
    SUM(Value) AS Value总和
FROM group_id
GROUP BY gid;

示例表计算结果 #

运行上述代码后，你给出的示例表会得到如下结果：

• 2021-10-17 15:53:35 ~ 2021-10-17 15:53:38 求和结果：-11
• 2021-10-14 08:29:02 ~ 2021-10-14 08:29:04 求和结果：-5
• 2021-10-09 12:21:22 无符合条件的相邻记录：-10
• 2021-10-09 12:34:20 与上一行时间差超过10秒：-5
• 后续其余两两间隔小于10秒的记录均会合并求和，最后一条2021-07-26 19:12:21无相邻记录：-2

MySQL 5.x 兼容实现代码 #

如果使用的是不支持窗口函数的5.x版本，可使用自定义变量实现相同逻辑：

SELECT 
    MIN(create_time) AS 窗口起始时间,
    MAX(create_time) AS 窗口结束时间,
    SUM(Value) AS Value总和
FROM (
    SELECT 
        create_time,
        Value,
        @gid := IF(TIMESTAMPDIFF(SECOND, @prev_time, create_time) >10 OR @prev_time IS NULL, @gid+1, @gid) AS gid,
        @prev_time := create_time
    FROM (
        SELECT 
            STR_TO_DATE(CONCAT(Year,'-',Month,'-',Day,' ',Time), '%Y-%m-%d %H:%i:%s') AS create_time,
            Value
        FROM 你的实际表名
        ORDER BY create_time
    ) t1, (SELECT @gid:=0, @prev_time:=NULL) t2
) t3
GROUP BY gid;

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