如何在BigQuery中原生实现多表循环查询？无需依赖Python

完全可以用BigQuery原生SQL实现，不需要依赖Python循环。核心思路是利用表后缀通配符（_TABLE_SUFFIX）一次性匹配1990-2022年的所有分年度表，再在查询逻辑中统一处理各年份的劳动节日期计算。

以下是等价的BigQuery原生SQL：

WITH all_years_data AS (
  SELECT 
    id,
    date,
    element,
    value,
    EXTRACT(YEAR FROM date) AS yearstring,
    EXTRACT(DAYOFYEAR FROM date) AS dayofyear
  FROM `bigquery-public-data.ghcn_d.ghcnd_*`
  WHERE 
    _TABLE_SUFFIX BETWEEN '1990' AND '2022'
    AND id = 'USC00264527'
    AND element IN ('TMAX', 'TMIN', 'PRCP')
),
labor_days AS (
  SELECT 
    yearstring,
    MIN(date) AS labor_day_date
  FROM all_years_data
  WHERE 
    EXTRACT(MONTH FROM date) = 9 
    AND EXTRACT(DAYOFWEEK FROM date) = 2 -- BigQuery中DAYOFWEEK：1=周日，2=周一，对应劳动节（9月第一个周一）
  GROUP BY yearstring
),
event_dates AS (
  SELECT 
    a.*,
    DENSE_RANK() OVER (PARTITION BY a.yearstring ORDER BY a.date ASC) AS day
  FROM all_years_data a
  JOIN labor_days b 
    ON a.yearstring = b.yearstring
    AND a.date BETWEEN DATE_SUB(b.labor_day_date, INTERVAL 8 DAY) AND b.labor_day_date
)
SELECT 
  id,
  date,
  element,
  CASE 
    WHEN element IN ('TMAX', 'TMIN') THEN (value/10 * 1.8) + 32 -- 摄氏度转华氏度
    WHEN element = 'PRCP' THEN value/10 -- 0.1毫米转毫米
  END AS value,
  dayofyear,
  yearstring,
  day
FROM event_dates
ORDER BY yearstring, date ASC;

关键细节说明： #

• 表通配符简化查询：ghcnd_*匹配所有年度分表，_TABLE_SUFFIX筛选1990-2022年的数据，避免了逐个年份循环查询的繁琐。
• 统一计算年度劳动节：通过按年份分组，找到每年9月的第一个周一（劳动节），再关联筛选出活动对应的9天日期范围。
• 性能与逻辑等价：BigQuery会并行处理所有匹配的表，比Python循环多次发起查询效率更高；最终输出的字段、计算逻辑和原Python方案完全一致。

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