Teradata SQL计算近7自然日移动平均值（缺失日期补0）

问题根源 #

• ROWS窗口函数按行数量选取数据，而非自然日间隔，当存在日期缺失时，会错误选取超出7天范围的历史数据。
• Teradata对RANGE窗口函数的日期间隔语法有严格要求，错误写法会触发Expected the word RESET or ')' after ORDER BY报错。

解决方案步骤 #

1. 生成完整的日期-商品维度表 #

先补全每个商品对应的所有日期，确保无缺失日期记录：

WITH date_range AS (
    -- 获取销售数据的最小/最大日期，生成完整日期序列
    SELECT MIN(sale_date) AS dt, MAX(sale_date) AS end_dt
    FROM your_sales_table
    UNION ALL
    SELECT dt + INTERVAL '1' DAY, end_dt
    FROM date_range
    WHERE dt + INTERVAL '1' DAY <= end_dt
),
article_full_dates AS (
    -- 笛卡尔积生成每个商品的所有日期记录
    SELECT DISTINCT a.article_no, d.dt AS sale_date
    FROM your_sales_table a
    CROSS JOIN date_range d
)

2. 关联销售数据并补0 #

将完整维度表与原销售表左关联，缺失日期的sell_val用COALESCE补0：

,sales_with_zero AS (
    SELECT 
        afd.article_no,
        afd.sale_date,
        COALESCE(s.sell_val, 0) AS sell_val
    FROM article_full_dates afd
    LEFT JOIN your_sales_table s
        ON afd.article_no = s.article_no
        AND afd.sale_date = s.sale_date
)

3. 用RANGE窗口函数计算近7自然日平均值 #

使用Teradata支持的日期间隔语法，计算当前日期及后续6天的销售平均值：

SELECT 
    article_no,
    sale_date,
    AVG(sell_val) OVER (
        PARTITION BY article_no
        ORDER BY sale_date
        RANGE BETWEEN CURRENT ROW AND INTERVAL '6' DAY FOLLOWING
    ) AS avg_sell_val
FROM sales_with_zero
ORDER BY article_no, sale_date;

关键说明 #

• 递归CTE生成日期序列时，若数据量过大，可调整Teradata的RECURSIVE_DEPTH参数（默认1000）避免报错，或使用系统表结合序列函数生成日期。
• RANGE BETWEEN CURRENT ROW AND INTERVAL '6' DAY FOLLOWING是Teradata针对日期类型的正确间隔写法，确保只选取自然日范围内的7天数据（当前日+后续6天）。
• 先补全日期再计算，彻底解决了缺失日期导致的窗口范围错误问题。

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