在 BigQuery 中进行连接

BigQuery 入门

Matthew Forrest

Field CTO

在 BigQuery 中连接数据

INNER JOIN：两个表中都存在的值。
LEFT JOIN：左表的所有行，匹配右表。
RIGHT JOIN：右表的所有行，匹配左表。
FULL JOIN：两个表的所有行，包含匹配与不匹配。
CROSS JOIN：两个表的每行两两配对。

SQL 连接的可视化图示

连接的实际示例

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 |

Joins 与 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         |

Passons à la pratique !

BigQuery 入门