BigQuery报错：表stations需指定数据集，求SQL代码修正方案

报错原因是主查询中引用stations表时没有指定完整的数据集路径，BigQuery要求必须用[项目名].[数据集名].[表名]的格式来引用公共数据集里的表。同时我们可以优化查询逻辑，避免重复关联站点表，提升效率：

WITH weather_data AS (
  SELECT
    s.station_name,
    s.country AS station_country,
    w.usaf AS station_usaf,
    w.wban AS station_wban,
    w.date AS observation_date,
    w.prcp AS precipitation,
    w.temp AS temperature,
    LAG(w.prcp) OVER (PARTITION BY w.usaf, w.wban ORDER BY w.date) AS prev_day_prcp,
    AVG(w.prcp) OVER (PARTITION BY w.usaf, w.wban ORDER BY w.date ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING) AS avg_prcp_last_week,
    AVG(w.temp) OVER (PARTITION BY w.usaf, w.wban ORDER BY w.date ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS avg_temp_last_4_days
  FROM `bigquery-public-data.noaa_gsod.gsod2000` w
  JOIN `bigquery-public-data.noaa_gsod.stations` s
    ON w.stn = s.usaf AND w.wban = s.wban
  WHERE s.country = 'PL' -- 提前过滤波兰站点，避免无效计算
)
SELECT 
  station_name,
  station_country,
  COUNT(*) AS number_of_occurrences
FROM weather_data
WHERE
  prev_day_prcp = 0 
  AND precipitation > avg_prcp_last_week 
  AND temperature < avg_temp_last_4_days - 5 
GROUP BY
  station_name,
  station_country
ORDER BY number_of_occurrences DESC; -- 直接按统计数降序，无需ROW_NUMBER()

主要修改点： #

• 把主查询中未限定的stations表替换为完整路径bigquery-public-data.noaa_gsod.stations，同时将站点信息（名称、国家）提前加入CTE，避免主查询重复关联表
• 将CTE中的LEFT JOIN改为JOIN，因为我们只需要波兰的站点，LEFT JOIN会保留无匹配的气象数据，这里不需要
• 移除了ORDER BY中多余的ROW_NUMBER()，直接按统计结果降序排序即可

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