求助：BigQuery中实现周内累计销售Projections列的SQL方案

针对你的需求，我们可以通过窗口函数结合FILTER子句，按商品、渠道、周维度分组计算指定日期范围的Sales与Profile总和的比值，直接生成Projections列。

实现代码 #

SELECT
  Item,
  Date,
  Day,
  Day_Number,
  WeekStart,
  Channel,
  Sales,
  Forecast,
  Profile,
  -- 根据当日Day Number计算对应Projections值
  CASE
    WHEN Day_Number = 4 THEN 
      SUM(Sales) FILTER (WHERE Day_Number = 3) OVER (PARTITION BY Item, Channel, WeekStart) 
      / NULLIF(SUM(Profile) FILTER (WHERE Day_Number = 3) OVER (PARTITION BY Item, Channel, WeekStart), 0)
    WHEN Day_Number = 5 THEN 
      SUM(Sales) FILTER (WHERE Day_Number BETWEEN 3 AND 4) OVER (PARTITION BY Item, Channel, WeekStart) 
      / NULLIF(SUM(Profile) FILTER (WHERE Day_Number BETWEEN 3 AND 4) OVER (PARTITION BY Item, Channel, WeekStart), 0)
    WHEN Day_Number = 6 THEN 
      SUM(Sales) FILTER (WHERE Day_Number BETWEEN 3 AND 5) OVER (PARTITION BY Item, Channel, WeekStart) 
      / NULLIF(SUM(Profile) FILTER (WHERE Day_Number BETWEEN 3 AND 5) OVER (PARTITION BY Item, Channel, WeekStart), 0)
    WHEN Day_Number = 7 THEN 
      SUM(Sales) FILTER (WHERE Day_Number BETWEEN 3 AND 6) OVER (PARTITION BY Item, Channel, WeekStart) 
      / NULLIF(SUM(Profile) FILTER (WHERE Day_Number BETWEEN 3 AND 6) OVER (PARTITION BY Item, Channel, WeekStart), 0)
    ELSE NULL -- 非目标日期返回NULL，可按需调整
  END AS Projections
FROM `your_dataset.your_table`
ORDER BY Item, Channel, WeekStart, Day_Number;

代码说明 #

1. 分区逻辑：通过PARTITION BY Item, Channel, WeekStart确保计算范围限定在同一商品、同一渠道、同一周内的数据，避免跨周/跨商品的错误计算。
2. 日期范围筛选：使用FILTER子句精准匹配需求中的日期区间：
   • 周四（Day_Number=4）：仅取周三（Day_Number=3）的Sales和Profile总和
   • 周五（Day_Number=5）：取周三至周四（Day_Number 3-4）的累计总和
   • 周六（Day_Number=6）：取周三至周五（Day_Number 3-5）的累计总和
   • 周日（Day_Number=7）：取周三至周六（Day_Number 3-6）的累计总和
3. 除以0防护：用NULLIF(SUM(Profile), 0)避免Profile总和为0时出现除以0的报错，此时Projections返回NULL。
4. 排序：最终结果按商品、渠道、周起始日、日期排序，保证输出顺序符合常规业务逻辑。

注意事项 #

• 请替换代码中的your_dataset.your_table为实际的BigQuery数据集和表名。
• 如果需要对非周四至周日的日期设置默认值（如0），可修改ELSE后的内容。

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