如何按月份计算数据的累积平均值？附示例与SQL代码

需求说明 #

需要按月份维度计算数据的累积平均值：每个月内从月初到当前日期的val值累积平均，跨月后重置累积计算逻辑（参考示例数据中2023年1月的累积值从当月数据重新开始）。

示例数据 #

现有SQL问题分析 #

• 子查询未选取FCURR字段，外层查询直接引用会触发语法错误
• 当前逻辑计算的是SUM(SUM(val))的累积总和，并非需求的累积平均值
• GROUP BY date, val会拆分同一日期的不同val行，不符合示例中同一日期多条数据参与累积的逻辑
• 窗口函数未按月份分区，无法实现跨月重置累积的核心需求

修正后的SQL代码 #

场景1：保留原始行，直接计算每行的累积平均

BEGIN 
var_out = SELECT 
    FCURR,
    date,
    val,
    -- 按年月分区，计算到当前行的累积平均值
    AVG(val) OVER (PARTITION BY SUBSTR(date, 1, 6) ORDER BY date ASC) AS acum
FROM 
    table
WHERE "KURST" = 'M'
ORDER BY date ASC; 
END

场景2：先按日期聚合val，再计算日汇总值的累积平均

如果需要先将同一日期的val求和，再基于日汇总值计算累积平均，可使用以下代码：

BEGIN 
var_out = SELECT 
    FCURR,
    date,
    daily_val AS val,
    -- 按年月分区，基于日汇总值计算累积平均
    AVG(daily_val) OVER (PARTITION BY SUBSTR(date, 1, 6) ORDER BY date ASC) AS acum
FROM (
    SELECT 
        FCURR,
        date,
        SUM(val) AS daily_val
    FROM table
    WHERE "KURST" = 'M'
    GROUP BY FCURR, date
) AS daily_summary
ORDER BY date ASC; 
END

代码逻辑说明 #

• PARTITION BY SUBSTR(date, 1, 6)：提取日期的前6位（格式如202212）作为分区键，确保每个月的累积计算独立进行
• ORDER BY date ASC：在每个月的分区内按日期排序，保证累积计算的顺序正确
• AVG(val) OVER (...)：窗口函数自动计算从分区起始行到当前行的所有val的平均值，完全匹配示例中的acum字段逻辑

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