Zenn
🦬

BigQueryのPIVOT演算子について

に公開
SQL
Google Cloud
BigQuery
tech

PIVOT演算子

そもそも演算子とは？値やカラムに対して計算や比較、論理操作などを行う記号やキーワード（例：+, =, LIKE, AND, OR, BETWEEN など）のことを言います。
PIVOT演算子は、指定したカラムの値ごとに列を作り、行データを集約して横持ちテーブルに変換するSQLの機能です。シンプルに行の指定したカテゴリを列にして集計してくれます。

例

変換前テーブル

下記のようなテーブルがある

event_date event_name
2020-12-01 page_view
2020-12-01 page_view
2020-12-01 session_start
2020-12-01 scroll
2020-12-01 add_to_cart
2020-12-02 page_view
2020-12-02 first_visit
2020-12-02 purchase
2020-12-02 purchase
2020-12-03 session_start

PIVOTするSQL

sql
SELECT
  *
FROM (
  SELECT
    event_date,
    event_name
  FROM
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`
)
PIVOT (
  COUNT(*)
  FOR event_name IN (-- ここはevent_nameのみをdistinctして出力すると楽
    'page_view',
    'session_start',
    'first_visit',
    'scroll',
    'add_to_cart',
    'purchase'
  )
)
ORDER BY
  event_date

変換後テーブル（PIVOT後）

上記のSQLを利用すると、event_nameごとに集計され列として展開される。

event_date page_view session_start first_visit scroll add_to_cart purchase
2020-12-01 2 1 0 1 1 0
2020-12-02 1 0 1 0 0 2
2020-12-03 0 1 0 0 0 0

PIVOTの動作説明

  1. グループ化: event_dateでデータをグループ化
  2. 集計: 各event_nameの値ごとにCOUNT(*)を実行
  3. 列変換: event_nameの各値を列として展開
  4. NULL値処理: 該当するイベントがない場合は0として表示

UNPIVOTの方法

PIVOTされたテーブルを元の形式に戻すには、UNPIVOTを使用します。

sql
SELECT
  event_date,
  event_name,
  event_count
FROM (
  -- ここにPIVOTされたテーブルまたは上記のPIVOTクエリを配置
  SELECT
    event_date,
    page_view,
    session_start,
    first_visit,
    scroll,
    add_to_cart,
    purchase
  FROM pivot_result_table
)
UNPIVOT (
  event_count FOR event_name IN (
    page_view,
    session_start,
    first_visit,
    scroll,
    add_to_cart,
    purchase
  )
)
WHERE event_count > 0  -- 0の値を除外する場合
ORDER BY event_date, event_name

まとめ

SQLでやると面倒なPIVOTが簡単にできるので重宝してます。

参考URL

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator

Discussion