如何在Big Query中获取任意年份11月第四个周五的日期

核心思路 #

11月的第四个周五必然落在当月的22日至28日区间内（第一个周五最早为1日，第四个即为22日；第一个周五最晚为7日，第四个即为28日）。基于这个规律，可以通过以下几种方式快速计算：

方法一：简洁单行查询（单个年份） #

直接以11月22日为基准，调整到最近的周五：

SELECT
  DATE_ADD(
    DATE('2024-11-22'), -- 替换为目标年份的11月22日，如DATE('2025-11-22')
    INTERVAL (6 - EXTRACT(DAYOFWEEK FROM DATE('2024-11-22'))) % 7 DAY
  ) AS fourth_friday_november;

逻辑说明：

• BigQuery中EXTRACT(DAYOFWEEK FROM date)返回1（周日）到7（周六）的数值
• (6 - DAYOFWEEK) % 7计算出从22日到目标周五需要添加的天数：
  • 若22日是周五，结果为0，直接返回22日
  • 若22日是周六，结果为6，添加6天后得到28日（当月第四个周五）
  • 其他日期则自动计算到周五的间隔天数

方法二：批量处理多个年份 #

如果需要一次性计算多个年份的结果，可以结合GENERATE_ARRAY生成年份列表：

WITH target_years AS (
  SELECT year
  FROM UNNEST(GENERATE_ARRAY(2020, 2030)) AS year -- 替换为需要的年份范围
),
november_22nd AS (
  SELECT DATE(year, 11, 22) AS base_date, year
  FROM target_years
)
SELECT
  year,
  DATE_ADD(
    base_date,
    INTERVAL (6 - EXTRACT(DAYOFWEEK FROM base_date)) % 7 DAY
  ) AS fourth_friday_november
FROM november_22nd
ORDER BY year;

方法三：从11月1日推导（直观逻辑） #

先找到11月的第一个周五，再加上21天（3周）得到第四个周五：

-- 单个年份示例
WITH november_first AS (
  SELECT DATE('2024-11-01') AS first_day
),
first_friday AS (
  SELECT
    first_day + INTERVAL 
      CASE 
        WHEN EXTRACT(DAYOFWEEK FROM first_day) = 1 THEN 5 -- 1号是周日，加5天到周五
        ELSE 6 - EXTRACT(DAYOFWEEK FROM first_day) + 1 -- 其他日期计算到周五的天数
      END DAY AS first_fri_date
  FROM november_first
)
SELECT first_fri_date + INTERVAL 21 DAY AS fourth_friday_november
FROM first_friday;

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