Oracle 4周滚动平均值计算（缺失值补0）及动态Pivot需求

核心思路 #

先生成全量RC与周的笛卡尔积补全缺失数据，再基于补全后的数据计算4周滚动平均，最后通过动态SQL实现自动Pivot生成透视表格式结果。

步骤1：生成全量维度组合 #

从原表提取所有唯一RC和时间范围，生成所有周与RC的完整组合，避免数据遗漏：

WITH date_range AS (
    -- 获取数据覆盖的所有周（按ISO周计算）
    SELECT TRUNC(MIN(week_date), 'IW') + (LEVEL - 1)*7 AS week_date
    FROM (
        SELECT TRUNC(week_date, 'IW') AS week_date FROM MY_TABLE
    )
    CONNECT BY TRUNC(MIN(week_date), 'IW') + (LEVEL - 1)*7 <= TRUNC(MAX(week_date), 'IW')
),
rc_list AS (
    -- 提取所有唯一RC标识
    SELECT DISTINCT rc FROM MY_TABLE
),
full_dimensions AS (
    -- 生成RC与周的全量笛卡尔积
    SELECT r.rc, d.week_date
    FROM rc_list r
    CROSS JOIN date_range d
)

步骤2：补全缺失数据并计算滚动平均 #

将全量维度与原表左连接，缺失数值默认填0，再通过窗口函数计算4周滚动平均，同时处理连续4周无数据的场景：

, data_with_zero AS (
    SELECT 
        fd.rc,
        fd.week_date,
        NVL(mt.value, 0) AS value
    FROM full_dimensions fd
    LEFT JOIN MY_TABLE mt 
        ON fd.rc = mt.rc 
        AND TRUNC(mt.week_date, 'IW') = fd.week_date
),
rolling_avg_calc AS (
    SELECT
        rc,
        week_date,
        value,
        -- 计算4周滚动平均，同时判断是否连续4周无数据
        CASE 
            WHEN SUM(value) OVER (
                PARTITION BY rc 
                ORDER BY week_date 
                ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
            ) = 0 THEN 0
            ELSE AVG(value) OVER (
                PARTITION BY rc 
                ORDER BY week_date 
                ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
            )
        END AS rolling_4w_avg
    FROM data_with_zero
)

步骤3：动态Pivot生成透视表结果 #

通过动态SQL自动生成所有周的Pivot列，无需手动枚举：

DECLARE
    pivot_cols VARCHAR2(4000);
BEGIN
    -- 动态拼接所有周的Pivot列名（格式："周YYYY-IW"）
    SELECT LISTAGG(
        '''' || TO_CHAR(week_date, 'YYYY-IW') || ''' AS "周' || TO_CHAR(week_date, 'YYYY-IW') || '"', 
        ', '
    ) INTO pivot_cols
    FROM (SELECT DISTINCT week_date FROM rolling_avg_calc ORDER BY week_date);

    -- 执行动态Pivot查询
    EXECUTE IMMEDIATE '
        SELECT *
        FROM rolling_avg_calc
        PIVOT (
            MAX(rolling_4w_avg) FOR TO_CHAR(week_date, ''YYYY-IW'') IN (' || pivot_cols || ')
        )
        ORDER BY rc
    ';
END;
/

关键说明 #

• 全量维度补全：通过CROSS JOIN确保每个RC在所有周都有记录，彻底解决缺失数据问题；
• 滚动平均逻辑：用ROWS BETWEEN 3 PRECEDING AND CURRENT ROW定义4周滑动窗口，通过窗口内数值总和判断是否连续4周无数据，符合需求；
• 动态Pivot：借助LISTAGG自动生成所有周的列名，适配数据的动态变化，无需手动维护列清单。

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