如何在BigQuery中查询每年日期可变的特定节假日（以美国劳动节为例）

问题分析 #

你之前用固定周数（36周）筛选的方式不可靠，因为美国劳动节（9月第一个周一）所在的ISO周数每年可能不同（有时是35周，有时是36周），必须通过日期计算动态获取每年的劳动节日期，再筛选目标时间段。

单年份查询方案（以1991年为例） #

以下SQL会先计算1991年的劳动节日期，再筛选该日期前7天到当天的所有记录：

WITH labor_day AS (
  SELECT 
    CASE
      -- 9月1日就是周一，直接取当天
      WHEN EXTRACT(DAYOFWEEK FROM DATE(1991,9,1)) = 2 THEN DATE(1991,9,1)
      -- 9月1日是周日，加1天到周一
      WHEN EXTRACT(DAYOFWEEK FROM DATE(1991,9,1)) = 1 THEN DATE_ADD(DATE(1991,9,1), INTERVAL 1 DAY)
      -- 9月1日是周二到周六，计算到下一个周一的天数
      ELSE DATE_ADD(DATE(1991,9,1), INTERVAL (9 - EXTRACT(DAYOFWEEK FROM DATE(1991,9,1))) DAY)
    END AS ld_date
)
SELECT * 
FROM `bigquery-public-data.ghcn_d.ghcnd_1991`
WHERE date BETWEEN DATE_SUB((SELECT ld_date FROM labor_day), INTERVAL 7 DAY) AND (SELECT ld_date FROM labor_day);

多年份通用查询方案 #

如果要处理包含多年数据的表（比如ghcnd_all），可以按年份批量计算每年的劳动节，再关联筛选：

WITH yearly_labor_day AS (
  SELECT 
    EXTRACT(YEAR FROM date) AS year,
    CASE
      WHEN EXTRACT(DAYOFWEEK FROM DATE(EXTRACT(YEAR FROM date),9,1)) = 2 THEN DATE(EXTRACT(YEAR FROM date),9,1)
      WHEN EXTRACT(DAYOFWEEK FROM DATE(EXTRACT(YEAR FROM date),9,1)) = 1 THEN DATE_ADD(DATE(EXTRACT(YEAR FROM date),9,1), INTERVAL 1 DAY)
      ELSE DATE_ADD(DATE(EXTRACT(YEAR FROM date),9,1), INTERVAL (9 - EXTRACT(DAYOFWEEK FROM DATE(EXTRACT(YEAR FROM date),9,1))) DAY)
    END AS ld_date
  FROM `bigquery-public-data.ghcn_d.ghcnd_all`
  GROUP BY year
)
SELECT d.*
FROM `bigquery-public-data.ghcn_d.ghcnd_all` d
JOIN yearly_labor_day yld ON EXTRACT(YEAR FROM d.date) = yld.year
WHERE d.date BETWEEN DATE_SUB(yld.ld_date, INTERVAL 7 DAY) AND yld.ld_date;

关键说明 #

BigQuery中EXTRACT(DAYOFWEEK FROM date)的返回值是1（周日）到7（周六），所以周一对应数值2，这是计算的核心依据。通过CASE分支覆盖9月1日为不同星期的情况，就能准确得到每年的劳动节日期。

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