如何用SQL计算SKU指定日期前21天的平均销售额？

问题分析 #

你需要提取一周内SKU+日期维度的数据，并为每条记录计算该日期前21天的销售额平均值。原SQL的核心问题在于窗口函数范围定义错误，且不必要的GROUP BY导致逻辑冲突。

分场景的正确实现 #

场景1：原表已按日聚合SKU销售额（每天每个SKU仅1条记录）

SELECT
    date,
    sku,
    -- 计算当前日期前21天（不含当天）的平均销售额
    AVG(sale) OVER (
        PARTITION BY sku
        ORDER BY date
        RANGE BETWEEN INTERVAL '21 day' PRECEDING AND INTERVAL '1 day' PRECEDING
    ) AS avg_21_day_sale
FROM sales
-- 过滤目标一周的数据，替换为实际日期范围
WHERE date BETWEEN '2023-01-01' AND '2023-01-07'
ORDER BY date, sku;

场景2：原表是订单级数据（每天每个SKU有多条销售记录）

需先聚合每日SKU销售额，再计算21天平均：

WITH daily_sku_sales AS (
    -- 先按日聚合每个SKU的总销售额
    SELECT
        date,
        sku,
        SUM(sale) AS daily_sale
    FROM sales
    GROUP BY date, sku
)
SELECT
    date,
    sku,
    AVG(daily_sale) OVER (
        PARTITION BY sku
        ORDER BY date
        RANGE BETWEEN INTERVAL '21 day' PRECEDING AND INTERVAL '1 day' PRECEDING
    ) AS avg_21_day_sale
FROM daily_sku_sales
WHERE date BETWEEN '2023-01-01' AND '2023-01-07'
ORDER BY date, sku;

关键说明 #

1. 窗口函数范围修正：
   • 用RANGE BETWEEN INTERVAL '21 day' PRECEDING AND INTERVAL '1 day' PRECEDING精准定义“当前日期前21天”的范围（不含当天）；若需包含当天，改为RANGE BETWEEN INTERVAL '21 day' PRECEDING AND CURRENT ROW。
   • 不同SQL引擎的日期语法略有差异：
     • MySQL：替换为INTERVAL 21 DAY，例如RANGE BETWEEN INTERVAL 21 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING
     • BigQuery：可使用DATE_SUB(date, INTERVAL 21 DAY)结合窗口范围，或保持标准SQL语法
2. 移除不必要的GROUP BY：窗口函数是对每行数据计算聚合值，无需额外GROUP BY；若原表是订单级数据，需先通过CTE聚合每日SKU销售额。

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