BigQuery超入門②
はじめに
この記事は BigQuery 超入門 ① の続きになります。
学んだこと
- テーブル結合
- テーブル集合演算
- NULL の扱い
- 仮想テーブル
テーブル結合（横方向につなぐ）
複数のテーブルを横方向につなぐ方法として「テーブル結合」という方法がある。
これを実現する JOIN について確認する。
INNER JOIN（内部結合）
二つのテーブルが共通して持つフィールドの値が一致するレコードのみを残す。
sales テーブル
| sale_id | product_id | sale_amount |
|---|---|---|
| 1 | 101 | 500 |
| 2 | 102 | 300 |
| 3 | 103 | 700 |
product テーブル
| product_id | product_name |
|---|---|
| 101 | Product A |
| 102 | Product B |
| 103 | Product C |
| 104 | Product D |
SQL 実行
SELECT
*
FROM
project_id.dataset.sales AS s
INNER JOIN project_id.dataset.products AS p
ON s.product_id = p.product_id
結合結果
product_id=104 は sales テーブルに存在しないため結合時に削除される。
そして両テーブルのフィールドが結合され横方向に結合される。
| sale_id | product_id(sales) | product_id(products) | sale_amount | product_name |
|---|---|---|---|---|
| 1 | 101 | 101 | 500 | Product A |
| 2 | 102 | 102 | 300 | Product B |
| 3 | 103 | 103 | 700 | Product C |
USING
結合するテーブルで共通のフィールドを持っている場合は USING 句を使うことができる。
USING 句を使うことで結合した時に共通のフィールドが一つになる。
SELECT
*
FROM
project_id.dataset.sales AS s
INNER JOIN project_id.dataset.products AS p
USING(product_id)
結合結果（JOIN の場合）
共通のフィールドが両方表示される。
| sale_id | product_id（sales） | product_id（product） | sale_amount | product_name |
|---|---|---|---|---|
| 1 | 101 | 101 | 500 | Product A |
| 2 | 102 | 102 | 300 | Product B |
| 3 | 103 | 103 | 700 | Product C |
結合結果（USING の場合）
共通のフィールドが一つにまとまる。
| sale_id | product_id | sale_amount | product_name |
|---|---|---|---|
| 1 | 101 | 500 | Product A |
| 2 | 102 | 300 | Product B |
| 3 | 103 | 700 | Product C |
LEFT OUTER JOIN（左外部結合）
結合時、先に指定したテーブルを「左側」、後に指定したテーブルを「右側」として左側は全レコード、右側は左側と共通して持つフィールドの値が重なっているレコードのみを結合する。
共通のフィールドの値が左側にはあるが右側には存在しない場合、結合したフィールドの値は null になる。
共通のフィールドの値が右側にしか存在しない場合は結合時に削除される。
products テーブル
| product_id | product_name |
|---|---|
| 1 | アジ |
| 2 | サバ |
| 3 | タコ |
orders テーブル
| order_id | product_id | qty |
|---|---|---|
| 1 | 2 | 4 |
| 2 | 3 | 3 |
| 3 | 4 | 4 |
SQL 実行
SELECT
p.product_id,
o.qty,
p.product_name
FROM
project_id.dataset.products AS p
LEFT JOIN project_id.dataset AS o
ON p.product_id = o.product_id
結合結果
order_id=3 が持つ product_id は products テーブルに存在しないため結合した時に削除される。
product_id=1 は orders テーブルに存在しないため結合したフィールドの値は null になる。
| product_id | qty | product_name |
|---|---|---|
| 1 | null | アジ |
| 2 | 4 | サバ |
| 3 | 3 | タコ |
注意したい点としてはベースとなるテーブルを左側に指定すること。（先に指定したテーブルが全レコード残るため）例えば products テーブルを残したい場合 products テーブルを右側に指定してしまうと orders テーブルに存在しない product_id を持つレコードは削除されてしまうため意図しない結合結果になってしまう。
SELECT
o.product_id,
o.qty
FROM
project_id.dataset AS o
LEFT JOIN project_id.dataset.products AS p
ON o.product_id = p.product_id
意図しない結合結果になる。
| product_id | qty | product_name |
|---|---|---|
| 2 | 4 | サバ |
| 3 | 3 | タコ |
| 4 | 4 | null |
RIGHT JOIN（右外部結合）
LEFT JOIN とは逆で先に指定したテーブルを「左側」、後に指定したテーブルを「右側」として右側は全レコード、左側は右側と共通して持つフィールドの値が重なっているレコードのみを結合する。
共通のフィールドの値が右側にはあるが左側には存在しない場合、結合したフィールドの値は null になる。
共通のフィールドの値が左側にしか存在しない場合は結合時に削除される。
また左右の結合でも結合キーが同じであれば USING を使うことができる。
SELECT
*
FROM
project_id.dataset.products AS p
LEFT JOIN project_id.dataset AS o
USING(product_id)
FULL JOIN（完全外部結合）
結合時、先に指定したテーブルを「左側」、後に指定したテーブルを「右側」として左側と右側の全レコードを残す結合。 相手方にないフィールドは null になる。
products テーブル
| product_id | product_name |
|---|---|
| 1 | アジ |
| 2 | サバ |
| 3 | タコ |
orders テーブル
| order_id | product_id | qty |
|---|---|---|
| 1 | 2 | 4 |
| 2 | 3 | 3 |
| 3 | 4 | 4 |
SELECT
p.product_id AS p_product_id,
o.product_id AS o_product_id,
p.product_name,
o.order_id,
o.qty
FROM
project_id.dataset.products AS p
FULL JOIN project_id.dataset.orders AS o
ON p.product_id = o.product_id
結合結果
| p_product_id | o_product_id | product_name | order_id | qty |
|---|---|---|---|---|
| 1 | null | アジ | null | null |
| 2 | 2 | サバ | 1 | 4 |
| 3 | 3 | タコ | 2 | 3 |
| null | 4 | null | 3 | 4 |
SELF JOIN（自己結合）
異なるテーブルではなく、同じテーブル同士を結合する自己結合というパターンも存在する。
決まった書き方があるわけではなく今までの JOIN を使う。（実務で使うことが多々あり勉強になった）
例えば次のような年間販売個数テーブルで前年との比較をしたい場合などに使える。
annual_sales_volumes（年間販売個数）テーブル
| year | qty |
|---|---|
| 2021 | 272 |
| 2022 | 309 |
| 2023 | 310 |
年を一つずらすように結合する。
SELECT
a1.year AS base_year,
a2.year AS compare_year,
a1.qty AS base_qty,
a2.qty AS compare_qty,
IFNULL(ROUND(SAFE_DIVIDE(a2.qty,a1.qty)*100, 2), 0) AS growth_rate_percent
FROM s_5_4_a AS a1 INNER JOIN s_5_4_a AS a2 ON a1.year = a2.year-1 // ここがポイント
結合結果
| base_year | compare_year | base_qty | compare_qty | growth_rate_percent |
|---|---|---|---|---|
| 2021 | 2022 | 272 | 309 | 113.6 |
| 2022 | 2023 | 309 | 310 | 100.32 |
CROSS JOIN（交差結合）
左側のテーブル 1 レコード に対して右側の全レコードを結合していく総当たりの形式での結合方法。
例えば 3 レコード存在するテーブル同士を結合した場合は 3*3 で 9 レコードになる。
SELECT
a1.year AS base_year,
a2.year AS compare_year,
a1.qty AS base_qty,
a2.qty AS compare_qty,
FROM
project_id.dataset.annual_sales_volumes AS a1
CROSS JOIN project_id.dataset.annual_sales_volumes AS a2
結合結果
| base_year | compare_year | base_qty | compare_qty |
|---|---|---|---|
| 2021 | 2021 | 272 | 272 |
| 2021 | 2022 | 272 | 309 |
| 2021 | 2023 | 272 | 310 |
| 2022 | 2021 | 309 | 272 |
| 2022 | 2022 | 309 | 309 |
| 2022 | 2023 | 309 | 310 |
| 2023 | 2021 | 310 | 272 |
| 2023 | 2022 | 310 | 309 |
| 2023 | 2023 | 310 | 310 |
NULL の扱い
テーブル結合では結合の仕方によっては NULL が発生することもあり、この NULL の扱いをどうするかが重要になる。
ここではフィールドの値が NULL の場合に置き換えることができる方法について確認する。
IFNULL
IFNULL 関数を使うことで値が null だった場合に任意の値を入れることができる。
もちろん任意の値の型はフィールドに合わせる必要がある。
SELECT
p.product_id,
p.product_name,
IFNULL(o.qty, 0) AS qty
FROM
project_id.dataset.products AS p
LEFT JOIN project_id.dataset.orders AS o
USING(product_id)
null は 0 に置き換わる
| product_id | qty | product_name |
|---|---|---|
| 1 | 0 | アジ |
| 2 | 4 | サバ |
| 3 | 3 | タコ |
COALESCE
COALESCE 関数を使うとより高度に null を扱うことができる。
null の場合に別カラムを指定できる。
例えば「registration_year（登録年）が null の場合は first_purchase_year（初回購入年）に置き換える」といったことができる。
SELECT
COALESCE(registration_year, first_purchase_year) AS register_user
FROM
project_id.dataset.test1 AS t1
INNER JOIN project_id.dataset.test2 AS t2 USING(user_id)
GROUP BY register_user
テーブル集合演算
ここからは複数のテーブルを縦方向につなぐ方法である「テーブル集合演算」について確認する。
テーブルという「集合」同士の「演算」（足し算/掛け算/引き算）という考え方からテーブルの集合演算と呼ばれているらしい。
UNION（和集合）
UNION はテーブルとテーブルを足し算して和集合を作る。
和集合とは二つの集合を「もれなく」「ダブりなく」ひとつにまとめる演算。
UNION ALL を使うと重複を許可して結合する。
products テーブル ①
| product_name | qty |
|---|---|
| アジ | 10 |
| サバ | 20 |
| タコ | 30 |
products テーブル ②
| product_name | qty |
|---|---|
| サバ | 20 |
| タコ | 40 |
| イカ | 50 |
SELECT
product_name, qty
FROM
project_id.dataset.product1
UNION ALL
SELECT
product_name, qty
FROM
project_id.dataset.product2
実行結果
サバ, 20 が重複しているがそのままレコードが増える。
| product_name | qty |
|---|---|
| アジ | 10 |
| サバ | 20 |
| タコ | 30 |
| サバ | 20 |
| タコ | 40 |
| イカ | 50 |
一方 UNION DISTINCT は重複を許可しない。
SELECT
product_name, qty
FROM
project_id.dataset.product1
UNION DISTINCT
SELECT
product_name, qty
FROM project_id.dataset.product2
実行結果
サバ, 20 が重複しているためレコードは削除される。
| product_name | qty |
|---|---|
| アジ | 10 |
| タコ | 30 |
| タコ | 40 |
| イカ | 50 |
補足 1. 集合演算における重複の考え方
集合演算における「重複」とはSELECT で取得している全フィールドの内容が全て一致するかどうかという考え方になる。
🙆 重複している
「サバ」「20」が重複しており全フォールドの内容が一致している。
| product_name | qty |
|---|---|
| サバ | 20 |
| サバ | 20 |
🙅 重複していない
「サバ」は重複しているが数が一致していないため全フォールドで見ると一致していない。
| product_name | qty |
|---|---|
| サバ | 10 |
| サバ | 20 |
補足 2. 集合演算をする際の注意点
SELECT で抽出するフィールドの種類、数、順序完全に一致していなければならない。
🙆 一致している
両テーブルの product_id フィールドの値が抽出される
SELECT
s.product_id
FROM
project_id.dataset_id.sales AS s
UNION ALL
SELECT
p.product_id
FROM
project_id.dataset_id.products AS p
🙅 一致していない（型の不一致）
「s.user_id」と「p.product_name」はそもそも型が一致しないのでエラーになる。
SELECT
s.product_id,
+ s.user_id
FROM
project_id.dataset_id.sales AS s
UNION ALL
SELECT
p.product_id,
+ p.product_name
FROM
project_id.dataset_id.products AS p
🙅 一致していない（型は一致するがフィールドが相違）
「s.user_id」と「p.cost」は型が一致するため SQL は実できる。
しかしフィールドが相違しているため左側で指定したフィールドが有効になり意図しない実行結果になってしまう点に注意する。
SELECT
s.product_id,
+ s.user_id
FROM
project_id.dataset_id.sales AS s
UNION ALL
SELECT
p.product_id,
+ p.cost
FROM
project_id.dataset_id.products AS p
user_id が途中から cost の値になってしまう。
| product_id | user_id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 15000 |
| 4 | 35000 |
INTERSECT（積集合）
INTERSECT は重複するレコードだけを取り出し積集合を作る。
積集合とは二つの集合から共通部分を取り出す演算。
必ず DISTINCT と併せて使用する。
SELECT
product_name, qty
FROM
project_id.dataset.product1
INTERSECT DISTINCT
SELECT
product_name, qty
FROM project_id.dataset.product2
実行結果
重複しているサバ, 20 のみを取得する。
| product_name | qty |
|---|---|
| サバ | 20 |
EXCEPT（差集合）
EXCEPT はあるテーブルから別のテーブルを差し引いた差集合を作る。
差集合とは二つの集合から別の集合に属する集合を差し引く演算。
SELECT
product_name, qty
FROM
project_id.dataset.product1
EXCEPT DISTINCT
SELECT
product_name, qty
FROM
project_id.dataset.product2
実行結果
product テーブル ② と重複しているサバが差し引かれたレコードを取得する。
| product_name | qty |
|---|---|
| アジ | 10 |
| タコ | 30 |
EXCEPT はテーブルを記述する順番によって結果が変わる。
例えば上記の SQL の FROM を逆にすると結果も変わる。
SELECT
product_name, qty
FROM
project_id.dataset.product2
EXCEPT DISTINCT
SELECT
product_name, qty
FROM
project_id.dataset.product1
実行結果
product テーブル ① に属してるレコードは差し引いたレコードを取得する。
| product_name | qty |
|---|---|
| タコ | 40 |
| イカ | 50 |
仮想テーブル
ここまで FROM には実在するテーブルを指定してきたが、SQL を実行した結果を返すテーブルである仮想テーブルを指定することも可能。
実体のあるテーブルから一時的に必要な仮のテーブルを作ることで集計が便利になったり SQL 文の可読性の向上が期待できたりする。
仮想テーブルを作成する方法としては WITH/ビュー/サブクエリの 3 パターンが存在する。
WITH
WITH とはクエリ内で一時的に参照するテーブル（仮想テーブル）を作成しあたかも実在するテーブルかのように扱うことができるもの
CTE（共通テーブル式） とも呼ばれている。
あくまでもクエリ内で一時的に参照できる仮想テーブルのため、クエリ内でしか使うことができない。またクエリが終了すると消える一時的なテーブルになる。
次のように WITH 句を使って実行する。
実行結果のテーブル（仮想テーブル）に対して名前をつけることで、以降はその名前を使って実体のあるテーブルかのように繰り返し使うことができる。
ここでは「月毎の平均売り上げ個数」という仮想テーブルを作ることでクエリ内で必要な時に通常と同じような扱いでテーブルとして扱うことができる。
WITH avg_january_qty AS (
SELECT AVG(qty) AS qty_january
FROM january_order_products
),
avg_february_qty AS (
SELECT AVG(qty) AS qty_february
FROM february_order_products
)
// 以降は通常のテーブルのように扱うことができる
SELECT
*
FROM
avg_january_qty CROSS JOIN avg_february_qty
このように WITH を使って仮想テーブルを作っておくことで繰り返し使う際にテーブルを呼び出すだけになる。
また適切な名前をつけることでクエリの可読性の向上に繋がる。
ビュー
ビューとはSQL の実行結果を保存したもの。（=仮想テーブルを保存したもの）
WITH で作る仮想テーブルとは違い BigQuery 上に保存できるため、あたかも実体のあるテーブルかのように仮想テーブルを使い回すことができる。
例えば先ほど使った「1 月の平均売り上げ個数」のクエリの結果をビューとして保存すると次のようなクエリで呼び出すことができる。
（ビュー名は適当です）
SELECT
*
FROM
project_id.dataset.view_avg_january_qty
ビューに関しての注意点として、あくまでも実体のあるテーブルはなく SQL 文の実行結果によって生成される結果テーブルである、という点が挙げられる。
そのためビューを対象として SQL 文を実行するとビューの SQL 文が再実行されるという動作になるので次の点に気を付ける。
- ビューで保存した SQL 文の処理が時間かかるなら総じて処理に時間がかかる
- 実行結果が都度変わる可能性がある
- 元データが変わると結果も変わるため
最後に
- インプットすることで引き出しが増えてきた
- UNION と JOIN をごちゃ混ぜで考えていたが違いを知ることができた
Discussion