【BigQuery演習】気象データをPIVOTで集計してみよう

f:id:true-fly:20210811234115p:plain

おはようございます。本日のBigQuery演習のテーマはこちら！

SQLで日付毎の気象データから月毎に集計したピボットを作成しよう

BigQueryも今年になってPIVOT演算子が実装されました。ピボットというと、Excelやスプレッドシートで馴染み深いものですが、これがSQLのみで実行できるようになりました。この機能を試して使い方を理解していただくのが、本日の演習の目的になります！

BigQueryには、一般公開データセットという、自前で用意しなくても利用できるデータがあります。ここから日付ごとのデータを取得し、その日付データを、BigQueryのPIVOT演算子を用いて集計してみましょう。一般公開データセットの利用方法はこちら！

www.true-fly.com

今回は一般公開データセットのうち、NOAAが提供する地域ごと、日付毎の気象データを用いてみようと思います！

ルール

以下のSQLをベースに、BigQueryの公開データである、bigquery-public-data.noaa_gsod.gsod2020と、bigquery-public-data.noaa_gsod.stations を参照すること
出力する地域は、札幌、新潟、東京、京都、高松、北九州、那覇のみに絞り込みすること
上記都市の、2020年の月ごとの平均気温を出力すること
平均気温は1月～12月まで1列ずつ列として出力すること
月を列で出力する時は、PIVOT演算子を用いること

SELECT   
    st.name,  -- 都市名
    gs.date,
    TRUNC((5 / 9) * (gs.temp - 32), 1) AS Celsius, -- 平均気温(摂氏)
FROM
    -- 2020年の気象情報
    `bigquery-public-data.noaa_gsod.gsod2020` gs
    -- 観測地域マスタ
    INNER JOIN `bigquery-public-data.noaa_gsod.stations` st
        ON gs.stn   = st.usaf
        AND st.country = 'JA'
        AND st.usaf != '999999'
        AND st.name IN ('SAPPORO', 'NIIGATA', 'TOKYO', 'KYOTO', 'TAKAMATSU', 'KITAKYUSHU', 'NAHA')

最終的な出力イメージ

f:id:true-fly:20210811231921p:plain

集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析できるDigital Camp

作者:木田和廣
インプレス

Amazon

答え

WITH data AS (
    SELECT   
        st.name,  -- 都市名
        FORMAT_DATE('%Y%m', gs.date) AS month,
        TRUNC(AVG((5 / 9) * (gs.temp - 32)), 1) AS Celsius, -- 平均気温(摂氏)
    FROM
        -- 2020年の気象情報
        `bigquery-public-data.noaa_gsod.gsod2020` gs
        -- 観測地域マスタ
        INNER JOIN `bigquery-public-data.noaa_gsod.stations` st
            ON gs.stn   = st.usaf
            AND st.country = 'JA'
            AND st.usaf != '999999'
            AND st.name IN ('SAPPORO', 'NIIGATA', 'TOKYO', 'KYOTO', 'TAKAMATSU', 'KITAKYUSHU', 'NAHA')
    GROUP BY
        1,2
)
SELECT
    *
FROM
    data
    PIVOT(
        AVG(Celsius) AS Celsius_in
        FOR month
        IN (
            '202001' AS January,
            '202002' AS February,
            '202003' AS March,
            '202004' AS April,
            '202005' AS May,
            '202006' AS June,
            '202007' AS July,
            '202008' AS August,
            '202009' AS September,
            '202010' AS October,
            '202011' AS November,
            '202012' AS December
        )
    )

解説

まず地域の月別の平均気温をWITH句内で計算しています。元々のデータはDATE型なので、月ごとにするために、FORMAT_DATE('%Y%m', 日付カラム)で、YYYYMMのSTRING型に変換しています。月に丸めるといえば、DATE_TRUNC(日付カラム, MONTH)が思いつきますが、こちらは「2020-03-01」のように、○月1日のDATE型として出力されるため、今回はSTRINGに変換するようにしてみました。
WITH句で出したデータは以下のようになります。

f:id:true-fly:20210811232629p:plain