SELECT
name,
value/10 AS min_temperature,
latitude,
longitude
FROM
[bigquery-public-data:ghcn_d.ghcnd_stations] AS stn
JOIN
[bigquery-public-data:ghcn_d.ghcnd_2016] AS wx
ON
wx.id = stn.id
WHERE
wx.element = 'TMIN'
AND wx.qflag IS NULL
AND STRING(wx.date) = '2016-08-15'
上のクエリからは次の結果が得られます。
Google Cloud Datalab で観測所の位置をプロットすると、観測所の密度は北米やヨーロッパ、日本では非常に良好で、アジアの大半の地域ではまずまずの状態だということがわかります。また、隙間のある地域の大半はオーストラリア内陸部やシベリア、北アフリカなどの人口過少地域と重なっており、大きな穴が空いているのはブラジルのみです。
SELECT
wx.date,
wx.value/10.0 AS prcp
FROM
[bigquery-public-data:ghcn_d.ghcnd_2015] AS wx
WHERE
id = 'USW00094846'
AND qflag IS NULL
AND element = 'PRCP'
ORDER BY wx.date
SELECT
wx.date,
MAX(prcp) AS prcp,
MAX(tmin) AS tmin,
MAX(tmax) AS tmax,
IF(MAX(haswx) = 'True', 'True', 'False') AS haswx
FROM (
SELECT
wx.date,
IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp,
IF (wx.element = 'TMIN', wx.value/10, NULL) AS tmin,
IF (wx.element = 'TMAX', wx.value/10, NULL) AS tmax,
IF (SUBSTR(wx.element, 0, 2) = 'WT', 'True', NULL) AS haswx
FROM
[bigquery-public-data:ghcn_d.ghcnd_2015] AS wx
WHERE
id = 'USW00094846'
AND qflag IS NULL )
GROUP BY
wx.date
ORDER BY
wx.date
SELECT
REPLACE(date,"-","") AS date,
MAX(prcp) AS prcp,
MAX(tmin) AS tmin,
MAX(tmax) AS tmax
FROM (
SELECT
STRING(wx.date) AS date,
IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp,
IF (wx.element = 'TMIN', wx.value/10, NULL) AS tmin,
IF (wx.element = 'TMAX', wx.value/10, NULL) AS tmax
FROM
[bigquery-public-data:ghcn_d.ghcnd_2016] AS wx
WHERE
id = 'USW00094846'
AND qflag IS NULL
AND value IS NOT NULL
AND DATEDIFF(CURRENT_DATE(), date) < 15 )
GROUP BY
date
ORDER BY
date ASC
SELECT
wx.date,
wx.prcp,
f.departure_delay,
f.arrival_airport
FROM (
SELECT
STRING(date) AS date,
value/10 AS prcp
FROM
[bigquery-public-data:ghcn_d.ghcnd_2005]
WHERE
id = 'USW00094846'
AND qflag IS NULL
AND element = 'PRCP') AS wx
JOIN
[bigquery-samples:airline_ontime_data.flights] AS f
ON
f.date = wx.date
WHERE
f.departure_airport = 'ORD'
LIMIT 100
