📐

BigQuery で PIVOT演算子を使う

2022/12/05に公開

BigQuery

tech

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

オフィシャルのドキュメントはこちら

やりたいこと

こんな感じの 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

できたー