MySQL中按分组列计算DATETIME列平均值并填充字段的方法

要实现按id分组计算time列的平均值并填充到avg_time字段，核心思路是将DATETIME类型转换为毫秒级时间戳进行数值计算，再转换回DATETIME格式。以下是具体实现步骤：

实现SQL语句 #

-- 按id分组计算每个组的平均毫秒时间戳，更新对应的avg_time字段
UPDATE timestamp_n_datetime t1
JOIN (
    SELECT 
        id,
        -- 将DATETIME转成毫秒级时间戳，计算平均值后转回DATETIME
        FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(time) * 1000) / 1000) AS avg_datetime
    FROM timestamp_n_datetime
    GROUP BY id
) t2 ON t1.id = t2.id
SET t1.avg_time = t2.avg_datetime;

代码说明 #

• UNIX_TIMESTAMP(time)：将DATETIME转换为秒级时间戳，乘以1000得到毫秒级时间戳，方便进行数值运算。
• AVG(...)：对分组后的毫秒时间戳求平均值。
• FROM_UNIXTIME(avg_ms/1000)：将计算得到的平均毫秒时间戳转换回DATETIME格式。
• 使用JOIN关联原表和分组计算结果，保证每个id对应的所有行都能更新到正确的平均值。

验证结果 #

执行更新后，可通过以下语句查看结果：

SELECT * FROM timestamp_n_datetime;

以id=2为例，两行time的平均值计算后会得到2023-03-17 17:45:00，与预期结果一致。

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