如何优化SQL，快速计算两列数据的月度累计平均值？

原SQL性能差的核心原因是相关子查询会对每个分组重复扫描全表，数据量越大，响应越慢。可以通过「先按月聚合+窗口函数累计」的方式，只扫描一次表就能完成计算，大幅提升效率：

优化后的SQL语句 #

WITH monthly_stats AS (
    -- 第一步：按月聚合，计算当月的各项统计值
    SELECT
        DATE_FORMAT(createdAt, '%b-%Y') AS month,
        LAST_DAY(createdAt) AS month_end, -- 用于排序和窗口函数的时间锚点
        SUM(wash_sc) AS monthly_wash_sum,
        SUM(agric_sc) AS monthly_agric_sum,
        COUNT(*) AS monthly_count
    FROM checkins
    GROUP BY month, month_end
    ORDER BY month_end
)
SELECT
    month,
    -- 累计平均值 = 累计总和 / 累计记录数
    SUM(monthly_wash_sum) OVER (ORDER BY month_end) / SUM(monthly_count) OVER (ORDER BY month_end) AS cumulative_avg_wash,
    SUM(monthly_agric_sum) OVER (ORDER BY month_end) / SUM(monthly_count) OVER (ORDER BY month_end) AS cumulative_avg_agric
FROM monthly_stats;

关键优化点 #

• 减少扫描次数：仅对原表做一次聚合扫描，避免了子查询的重复遍历
• 窗口函数高效累计：利用窗口函数的有序累计特性，直接计算到当前月份的总和与记录数，再推导平均值
• 索引优化：如果createdAt字段没有索引，建议创建INDEX idx_checkins_createdAt (createdAt)，进一步加速按月聚合的分组操作

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