存在重复日期的数据表计算历史累计平均值与每日条目数SQL问题

你已经完成的按日期分组统计单日条目数的逻辑是核心基础，我们在此基础上补充累计平均值的计算即可，以下提供两种兼容不同数据库版本的写法：

方案1：支持窗口函数的主流数据库（MySQL 8.0+、PostgreSQL、SQLite 3.25+ 等，优先推荐） #

利用窗口函数的累加特性直接计算，性能高写法简洁：

SELECT
    date,
    -- 累计所有历史value总和 / 累计所有历史记录条数 = 截止当前日期的历史平均值
    SUM(daily_value_sum) OVER (ORDER BY date) / SUM(daily_record_count) OVER (ORDER BY date) AS avg,
    daily_record_count AS count
FROM (
    -- 内层先按日期分组，计算单日的value总和、单日记录条数
    SELECT
        date,
        SUM(value) AS daily_value_sum,
        COUNT(*) AS daily_record_count
    FROM table_name
    GROUP BY date
    ORDER BY date
) t;

方案2：不支持窗口函数的低版本数据库兼容写法 #

用自关联实现累计计算，结果和方案1完全一致：

SELECT
    t1.date,
    SUM(t2.daily_value_sum) / SUM(t2.daily_record_count) AS avg,
    t1.daily_record_count AS count
FROM (
    SELECT date, SUM(value) AS daily_value_sum, COUNT(*) AS daily_record_count
    FROM table_name GROUP BY date
) t1
-- 关联所有小于等于当前日期的分组数据，累加得到历史总和
JOIN (
    SELECT date, SUM(value) AS daily_value_sum, COUNT(*) AS daily_record_count
    FROM table_name GROUP BY date
) t2 ON t2.date <= t1.date
GROUP BY t1.date, t1.daily_record_count
ORDER BY t1.date;

两种写法输出的结果和你给出的预期示例完全匹配，可直接用于后续gnuplot绘图。

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