如何用Google BigQuery SQL计算当前季度内的周数？

要得到某日期在其所属季度内的周数，核心是先确定季度起始日，再通过日期差计算周数，确保从1开始计数。

方法一：基于ISO周数计算 #

WITH date_context AS (
  SELECT
    -- 替换为目标日期，可用CURRENT_DATE()获取当前日期
    DATE('2022-10-06') AS target_date,
    -- 获取目标日期所在季度的第一天
    DATE_TRUNC(DATE('2022-10-06'), QUARTER) AS quarter_start
)
SELECT
  target_date,
  quarter_start,
  -- 周数差+1，使起始周为第1周
  EXTRACT(WEEK FROM target_date) - EXTRACT(WEEK FROM quarter_start) + 1 AS quarter_week_number
FROM date_context;

方法二：基于天数划分（避免跨年周偏差） #

如果季度起始日属于上一年的周，方法一可能出现偏差，此时用按7天为一周的划分方式更可靠：

WITH date_context AS (
  SELECT
    DATE('2022-10-06') AS target_date,
    DATE_TRUNC(DATE('2022-10-06'), QUARTER) AS quarter_start
)
SELECT
  target_date,
  quarter_start,
  -- 按季度内天数计算，向上取整得到周数，起始日所在周为第1周
  CEIL((DATE_DIFF(target_date, quarter_start, DAY) + 1) / 7) AS quarter_week_number
FROM date_context;

示例结果 #

对于2022-10-06，两种方法都会返回1，符合需求：该日期属于Q4，且是季度内第1周的日期。

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