BigQuery超入門③

はじめに

こちらは BigQuery 超入門 ② の続きになります。

学ぶこと

• サブクエリについて
• データ構造の変換
• WITH とサブクエリの違いについて

サブクエリ（副問い合わせ）

仮想テーブルを作成する一つの方法。一つの SQL 文の中で別の SQL 文を利用する。

CTE とは違い、後続で使い回すようなものではないので上手に使い分けられるようにする。
（もし使い回すようなら何回も SELECT を書かないといけなくなるため）

WITH 句とサブクエリは次のよう記述する。
サブクエリは使い回すものではなく最初に CTE として定義する必要がないため記述量は減る。

WITH 句 Ver

WITH qty_avg AS (
    SELECT
        AVG(qty) AS total_avg_qty
    FROM
        project_id.dataset.products
)

SELECT
    *
FROM
    project_id.dataset.products AS p CROSS JOIN total_avg
WHERE
    qty > total_avg_qty

サブクエリ Ver

SELECT
    *
FROM
    project_id.dataset.products
WHERE
    qty > (SELECT AVG(qty) AS total_avg_qty FROM project_id.dataset.products)

サブクエリのイメージとしては、メインのクエリで必要とする部分的な結果を返すクエリ。

一つの値を返す

サブクエリで一つの値を返す処理を確認する。
特に集計や絞り込みでよく使うことが多い。

新しいフィールドの作成
次のように都道府県毎の最低賃金を返すテーブルに対して「平均賃金」という新しいフィールドを作成することで実現できる。

SELECT
    *, (SELECT AVG(min_wage) FROM project_id.dataset.prefectures) AS min_wage
FROM
    project_id.dataset.prefectures

サブクエリの結果を演算で使う
次のように平均賃金を返すサブクエリの結果を使って各県の最低賃金との差を調べる。

SELECT
    *, min_wage - s(SELECT AVG(min_wage) FROM project_id.dataset.prefectures) AS min_wage
FROM
    project_id.dataset.prefectures

一列 n 行を返す

サブクエリで一列で複数行を返すようにし、それをメインクエリで利用することもある。業務でも使うことが多い。
クエリのコメントにもあるように実際にどのような値が入るかわからないような箇所に対して動的な表現をしたい時に使える。
例えば次のように IN 句を使う場合。

// 1. 平均賃金を取得
// SELECT AVG(min_wage) FROM project_id.dataset.prefectures

// 2. 最低賃金が平均賃金を超えている県を取得
// SELECT DISTINCT pref FROM project_id.dataset.prefectures WHERE min_wage > (SELECT AVG(min_wage) FROM project_id.dataset.prefectures)
// 2のイメージ（数字がクエリに置き換わっている）
// SELECT DISTINCT pref FROM project_id.dataset.prefectures WHERE min_wage > 995.33...

// 3. prefフィールドの県名がいずれかの「最低賃金が平均賃金を超えている県」に含まれているかどうかを絞り込む
// 3のイメージ（東京,千葉がクエリに置き換わっている）
// SELECT * FROM project_id.dataset.prefectures WHERE pref IN ("東京","千葉")

SELECT
    *
FROM
    project_id.dataset.prefectures
WHERE
    pref IN (SELECT DISTINCT pref FROM project_id.dataset.prefectures WHERE min_wage > (SELECT AVG(min_wage) FROM project_id.dataset.prefectures))

m 列 n 行を返す

サブクエリで通常のテーブルのように m 列 n 行の結果テーブルを返すパターン。
「集計の集計」をしたいときによく使われることが多い。
例えば次のように「最低賃金が 900 円を超えている都道府県数とそれらの平均最低賃金」を取得したい場合。
まずは絞り込みで「最低賃金が 900 円を超えている都道府県」を取得する。そして結果テーブルを元に「都道府県数」「平均最低賃金」を集計する。

// 1. 最低賃金が900円を超える都道府県を返す（m列n行を返すサブクエリ）
// SELECT * FROM project_id.dataset.prefectures WHERE r5_min_wage > 900
// 2. 1の結果テーブルを元に集計する

SELECT
    COUNT(pref) AS count_900_over_pref, AVG(r5_min_wage) AS avg_min_wage
FROM
    (SELECT * FROM project_id.dataset.prefectures WHERE r5_min_wage > 900)

WITH とサブクエリの違いについて

WITH 句とサブクエリの違いについてまとめてみる。

可読性

• WITH 句: CTE 名を見るだけである程度どのような結果を返すのかが理解できる。一度定義すると以降は再利用しやすい
• サブクエリ: 直接クエリ内に記述するためサブクエリが長くなるほど読みづらくなりどういう結果テーブルを返すのかを理解するのに時間がかかる。再利用するような場合は毎回同じクエリを書くことになり冗長になりやすい

パフォーマンス

• WITH 句: クエリ内ではメモリに保存されるらしい。再利用する場合はメモリから呼び出すので、問い合わせしてデータを作成する必要がない
• サブクエリ: 実行するたびに問い合わせしてデータ作成をするためクエリによっては時間がかかってしまう

データ構造の変換

データには「縦持ち」「横持ち」という持ち方が存在しているらしい。

縦持ちのテーブル

pref	year	min_wage
東京	r3	1013
東京	r4	1041
東京	r5	1072
大阪	r3	964
大阪	r4	992
大阪	r5	1023
福岡	r3	842
福岡	r4	870
福岡	r5	901

