如何在Google BigQuery中实现按双周分组？

BigQuery的DATE_TRUNC确实没有直接的双周（bi-week）参数，不过可以通过以下几种灵活方式实现需求：

方案1：以自然周为基础分组合并 #

核心逻辑是将周数按奇偶分组，把连续两周合并为一个双周周期，以每年第一周作为第一个双周的起始点：

WITH sample_data AS (
  SELECT DATE('2024-01-01') AS event_date UNION ALL
  SELECT DATE('2024-01-08') UNION ALL
  SELECT DATE('2024-01-15') UNION ALL
  SELECT DATE('2024-01-22')
)
SELECT
  event_date,
  -- 计算双周起始日期：当前周为奇数周则取本周起始，偶数周则取上一周起始
  DATE_SUB(
    DATE_TRUNC(event_date, WEEK),
    INTERVAL (EXTRACT(WEEK FROM event_date) % 2) WEEK
  ) AS biweek_start
FROM sample_data;

注：可通过WEEK(MONDAY)调整一周的起始日（默认是周日）

方案2：自定义固定起始日的双周周期 #

如果业务有明确的双周起始规则（比如每月1日和16日，或某个固定日期作为周期起点），直接通过日期差计算分组：

WITH sample_data AS (
  SELECT DATE('2024-01-01') AS event_date UNION ALL
  SELECT DATE('2024-01-10') UNION ALL
  SELECT DATE('2024-01-17') UNION ALL
  SELECT DATE('2024-02-01')
)
SELECT
  event_date,
  -- 自定义起始日为2024-01-01，每14天为一个双周周期
  DATE_ADD(
    DATE('2024-01-01'),
    INTERVAL (DATE_DIFF(event_date, DATE('2024-01-01'), DAY) DIV 14) * 14 DAY
  ) AS biweek_start
FROM sample_data;

这个方案完全贴合业务自定义周期，不受自然年周数限制。

方案3：基于ISO周规则的双周截断 #

如果需要遵循ISO周标准（周一为一周起始，每年至少包含52周），可以用ISO年和周数计算：

WITH sample_data AS (
  SELECT DATE('2023-12-25') AS event_date UNION ALL
  SELECT DATE('2024-01-05') UNION ALL
  SELECT DATE('2024-01-12')
)
SELECT
  event_date,
  -- 计算ISO规则下的双周起始日期
  DATE_TRUNC(
    DATE_ADD(DATE('0001-01-01'), INTERVAL (EXTRACT(ISOYEAR FROM event_date) - 1) YEAR),
    WEEK(MONDAY)
  ) + INTERVAL ((EXTRACT(ISOWEEK FROM event_date) - 1) DIV 2) * 14 DAY AS biweek_start
FROM sample_data;

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