📐

BigQuery で PIVOT演算子を使う

2022/12/05に公開約1,400字

BigQuery で PIVOT演算子を使ってみたので、そのメモです

オフィシャルのドキュメントはこちら
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#pivot_operator

やりたいこと

こんな感じの users テーブルがあったとして

input

id name pref gender
1 やまだたろう 北海道
2 さとうはなこ 東京都
3 たなかゆうこ 沖縄県

都道府県ごとに、各性別のユーザが何人いるかをカウントして、表にしたい
具体的にほしい出力は

output

pref male female
東京都 23 36
北海道 10 8
沖縄県 null 4

のような、都道府県名と各性別の人数が1レコードにまとめられている形式。

group by と count で数を数える

都道府県と性別で group by して、count でまずはレコード数を数える

先述の users テーブルに対して以下のような SQL を発行

select pref, gender, count(id) as cnt
from `users`
group by pref, gender

クエリ結果はこんな感じ

pref gender cnt
北海道 10
北海道 9
東京都 23
東京都 36
沖縄県 4

都道府県と性別ごとに、レコード数を計算できた

PIVOT で表を整形する

PIVOT は from に続けて記述する。ドキュメントにも

PIVOT は FROM 句の一部です。

とある。

これで性別のカウント数を1レコードにまとめてみる。

select pref, male, female
from *****
pivot(
  sum(cnt)
  for gender
  in('男' as male, '女' as female)
)

この例では、

  • cnt の値を集計対象とする
  • for gender で、genderの値に対応した列を作成すると定義
  • in(...) で、gender の値に応じてどんな列名を作るかを定義する

このPIVOT演算子を、先程の group by するクエリ結果に対して適用する

select pref, male, female
from (
  select pref, gender, count(id) as cnt
  from `users`
  group by pref, gender
)
pivot(
  sum(cnt)
  for gender
  in('男' as male, '女' as female)
)

最終結果

pref male female
東京都 23 36
北海道 10 8
沖縄県 null 4

できたー

Discussion

ログインするとコメントできます