SQL实现：计算从初始日期至当日的累计平均值

问题场景 #

现有表test包含date和value两列，样例数据：

Date        Value
3/1/2023    1
3/1/2023    5
3/2/2023    6
3/2/2023    8
3/3/2023    15

需要实现：对每个日期，计算从表中最早日期到该日期的所有记录的平均值，而非仅当日数据的平均值。期望输出：

Date        running_avg
3/1/2023    3   -- (1+5)/2 = 3
3/2/2023    5   -- (1+5+6+8)/4 = 5
3/3/2023    7   -- (1+5+6+8+15)/5 = 7

原查询因使用GROUP BY date仅统计当日数据，不符合需求，以下是正确解法：

解法1：窗口函数（推荐，兼容多数现代数据库） #

对于支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server、Oracle），直接用AVG() OVER()实现累计计算：

SELECT DISTINCT
    date,
    AVG(value) OVER (ORDER BY date) AS running_avg
FROM test;

说明 #

• AVG(value) OVER (ORDER BY date)：窗口范围默认是从表的第一行（最早日期）到当前行的所有数据，自动计算累计平均值
• DISTINCT：因为每个日期有多条记录，用它确保每个日期只返回一行结果

如果需要更明确指定窗口范围，可以写成：

SELECT DISTINCT
    date,
    AVG(value) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg
FROM test;

解法2：关联子查询（兼容老版本数据库） #

如果你的数据库不支持窗口函数（如MySQL 5.x），可以用关联子查询实现：

SELECT
    t1.date,
    (SELECT AVG(value) FROM test t2 WHERE t2.date <= t1.date) AS running_avg
FROM test t1
GROUP BY t1.date
ORDER BY t1.date;

说明 #

• 子查询会针对t1中的每个日期，统计所有日期小于等于该日期的value的平均值
• GROUP BY t1.date确保每个日期仅返回一行结果

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