Zenn
💡

BigQueryでpivotを使う

2021/07/24に公開
BigQuery
tech

サンプルデータ

COVID-19（新型コロナウイルス感染症）研究用データセット を用いる。
bigquery-public-data.covid19_ecdc_eu.covid_19_geographic_distribution_worldwide では、日付・国ごとの感染者確認数・死亡者数が見れる。

pivotを使わない

例えば日本・アメリカ・中国の感染者数を月ごとに出すと、以下のように書ける。

WITH confirmed AS (
  SELECT month, country_territory_code, SUM(daily_confirmed_cases) AS confirmed
    FROM `bigquery-public-data.covid19_ecdc_eu.covid_19_geographic_distribution_worldwide` 
    WHERE year = 2020
    GROUP BY month, country_territory_code
), jpn AS (
    SELECT * FROM confirmed WHERE country_territory_code = "JPN"
), usa AS (
    SELECT * FROM confirmed WHERE country_territory_code = "USA"
), chn AS (
    SELECT * FROM confirmed WHERE country_territory_code = "CHN"
) 

SELECT month, jpn.confirmed AS jpn, usa.confirmed AS usa, chn.confirmed AS chn FROM (
  SELECT DISTINCT month FROM confirmed 
)
  INNER JOIN jpn USING(month)
  INNER JOIN usa USING(month)
  INNER JOIN chn USING(month)
  ORDER BY month

国ごとに集計したものをJOINする必要がある。

pivotを使う

pivotを使うと、以下のように書ける。

SELECT month, JPN, USA, CHN FROM (
  SELECT month, country_territory_code, daily_confirmed_cases
    FROM `bigquery-public-data.covid19_ecdc_eu.covid_19_geographic_distribution_worldwide` 
    WHERE year = 2020
) PIVOT (SUM(daily_confirmed_cases) FOR country_territory_code IN ("JPN", "USA", "CHN"))

FOR で指定した country_territory_code の値 ( JPN , USA, CHN )がそのままカラム名として指定できるようになり、それぞれを FOR の前で指定した集計関数で集計できるようになる。

tips

以下pivot使う上でのtips。

FOR で指定したい値が数値の場合

例えば月ごとの数値を横持ちで見たい場合、カラム名として数値を指定するとその数値が出るだけでカラム名と判定されない。
頭に _ をつけてバッククオートでくくる。

SELECT country_territory_code, `_1`, `_6`, `_12` FROM (
  SELECT country_territory_code, month AS ym, daily_confirmed_cases
    FROM `bigquery-public-data.covid19_ecdc_eu.covid_19_geographic_distribution_worldwide` 
    WHERE country_territory_code IN ("JPN", "USA", "CHN") AND year = 2020
) PIVOT (SUM(daily_confirmed_cases) FOR ym IN (1, 6, 12))

または、エイリアスをつける

SELECT country_territory_code, JAN, JUN, DEC FROM (
  SELECT country_territory_code, month AS ym, daily_confirmed_cases
    FROM `bigquery-public-data.covid19_ecdc_eu.covid_19_geographic_distribution_worldwide` 
    WHERE country_territory_code IN ("JPN", "USA", "CHN") AND year = 2020
) PIVOT (SUM(daily_confirmed_cases) FOR ym IN (1 AS JAN, 6 AS JUN, 12 AS DEC))

2つのカラムを使ってpivotしたい

上の例でさらに年・月毎に見たい場合。
2つのカラムから新たにカラムを作ってそれでもってpivotする。

SELECT country_territory_code, `_2019_12`, `_2020_1`, `_2020_12` FROM (
  SELECT country_territory_code, CONCAT("_", year, "_", month) AS ym, daily_confirmed_cases
    FROM `bigquery-public-data.covid19_ecdc_eu.covid_19_geographic_distribution_worldwide` 
    WHERE country_territory_code IN ("JPN", "USA", "CHN")
) PIVOT (SUM(daily_confirmed_cases) FOR ym IN ("_2019_12", "_2020_1", "_2020_12"))

Discussion