如何在SQL中计算月度滚动（移动）平均值？

要实现每月内累积计算移动平均值、每月初重置的需求，你需要调整窗口函数的分区逻辑和范围定义，具体修改如下：

修改后的SQL代码 #

SELECT 
    DDA_Account,
    Current_Balance,
    Date_Last_Updated,
    ROUND(AVG(Current_Balance) OVER (
        PARTITION BY 
            DDA_Account,
            DATE_TRUNC('month', Date_Last_Updated)  -- 按账户+月份分区，实现每月重置
        ORDER BY 
            Date_Last_Updated 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 累积计算当月从首日到当日的平均
    ), 2) as avg_current_balance
FROM trb_acct_3
WHERE DDA_Account = '3921'
ORDER BY Date_Last_Updated ASC;

关键修改说明 #

• 新增月份分区维度：通过DATE_TRUNC('month', Date_Last_Updated)将日期截断至当月第一天，让每个账户的每个月数据单独组成一个计算窗口，确保每月初重置计算逻辑。
  • 若你的数据库不支持DATE_TRUNC，可替换为对应数据库的等价函数：
    • MySQL：DATE_FORMAT(Date_Last_Updated, '%Y-%m-01')
    • Oracle：TRUNC(Date_Last_Updated, 'MM')
    • SQL Server：DATEFROMPARTS(YEAR(Date_Last_Updated), MONTH(Date_Last_Updated), 1)
• 替换窗口范围逻辑：用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW替代原有的RANGE INTERVAL 30 DAY preceding，实现当月内从第一条数据到当前数据的累积平均——当月首日平均值为自身余额，次日为两天余额的平均，以此类推。

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