BigQuery分区内日期差识别及上年同期周支出计算异常排查

我一眼就看出你遇到的问题所在了！你当前的LastYearWeekSales计算逻辑存在一个关键误区：你只是把当前行的年份减1作为分区字段，但窗口函数依然是对当前本年的数据进行求和，根本没有关联到上年同周的实际数据。

错误原因分析 #

你的代码行：

SUM(Gross_home_currency) OVER (
  PARTITION BY Brand_Family,
               EXTRACT(week from PARSE_DATE('%Y-%m-%d',Customer_Contract_date)),
               EXTRACT(year from DATE_SUB(CAST(Customer_Contract_date as date), INTERVAL 1 Year))
) AS LastYearWeekSales

这里的分区逻辑是：按品牌、本年的周数、当前日期减1后的年份来分组。但所有参与计算的行依然是本年的数据，所以SUM出来的结果自然和本年同周的WeekSales完全一致——你只是给本年的数据打了个“上年”的分区标签，并没有真正去取上年的数据。

正确解决方案 #

要获取上年同期同周的销售额，我们需要先把每年每周的销售数据聚合好，再通过品牌、周数、年份+1的关联逻辑，匹配上年的数据。结合你的示例查询，修正后的完整代码如下：

WITH weekly_sales AS (
  -- 第一步：先按品牌、年份、周聚合出每周销售额
  SELECT
    Brand_Family,
    EXTRACT(YEAR FROM PARSE_DATE('%Y-%m-%d', Customer_Contract_date)) AS Year,
    EXTRACT(WEEK FROM PARSE_DATE('%Y-%m-%d', Customer_Contract_date)) AS Week,
    SUM(Gross_home_currency) AS WeekSales
  FROM exampletable cc
  WHERE
    cc.Customer_Contract_Status = "Processed in ISOP"
    AND cc.Brand_Family IN ("CB")
    AND cc.Contract_Type = "Sponsorship"
    AND EXTRACT(YEAR FROM PARSE_DATE('%Y-%m-%d', Customer_Contract_date)) IN (2018, 2019)
  GROUP BY Brand_Family, Year, Week
)
SELECT
  Year,
  Week,
  Brand_Family,
  WeekSales,
  -- 累计销售额
  SUM(WeekSales) OVER (PARTITION BY Brand_Family, Year ORDER BY Year, Week ASC) AS CumulativeSales,
  -- 上周销售额
  LAG(WeekSales, 1) OVER (PARTITION BY Brand_Family, Year ORDER BY Year, Week ASC) AS LastWeek,
  -- 上年同期同周销售额
  prev_year.WeekSales AS LastYearWeekSales
FROM weekly_sales ws
-- 通过品牌、周数、年份关联上年数据
LEFT JOIN weekly_sales prev_year
  ON ws.Brand_Family = prev_year.Brand_Family
  AND ws.Week = prev_year.Week
  AND ws.Year = prev_year.Year + 1
ORDER BY Year, Week;

额外注意点 #

• 周数匹配问题：部分年份会有53周（比如2020年），如果上年没有对应的53周，LastYearWeekSales会返回NULL，这是符合逻辑的。
• 日期解析优化：你多次使用PARSE_DATE('%Y-%m-%d',Customer_Contract_date)，如果Customer_Contract_date本身就是DATE类型，可以直接用Customer_Contract_date代替，避免重复解析。

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