组合两个不同数据集时不返回数据的子查询

rkue9o1l  于 2021-06-25  发布在  Mysql
关注(0)|答案(2)|浏览(298)

我正在尝试将两个不同的数据集与googlebigquery上的一个子查询相结合。
当我单独使用子查询时,它运行并返回每年的总平均温度。但是,当我将查询合并为子查询时,它只返回所述期间的平均温度,而不是每年的平均温度。
我的代码:


# 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 > '1982') as average_temperature
FROM `cs779-term-project.CO2_FossilFuel.GlobalEmissions` as t1
JOIN `cs779-term-project.CO2_FossilFuel.SeaLevels` as t2
on t1.year = t2.year
Where t1.year > 1982
ORDER BY t1.year ASC;

另外,当我添加 GROUP BY year 在子查询中,它显示以下错误:
错误:标量子查询产生了多个元素
谢谢您!

cxfofazt

cxfofazt1#

如果您需要年平均温度,则需要使用子查询条件中查询的年份:

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) as average_temperature
FROM `cs779-term-project.CO2_FossilFuel.GlobalEmissions` as t1
JOIN `cs779-term-project.CO2_FossilFuel.SeaLevels` as t2
on t1.year = t2.year
Where t1.year > 1982
ORDER BY t1.year ASC;
z9ju0rcb

z9ju0rcb2#

这是我使用并运行的最终代码:


# standardSQL

SELECT t1.year, gas_fuel, liquid_fuel, solid_fuel, cement, annual_anomaly
, (SELECT avg(temp)
  FROM `bigquery-public-data.noaa_gsod.gsod*`
  WHERE CAST(year as INT64) = t1.year) as average_temperature
FROM `cs779-term-project.CO2_FossilFuel.GlobalEmissions` as t1
JOIN `cs779-term-project.CO2_FossilFuel.SeaLevels` as t2
on t1.year = t2.year
Where t1.year > 1982
ORDER BY t1.year ASC;

相关问题