基于BigQuery最后提取日期（每周一）的18个月预测数量查询需求

嘿，我来帮你把这个查询改得更简洁、更灵活！你的原始CASE语句硬编码了一堆日期，不仅维护麻烦，还没法自动适配每周更新的最后一个周一基准日期，咱们来解决这些问题：

第一步：先锁定最后一个周一的提取日期 #

首先得用CTE（公共表表达式）自动获取咱们需要的基准日期——也就是最新的那个周一的extract_date：

WITH base_date AS (
  SELECT 
    MAX(extract_date) AS last_monday_extract_date
  FROM your_table
  -- 如果你的extract_date字段本身全是周一，可以去掉下面这个WHERE条件
  WHERE EXTRACT(DAYOFWEEK FROM extract_date) = 2 -- BigQuery里周一对应DAYOFWEEK=2，周日是1
)

第二步：主查询——按物料+交货日期汇总，标记预测周期 #

接下来用这个基准日期来动态判断交货日期属于哪个预测周期，同时汇总数量：

SELECT 
  t.material,
  t.material_desc,
  bd.last_monday_extract_date AS extract_date,
  t.deliv_date,
  SUM(t.quantity) AS quantity,
  CASE
    -- 未来6个月：交货日期在基准日及之后，且间隔≤6个月（可根据你的需求调整是否包含基准当月）
    WHEN DATE_DIFF(t.deliv_date, bd.last_monday_extract_date, MONTH) BETWEEN 0 AND 6 THEN '6months'
    -- 未来12个月：间隔在7-12个月之间
    WHEN DATE_DIFF(t.deliv_date, bd.last_monday_extract_date, MONTH) BETWEEN 7 AND 12 THEN '12months'
    -- 未来18个月：间隔在13-18个月之间
    WHEN DATE_DIFF(t.deliv_date, bd.last_monday_extract_date, MONTH) BETWEEN 13 AND 18 THEN '18months'
    ELSE NULL
  END AS forecast_period
FROM your_table t
CROSS JOIN base_date bd
-- 只筛选基准日之后的交货数据（如果要包含基准当月，保留=即可）
WHERE t.deliv_date >= bd.last_monday_extract_date
GROUP BY 
  t.material,
  t.material_desc,
  bd.last_monday_extract_date,
  t.deliv_date,
  forecast_period
ORDER BY 
  t.material,
  t.deliv_date;

关键优化点 #

• 动态基准日：不用手动改日期，每周数据更新后自动取最新的周一作为基准
• 简化日期判断：用DATE_DIFF替代一堆硬编码的OR条件，代码更简洁，后期调整周期范围也方便
• 灵活适配需求：如果你的“未来6个月”是从基准日的下一个月开始，只要把BETWEEN 0 AND 6改成BETWEEN 1 AND 6就行

可选：把不同周期数量拆成列展示 #

如果需要直接看到每个物料在6/12/18个月的总数量（而不是每行标记周期），可以用条件聚合实现：

WITH base_date AS (
  SELECT MAX(extract_date) AS last_monday_extract_date
  FROM your_table
  WHERE EXTRACT(DAYOFWEEK FROM extract_date) = 2
)
SELECT 
  t.material,
  t.material_desc,
  bd.last_monday_extract_date AS extract_date,
  -- 未来6个月的总数量
  SUM(CASE WHEN DATE_DIFF(t.deliv_date, bd.last_monday_extract_date, MONTH) BETWEEN 0 AND 6 THEN t.quantity ELSE 0 END) AS qty_6months,
  -- 未来12个月的总数量（包含前6个月的数据）
  SUM(CASE WHEN DATE_DIFF(t.deliv_date, bd.last_monday_extract_date, MONTH) BETWEEN 0 AND 12 THEN t.quantity ELSE 0 END) AS qty_12months,
  -- 未来18个月的总数量（包含前12个月的数据）
  SUM(CASE WHEN DATE_DIFF(t.deliv_date, bd.last_monday_extract_date, MONTH) BETWEEN 0 AND 18 THEN t.quantity ELSE 0 END) AS qty_18months
FROM your_table t
CROSS JOIN base_date bd
WHERE t.deliv_date >= bd.last_monday_extract_date
GROUP BY 
  t.material,
  t.material_desc,
  bd.last_monday_extract_date
ORDER BY t.material;

这样输出的结果更适合做报表，一眼就能看到每个物料各周期的预测总量。

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