横持ちのテーブル

pref	r3_min_wage	r4_min_wage	r5_min_wage
東京	1013	1041	1072
大阪	964	992	1023
福岡	842	870	901

縦持ちと横持ちの違い

データの見え方

横持ちのテーブルは各行が一つの項目を表し（最低賃金）、各列が項目の特性を表しているイメージ。
縦持ちテーブルの場合は各行が一つの事実を表す（東京都の令和３年の最低賃金）イメージ。

縦持ちテーブルの方が集計がしやすい

例えば平均の最低賃金を求める場合
縦持ちだと AVG を使って求めることができるが、横持ちの場合は （r3_min_wage+r4_min_wage+r5_min_wage）/3 のような計算をする必要がある。

一方で横持ちの場合は「令和４年の平均賃金に対する令和 5 年の伸び率」のような「フィールド同士の計算」が得意。
縦持ちでやろうとするとフィールド同士で比較するためにセルフジョインやサブクエリを使う必要がある。

データ追加時の挙動
例えば令和６年のデータが増えた時
縦持ちの場合はレコードが追加されるためテーブルが縦に伸びていくが、横持ちの場合はデータの追加とともにフィールドが横に伸びていく。

横持ちテーブルを縦持ちテーブルに変換する

order_id 毎で商品カテゴリ別の売り上げ金額を表している横持ちテーブルを縦持ちテーブルに変換してみる。

order_id	fashion	zakka	food
123	18600	null	5800
124	null	2400	8800
125	6900	2900	11200
126	4200	3800	4500
127	null	9800	null

SELECT
    order_id, "fashion" AS product_category, fashion AS revenue
FROM
    project_id.dataset.sales

次のように fashion を縦持ちテーブルに変換できる。

order_id	product_category	fashion
123	fashion	18600
124	fashion	null
125	fashion	6900
126	fashion	4200
127	fashion	null

UNION ALL で繋げることで他の商品も含めて全てを縦持ちのテーブルに変換できる。

SELECT
    order_id, "fashion" AS product_category, fashion AS revenue
FROM
    project_id.dataset.sales
UNION ALL
    (
        SELECT
            order_id, "zakka" AS product_category, zakka AS revenue
        FROM
            project_id.dataset.sales
    )
UNION ALL
    (
        SELECT
            order_id, "food" AS product_category, food AS revenue
        FROM
            project_id.dataset.sales
    )

order_id	product_category	fashion
123	fashion	18600
124	fashion	null
123	zakka	null
124	zakka	2400
123	food	5800
124	food	8800

専用関数を使って横持ちテーブルを縦持ちテーブルに変換する

BigQuery がサポートしている UNPIVOT 関数を使って横持ちテーブルを縦持ちテーブルに変換してみる。
UNPIVOT 関数は横持ちから縦持ちに変換する専用関数になる。

// 1. revenue => 横持ちテーブルで指標となっていた値を格納する任意の列名を指定している
// 今回の場合は売り上げ金額が指標となっていたので金額が格納される
// 2. product_category => 指標を分けていた基準名（任意）
// 売り上げ金額を商品カテゴリで分けていたので product_category とする
// INでは縦持ちに変換したい値のカラム名を指定。元のテーブルのカラム名と完全一致する必要がある。

SELECT
    *
FROM
    project_id.dataset.sales UNPIVOT INCLUDE NULLS (revenue FOR product_category IN (fashion,zakka,food))

order_id	product_category	fashion
123	fashion	18600
123	zakka	null
123	food	5800
124	fashion	null
124	zakka	2400
124	food	8800

null の値を除外したい場合は次のようになる。

SELECT
    *
FROM
    project_id.dataset.sales UNPIVOT EXCLUDE NULLS (revenue FOR product_category IN (fashion,zakka,food))

order_id	product_category	fashion
123	fashion	18600
123	food	5800
124	zakka	2400
124	food	8800

縦持ちテーブルを横持ちテーブルに変換する

専用関数を使わずに変換してみる。

SELECT
    ad_id,
    IF(phase="before",bounce_rate,null) AS before_index,
    IF(phase="after",bounce_rate,null) AS after_index
FROM
    project_id.dataset.test

次のように非正規化された横持ちのテーブルが返ってくる。

ad_id	before_index	after_index
A	0.69	null
A	0.67	null
A	null	0.63
A	null	0.61

専用関数を使って縦持ちから横持ちに変換する

横持ちに変換する専用関数としてPIVOT 関数が存在する。

// 1. AVG(bounce_rate) => 横持ちにした時に指標となる値とその集計方法を指定する
// 2. phase => 指標を列に分ける値が格納されているカラム名を指定する
// 3. IN で指標を列に分ける基準の値を指定。元テーブルと一致する値じゃないとnullになってしまう

SELECT
    ad_id,
    before,
    after
FROM
    project_id.dataset.test PIVOT (AVG(bounce_rate) FOR phase IN ("before","after"))

専用関数を使わなかった場合と同じ結果が返ってくる。

ad_id	before_index	after_index
A	0.69	null
A	0.67	null
A	null	0.63
A	null	0.61

参考記事・書籍

BigQuery ではじめる SQL データ分析 GA4 & Search Console & Google フォーム対応
WITH 句かサブクエリか