見出し画像
Photo by 4540_73

BigQuery ～Pivot編～

コグラフ株式会社 データアナリティクス事業部

はじめに

こんにちは！コグラフ株式会社データアナリティクス事業部の池田です。
今回はピボットをBQでしたので備忘録ということで書いていきたいと思います！pivot句を使う方法と使わない方法を書いていこうと思います🙌

ピボットとは？

ピボットってよく聞くけど、ちょっと想像しにくいですよね。(筆者自身もたまに分からなくなったりします。)
要は特定のカラムのデータを横持にするということです！今回は超簡単なデータテーブルを使って説明していきます。

実際にピボット前後のイメージを作成してみました。これでイメージを膨らませ、クエリに落とし込んでいきましょう！



Pivot で横持ちテーブルを作成

実際に横持したテーブルを作成するクエリを書いていきましょう！
上記の横持ちテーブルを作成していきたいと思います！

まずはピボットなし版

-- 横持ちテーブルを作成する
select
 user_id

 -- 各アイテムの個数を数えて新しいカラムを作成する
 , sum(case when item = 'りんご' then 1 else 0 end) as apple
 , sum(case when item = 'みかん' then 1 else 0 end) as orange
 , sum(case when item = 'もやし' then 1 else 0 end) as bean_sprouts
from
  `fortwitter.twitter_neta.item_table`
group by
  user_id

クエリ自体は単純で case when 文で対象の item を決めそれの個数を数えているだけです。

次に pivot を使って書いてみましょう！

-- 横持ちテーブルを作成する
select
  * 
from
  `fortwitter.twitter_neta.item_table`
pivot(
  count(price) -- 個数をカウントする
  for item in('りんご' as apple, 'みかん' as orange, 'もやし' as bean_sprouts) -- 横持のカラム名を入力
)

少しスッキリしましたね。
pivot の中身を簡単に説明します。（）内の1行目は集計関数を使い対象カラムの集計値を出します。（ここでは count() ）
2行目の for item in () では横持ちの対象となるカラムを指定して対象となるデータを書いています。※この時 () 内の値はカラム名になるため、日本語だとエラーになってしまいます。

どちらも下記の結果を取得できます！
上記で出したかったテーブルを同じになっていることが確認できますね！

クエリを比較すると、そこまで差がないように見えますが、pivot を使った方がメンテナンスがしやすくなると思うので筆者は pivot を推奨します。
例えば、新しいデータとして「ぶどう」が入ってきたとき、pivot なしのほうであれば、case 文を追加しないといけないですが、pivot は for 文の中にデータ（グレープ）を追加するだけです。
また、やっぱり個数じゃなくて、合計金額値を入れたいと思った時、 pivotは count() を sum() に変更するだけの修正で済みます。 

-- 集計を購入金額の合計にしたい。
select
  * 
from
  `fortwitter.twitter_neta.item_table`
pivot(
  sum(price) -- ここを変えるだけ
  for item in('りんご' as apple, 'みかん' as orange, 'もやし' as bean_sprouts) -- 横持のカラム名を入力
)

Unpivot で縦持ちテーブルを作成

ついでに unpivot も触ってみましょう。言葉通り pivot と逆のことをするので縦持ちのテーブルを作るということですね。
上記で作成したこのテーブルを縦持ちにしてみましょう！

これを縦持ちにする（合計購入金額カラムを作る）
-- 縦持ちデータを作成する
select
  * 
from
  `fortwitter.twitter_neta.pivot_table`
unpivot(
  total_price -- unpivotしたデータのカラムを作成する
  for item in (apple as "りんご", orange as "みかん", bean_sprouts as "もやし") -- 縦持ちにした時のカラム名とデータ名を決める
)

サクッとできましたね！簡単に説明していきます。
（）内の1行目は新しく作成するカラム名を決めています。この中に縦持ちにされたデータが入ってきます！
2行目では 新しいカラム名（上記では item ）とその中に値として入るカラムを書いています（例：apple as "りんご"）。
少し考えにくいかも知れませんが、一度書いてしまえば、すぐに理解できるかと思います！
Unpivotの方がもしかすると重宝するかもしれませんね🤔

さいごに

以上が、pivot の使い方です。少し癖があり、実際に書くと戸惑うかもしれませんが、自分の作成したいテーブルを一度イメージして取り組めば簡単にできるかと思います！かっこいいクエリを目指していきましょう！！

公式ドキュメント

Twitterもやってます！

コグラフデータ事業部ではTwitterから情報を発信しています。
データ分析に興味がある、データアナリストになりたい人など、ぜひフォローお願いします！


この記事が気に入ったらサポートをしてみませんか？