Extract AVG Temp by months with BigQuery?

Question

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

This is what I wrote to get what I need from a single table, 2015. The data received 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 this query does retrieve the information I need, how can I rewrite it to include the entire range (1929 - 2016)?

Answer 1

You should use a lookup table for this as shown 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