如何在BigQuery中将复活节周日计算逻辑转为CTE批量计算多年份

直接用CTE实现批量计算，核心是把原脚本里的循环转换成纯数学运算，避免变量和循环，适配批量年份需求：

WITH years AS (
  -- 生成1990到2099的所有年份
  SELECT year
  FROM UNNEST(GENERATE_ARRAY(1990, 2099)) AS year
),
step1 AS (
  -- 计算初始D值
  SELECT year, 225 - 11 * MOD(year, 19) AS initial_D
  FROM years
),
step2 AS (
  -- 替换循环：将D降到50以下（等价于初始D>50时取模30）
  SELECT year, IF(initial_D > 50, MOD(initial_D, 30), initial_D) AS D
  FROM step1
),
step3 AS (
  -- 若D>48则减1
  SELECT year, IF(D > 48, D - 1, D) AS D
  FROM step2
),
step4 AS (
  -- 计算E值
  SELECT year, D, MOD(year + FLOOR(year / 4) + D + 1, 7) AS E
  FROM step3
),
step5 AS (
  -- 计算Q值
  SELECT year, D, E, D + 7 - E AS Q
  FROM step4
),
easter_dates AS (
  -- 生成复活节日期
  SELECT
    year,
    CASE
      WHEN Q < 32 THEN DATE(year, 3, Q)
      ELSE DATE(year, 4, Q - 31)
    END AS easter_sunday
  FROM step5
)
-- 输出结果，可直接替换为和现有日期表关联的逻辑
SELECT * FROM easter_dates;

关键逻辑说明 #

• 年份批量生成：用GENERATE_ARRAY(1990, 2099)生成目标年份范围，再通过UNNEST转换成行级数据，实现批量处理。
• 循环替换：原脚本的WHILE循环本质是将D值减到50以下，等价于对初始D值取30的模（仅当初始D>50时），完全用数学运算替代循环逻辑。
• 分层CTE：把计算拆成多个步骤CTE，可读性更强，后续维护修改更方便。

和现有日期表关联示例 #

假设你的日期表名为date_table，包含date字段，可通过LEFT JOIN标记复活节：

WITH -- 上述CTE部分省略，直接复用即可
easter_dates AS (
  SELECT year, CASE WHEN Q <32 THEN DATE(year,3,Q) ELSE DATE(year,4,Q-31) END AS easter_sunday FROM step5
)
SELECT
  dt.date,
  CASE WHEN ed.easter_sunday = dt.date THEN '复活节周日' ELSE NULL END AS holiday_name
FROM date_table dt
LEFT JOIN easter_dates ed ON dt.date = ed.easter_sunday;

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