Oracle SQL如何在无数据（默认0）时计算正确滚动平均值？

核心问题是原查询仅包含有数据的周，导致滚动窗口行数不足，无法将缺失周的默认值0纳入计算。只需先补全目标时间段内的所有周数据，再计算滚动平均即可解决。

实现思路 #

1. 生成目标时间段内的所有CY_WEEK值，确保每个需要统计的周都存在；
2. 将生成的周列表与原表左连接，匹配RC分组，把缺失数据的周的DURATION_MINUTES填充为0；
3. 在补全后的完整数据集上计算滚动平均。

具体SQL示例 #

手动枚举目标周（通用多数数据库）

WITH target_weeks AS (
    SELECT '2022_08' AS CY_WEEK
    UNION ALL SELECT '2022_09'
    UNION ALL SELECT '2022_10'
    UNION ALL SELECT '2022_11'
)
SELECT 
    tw.CY_WEEK,
    'FOO' AS RC,
    AVG(COALESCE(mr.DURATION_MINUTES, 0)) OVER (
        ORDER BY tw.CY_WEEK 
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS rolling_avg
FROM target_weeks tw
LEFT JOIN MI_REPORT mr 
    ON tw.CY_WEEK = mr.CY_WEEK 
    AND mr.RC = 'FOO'
ORDER BY tw.CY_WEEK;

递归生成连续周（适合周格式有规律的场景）

如果CY_WEEK是年份_周数的固定格式，可用递归自动生成连续周，无需手动枚举：

WITH RECURSIVE target_weeks AS (
    SELECT '2022_08' AS CY_WEEK
    UNION ALL
    SELECT 
        CONCAT(
            SUBSTRING(CY_WEEK, 1, 4),
            '_',
            LPAD(CAST(SUBSTRING(CY_WEEK, 6) AS INT) + 1, 2, '0')
        ) AS CY_WEEK
    FROM target_weeks
    WHERE CY_WEEK < '2022_11'
)
SELECT 
    tw.CY_WEEK,
    'FOO' AS RC,
    AVG(COALESCE(mr.DURATION_MINUTES, 0)) OVER (
        ORDER BY tw.CY_WEEK 
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS rolling_avg
FROM target_weeks tw
LEFT JOIN MI_REPORT mr 
    ON tw.CY_WEEK = mr.CY_WEEK 
    AND mr.RC = 'FOO'
ORDER BY tw.CY_WEEK;

结果说明 #

补全后的数据集会包含4行：

此时2022_11对应的滚动窗口包含全部4行数据，平均值为(0+0+0+97)/4=24.25，完全符合需求。

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