如何基于过去12个月数据计算7日滚动平均值？

原代码的问题分析 #

你之前的SQL存在三个核心错误，导致结果偏差极大：

• PARTITION BY Date将每个日期的数据单独分区，窗口函数无法跨日期计算滚动值，完全失去了滚动窗口的意义。
• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW是按行号取前6行，不是按日期范围取过去7天。如果同一日期有多条记录，这个窗口只会取当前行和同日期的前几条记录，根本覆盖不了7天的数据。
• 未先按日期聚合每日总金额，计算的是单条金额记录的滚动和，而非每日总额的平均值。

正确实现步骤 #

1. 先聚合每日总金额

首先将同一日期的多条金额记录合并为当日总额，这是计算滚动平均值的基础：

WITH daily_totals AS (
    SELECT 
        Date,
        SUM(Amount) AS daily_amount
    FROM your_table
    GROUP BY Date
)

2. 计算带12个月范围限制的7日滚动平均值

使用日期范围窗口而非行号窗口，确保取到过去7天的所有数据，同时限制窗口仅包含当前日期往前12个月内的数据：

WITH daily_totals AS (
    SELECT 
        Date,
        SUM(Amount) AS daily_amount
    FROM your_table
    GROUP BY Date
)
SELECT
    Date,
    daily_amount,
    -- 计算过去7天（含当日）的每日金额平均值，仅包含过去12个月内的数据
    AVG(daily_amount) OVER (
        ORDER BY Date
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM daily_totals
-- 可选：仅保留有完整12个月历史数据的日期
WHERE Date >= (SELECT MIN(Date) FROM daily_totals) + INTERVAL 12 MONTH

3. 处理缺失日期（可选但推荐）

如果存在无记录的日期（即该日无任何金额数据），直接计算会跳过这些日期，导致平均值不准确。可以生成连续日期序列，填充缺失日期的金额为0：

-- 生成连续日期序列（PostgreSQL语法，其他数据库需调整）
WITH date_range AS (
    SELECT 
        GENERATE_SERIES(
            (SELECT MIN(Date) FROM your_table),
            (SELECT MAX(Date) FROM your_table),
            INTERVAL '1 day'
        ) AS Date
),
daily_totals AS (
    SELECT 
        dr.Date,
        COALESCE(SUM(yt.Amount), 0) AS daily_amount
    FROM date_range dr
    LEFT JOIN your_table yt ON dr.Date = yt.Date
    GROUP BY dr.Date
)
SELECT
    Date,
    daily_amount,
    AVG(daily_amount) OVER (
        ORDER BY Date
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM daily_totals
WHERE Date >= (SELECT MIN(Date) FROM daily_totals) + INTERVAL 12 MONTH

数据库兼容说明 #

• 不同数据库的日期范围窗口语法略有差异：
  • MySQL：需用BETWEEN DATE_SUB(Date, INTERVAL 6 DAY) AND Date结合窗口排序，或确保日期连续后用ROWS BETWEEN 6 PRECEDING AND CURRENT ROW。
  • SQL Server：使用RANGE BETWEEN DATEADD(day, -6, Date) AND CURRENT ROW。
• 若数据库不支持GENERATE_SERIES，可使用递归CTE生成连续日期。

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