使用BigQuery按月检索AVG Temp？ [英] Retrieve AVG Temp by Month using BigQuery?

问题描述

使用 fh-bigquery：weather_gsod 数据集，我想检索特定国家/地区所有电台的一些月度天气数据。也就是说，我想从1929年到现在每月的平均温度，每月平均最高值和每月平均最低值。

这是我写的从一张表中检索我需要的东西，我得到的数据似乎是正确的：

  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]台
 ON gsod.wban = stations.wban AND gsod.stn = stations.usaf 
 WHERE country ='SA'
 GROUP BY stn，mo 
 ORDER BY mo 
  

假设这个查询确实检索到我需要的信息，我该如何重写它，以便我可以包含整个范围（1929年至2016年）？ 解决方案

您应该使用表格通配符功能，如下所示

 选择
 stn，
 FIRST（名称）作为station_name，
 mo，
（AVG（temp） - 32）* 0.5556 AS温度，
（AVG（最大值）-32）* 0.5556最大值，
（AVG（最小值）-32）* 0.5556最小值
来自（
 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 
  




Using the fh-bigquery:weather_gsod dataset, I want to retrieve some monthly weather data for all stations in a specific country. Namely, I want the monthly avg temp, monthly avg max, and monthly avg min, from 1929 to present.

This what I wrote to retrieve what I need from one table, 2015. The data I get seems correct:

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

Assuming that this query does indeed retrieve the info I need, how can I rewrite it so that I can include the whole range (1929 to 2016)?

解决方案

You should use Table wildcard functions for this as in below

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

这篇关于使用BigQuery按月检索AVG Temp？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！