BigQuery の結合

BigQuery入門

Matthew Forrest

Field CTO

BigQuery でのデータ結合

INNER JOIN: 両テーブルに存在する値。
LEFT JOIN: 左テーブルの全行。右テーブルとマッチ。
RIGHT JOIN: 右テーブルの全行。左テーブルとマッチ。
FULL JOIN: 両テーブルの全行。マッチと非マッチを含む。
CROSS JOIN: 両テーブルの全行どうしをすべて組み合わせ。

SQL の結合を視覚化した画像

実務での JOIN 例

Customers: 左テーブル

Orders: 右テーブル

INNER JOIN: 顧客とその注文の一致
LEFT JOIN: 注文がない顧客も含め全顧客を表示
RIGHT JOIN: 顧客IDが欠けていても全注文を表示
FULL JOIN: 互いに関与していなくても全顧客・全注文を表示
CROSS JOIN: 条件なしで全注文×全顧客を組み合わせ

INNER JOIN

両データセットの一致のみを返す

SELECT
  c.customer_id, s.product_name
FROM customers c
-- The INNER keyword is optional
JOIN sales_data s
ON c.customer_id = s.customer_id;

| customer_id | product_name         |
|-------------|----------------------|
| 1           | Bluetooth Headphones |
| 2           | Running Shoes        |

LEFT JOIN

左側データセットの全行を返す

SELECT
  c.customer_id, s.product_name
FROM customers c
LEFT JOIN sales_data s
ON c.customer_id = s.customer_id;

| customer_id | product_name         |
|-------------|----------------------|
| 1           | Bluetooth Headphones |
| 2           | Running Shoes        |
| 3           | null                 |

RIGHT JOIN

右側データセットの全行を返す

SELECT
  c.customer_id, s.product_name
FROM customers c
RIGHT JOIN sales_data s
ON c.customer_id = s.customer_id;

| customer_id | product_name         |
|-------------|----------------------|
| 1           | Bluetooth Headphones |
| 2           | Running Shoes        |
| null        | External Microphone  |

OUTER JOIN

「右・左」結合: 右と左の両データセットの全行

SELECT
  c.customer_id, s.product_name
FROM customers c
OUTER JOIN sales_data s
ON c.customer_id = s.customer_id;

| customer_id | product_name         |
|-------------|----------------------|
| 1           | Bluetooth Headphones |
| 2           | Running Shoes        |
| 3           | null                 |
| null        | External Microphone  |

SELF/CROSS JOIN

デカルト結合: すべての行 × すべての行

SELECT
  c.customer_id,
  s.product_name,

-- Adding table names separated 
-- by a comma is a CROSS JOIN
-- Order is determined by the
-- left table, here "customers"
FROM customers c, sales_data s;

| customer_id | product_name         |
|-------------|----------------------|
| 1           | Bluetooth Headphones |
| 1           | null                 |
| 2           | Bluetooth Headphones |
| 2           | null                 |
| 3           | null                 |
| 3           | Bluetooth Headphones |

JOIN と UNNEST

UNNEST したデータの結合にも使用

SELECT
  c.customer_id,
  payments.method
FROM customers c, 
UNNEST(
  customers.payment_methods
) payments;

| customer_id | product_name |
|-------------|--------------|
| 1           | Visa         |
| 1           | Mastercard   |
| 1           | Venmo        |
| 1           | Paypal       |
| 2           | Amex         |
| 2           | Visa         |

演習に進みましょう！

BigQuery入門