Google BigQuery关联两数据集时子查询无法按年返回数据

嘿，我一眼就看出问题出在哪啦！你单独跑子查询时能按年份返回平均气温，但嵌入主查询后变成全局平均，核心原因是你的子查询没有和主查询的年份做关联，导致它计算的是所有年份的整体平均，而不是对应主查询每行的年份平均。

先看看你提供的代码片段（我补全了部分省略的内容以便理解）：

#standardSQL
SELECT 
  t1.year, 
  gas_fuel, 
  liquid_fuel, 
  solid_fuel, 
  cement, 
  annual_anomaly, 
  (SELECT avg(temp) FROM `bigquery-public-data.noaa_gsod.gsod*` WHERE year >= 1990 AND year <= 2010) AS avg_temp
FROM 
  `your_main_dataset.your_main_table` t1
WHERE 
  t1.year >= 1990 AND t1.year <= 2010

问题分析 #

上面的子查询没有和主查询的t1.year建立关联，所以不管主查询的行是哪一年，子查询都会返回1990-2010所有年份的气温平均值，自然就没法按年返回了。

修正后的相关子查询写法 #

你需要在子查询的WHERE条件里加上year = t1.year，让子查询针对主查询每行的年份单独计算平均值：

#standardSQL
SELECT 
  t1.year, 
  gas_fuel, 
  liquid_fuel, 
  solid_fuel, 
  cement, 
  annual_anomaly, 
  -- 子查询关联主查询的年份，计算对应年份的平均气温
  (SELECT avg(temp) 
   FROM `bigquery-public-data.noaa_gsod.gsod*` 
   WHERE year = t1.year  -- 关键：关联主查询的year字段
     AND year >= 1990 AND year <= 2010) AS annual_avg_temp
FROM 
  `your_main_dataset.your_main_table` t1
WHERE 
  t1.year >= 1990 AND t1.year <= 2010

更高效的JOIN写法（推荐） #

如果数据量较大，相关子查询可能性能一般，你也可以用CTE预计算出每年的平均气温，再和主表关联，这种写法更清晰且性能更优：

#standardSQL
WITH annual_temp_avg AS (
  SELECT 
    year, 
    avg(temp) AS annual_avg_temp
  FROM `bigquery-public-data.noaa_gsod.gsod*`
  WHERE year >= 1990 AND year <= 2010
  GROUP BY year
)
SELECT 
  t1.year, 
  gas_fuel, 
  liquid_fuel, 
  solid_fuel, 
  cement, 
  annual_anomaly, 
  ata.annual_avg_temp
FROM 
  `your_main_dataset.your_main_table` t1
JOIN 
  annual_temp_avg ata ON t1.year = ata.year
WHERE 
  t1.year >= 1990 AND t1.year <= 2010

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