Big Query计算过去12个月每月首周时1月1日周数返回00的问题

问题根因 #

BigQuery 中%W格式化符的周数计数规则为：

• 每周以周一作为第一天
• 每年第一个周一之前的所有日期，周数统一返回00
  2021年1月的第一个周一是1月4日，因此1月1日会被判定为2021年第0周，也就出现了你遇到的202100异常结果。

解决方案 #

可根据你的业务需求选择以下两种方案：

方案1：使用ISO周（无00周，通用场景推荐） #

ISO周是国际通用的周计数规则，不会出现00周，对应格式化符为%G（ISO周所属年份）+%V（ISO周数，取值范围01-53），直接替换原来的%Y%W即可。
示例代码：

SELECT 
  FORMAT_DATE('%Y%m%d', DATE_TRUNC(DATE_SUB(CURRENT_DATE(),interval  10 month), MONTH)) AS YYMMDD,
  FORMAT_DATE('%Y%m', DATE_TRUNC(DATE_SUB(CURRENT_DATE(), interval  10 month), MONTH)) AS YYMM,
  FORMAT_DATE('%G%V', DATE_TRUNC(DATE_SUB(CURRENT_DATE(), interval  10 month), MONTH)) AS YYWW

该代码执行后，2021年1月1日返回的YYWW为202053，属于合法周数，不会打断统计逻辑。

方案2：取当月第一个周一所在周（周数完全落在当月） #

如果你要求第一周的周数完全归属当前月，可以先计算当月第一个周一的日期，再基于该日期计算周数，就不会出现00结果。
示例代码：

SELECT 
  FORMAT_DATE('%Y%m%d', first_monday_of_month) AS YYMMDD,
  FORMAT_DATE('%Y%m', first_monday_of_month) AS YYMM,
  FORMAT_DATE('%Y%W', first_monday_of_month) AS YYWW
FROM (
  -- 计算当月第一个周一的日期
  SELECT 
    DATE_TRUNC(DATE_SUB(CURRENT_DATE(), interval  10 month), MONTH) 
    + INTERVAL MOD(9 - EXTRACT(DAYOFWEEK FROM DATE_TRUNC(DATE_SUB(CURRENT_DATE(), interval  10 month), MONTH)),7) DAY 
    AS first_monday_of_month
)

2021年1月的第一个周一是1月4日，执行后返回的YYWW为202101，完全符合%Y%W的计数规则，无00问题。

批量生成近12个月的第一周数据 #

如果需要一次性计算往前12个月所有月份的第一周，可以直接使用以下通用代码：

WITH months AS (
  -- 生成近12个月的月初日期序列
  SELECT DATE_TRUNC(DATE_SUB(CURRENT_DATE(), interval n month), MONTH) AS month_start
  FROM UNNEST(GENERATE_ARRAY(0, 11)) n
)
SELECT 
  FORMAT_DATE('%Y%m%d', month_start) AS YYMMDD,
  FORMAT_DATE('%Y%m', month_start) AS YYMM,
  -- 两种周数计算逻辑二选一即可
  FORMAT_DATE('%G%V', month_start) AS iso_yyww, -- 方案1 ISO周结果
  FORMAT_DATE('%Y%W', month_start + INTERVAL MOD(9 - EXTRACT(DAYOFWEEK FROM month_start),7) DAY) AS month_first_monday_yyww -- 方案2 当月首个周一的周数结果
FROM months
ORDER BY month_start

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