MySQL5.1如何计算每行数据对应的3项移动平均值？

解决方案 #

你原来的写法使用GROUP BY按3行一组聚合，所以只会每3行返回1条结果。MySQL 5.1没有内置窗口函数，我们可以通过带行号的子查询自关联来实现逐行的移动平均计算，具体SQL如下：

SELECT 
    t1.scdate,
    t1.value1,
    t1.value2,
    ROUND(AVG(t2.value1), 2) AS avg_v1,
    ROUND(AVG(t2.value2), 2) AS avg_v2
FROM 
    (
        SELECT 
            scdate,
            value1,
            value2,
            @rownum := @rownum + 1 AS rn
        FROM 
            scantech_data,
            (SELECT @rownum := 0) init
        WHERE 
            scdate >= '2021-11-10 17:00:00' 
            AND scdate <= '2021-11-10 17:10:00'
        ORDER BY scdate ASC
    ) t1
LEFT JOIN 
    (
        SELECT 
            scdate,
            value1,
            value2,
            @rownum2 := @rownum2 + 1 AS rn
        FROM 
            scantech_data,
            (SELECT @rownum2 := 0) init
        WHERE 
            scdate >= '2021-11-10 17:00:00' 
            AND scdate <= '2021-11-10 17:10:00'
        ORDER BY scdate ASC
    ) t2 
ON t2.rn BETWEEN t1.rn AND t1.rn + 2
GROUP BY t1.rn, t1.scdate, t1.value1, t1.value2
ORDER BY t1.scdate ASC;

实现逻辑 #

• 先给所有符合时间范围的记录按时间升序分配连续行号
• 将带行号的表做自关联，关联条件设置为右表的行号落在「当前行号到当前行号+2」的区间，正好覆盖当前行+后续2行共3条数据
• 按左表的每条记录分组，计算关联到的右表数据的平均值，即可得到每行对应的3项移动平均
• 最后1行没有后续行，只会关联到自身，平均值就是自身值，完全匹配需求

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