代码之家 › 专栏 › 技术社区 › iTurki

使用BigQuery?按月检索平均温度?

weather google-bigquery sql

1

iTurki · 技术社区 · 10 年前

使用 fh-bigquery:weather_gsod 数据集,我想检索特定国家所有站点的一些月度天气数据。也就是说,我想要1929年至今的月平均温度、月平均最大值和月平均最小值。

这是我为从2015年的一张表中检索所需内容而写的。我得到的数据似乎是正确的:

SELECT stn, FIRST(name) AS station_name, mo, (AVG(temp)-32)*0.5556 AS temp, (AVG(max)-32)*0.5556 AS max, (AVG(min)-32)*0.5556 AS min
FROM [fh-bigquery:weather_gsod.gsod2015] gsod
JOIN [fh-bigquery:weather_gsod.stations2] stations
ON gsod.wban=stations.wban AND gsod.stn=stations.usaf
WHERE country='SA' 
GROUP BY stn, mo
ORDER BY mo

假设这个查询确实检索到了我需要的信息,我如何重写它,以便能够包括整个范围(1929年到2016年)?

1 回复 | 直到 10 年前

1

5

Mikhail Berlyant 10 年前

你应该使用 Table wildcard functions 如下所示

SELECT 
  stn, 
  FIRST(name) AS station_name, 
  mo, 
  (AVG(temp)-32)*0.5556 AS temp, 
  (AVG(max)-32)*0.5556 AS max, 
  (AVG(min)-32)*0.5556 AS min
FROM (
  SELECT * FROM
  (TABLE_QUERY([fh-bigquery:weather_gsod], 'table_id CONTAINS "gsod"')) 
) gsod
JOIN [fh-bigquery:weather_gsod.stations2] stations
ON gsod.wban=stations.wban AND gsod.stn=stations.usaf
WHERE country='SA' 
GROUP BY stn, mo
ORDER BY mo

推荐文章

jdiction · python在3 x n数组中最常见的索引对