透過連接與集合運算子進行資料轉換

資料工程工作流程常需要結合多個資料集的資訊。 連接運算 會根據相關欄位匹配行以水平組合表格，而 集合運算 則透過附加、交集或排除列來垂直組合表格。 了解何時使用每種方法，有助於建立高效的轉型流程。

在這個單元中，你會學習如何根據關係連接資料表，並應用集合運算子來結合與相符結構的資料集。

結合資料表與聯結

聯結根據相關資料行合併兩個資料表中的資料列。 連接類型決定結果中出現的列數，以及未匹配的列如何處理。

了解連接類型

每種連接類型在資料轉換中都有特定用途：

聯結類型	Description	用例
INNER	只回傳兩個資料表中相符的列	與有效客戶連結訂單
LEFT	回傳左邊表格的所有列；當沒有匹配時，為右邊表格新增 NULL。	包括所有客戶，即使沒有訂單
RIGHT	回傳右表的所有資料列；在沒有匹配的情況下，為左表的資料列新增 NULL。	包括所有部門，即使沒有員工
FULL	返回兩個資料表中的所有資料列；為不匹配的項目新增 NULL 值。	結合資料集以找出任一端的缺口
SEMI	回傳左表格中與右表格匹配的列	篩選下訂單的顧客
ANTI	回傳左邊表格中與右邊表格無匹配的列	尋找從未下過訂單的顧客
CROSS	傳回所有資料列的笛卡兒乘積	產生所有可能的組合

用 SQL 連接資料表

該JOIN子句根據ON或USING指定的條件來組合表格。 考慮員工與部門的資料表，將員工與其所屬部門配對：

-- Inner join: only employees with matching departments
SELECT e.id, e.name, e.deptno, d.deptname
FROM employee e
INNER JOIN department d ON e.deptno = d.deptno;

內部聯結只會傳回三個資料列—部門編號存在於部門資料表中的員工。 第4、5、6部門的員工沒有出現，因為這些部門不在部門表中。

當您需要包含所有員工，不管部門是否相符，請使用左聯結：

-- Left join: all employees, NULL for missing departments
SELECT e.id, e.name, e.deptno, d.deptname
FROM employee e
LEFT JOIN department d ON e.deptno = d.deptno;

這會傳回所有六名員工。 沒有符合部門的項目在 deptname 資料行中顯示 NULL。

將 DataFrames 與 PySpark 連接

PySpark join() 的方法也提供相同的功能。 參數 how 指定連接類型：

df_employee = spark.table("employee")
df_department = spark.table("department")

# Inner join
df_inner = df_employee.join(
    df_department,
    on=df_employee.deptno == df_department.deptno,
    how="inner"
)
display(df_inner)

對於左外聯結、右外聯結或全外聯結，請變更 how 參數：

# Left join: keep all employees
df_left = df_employee.join(
    df_department,
    on="deptno",
    how="left"
)

# Full outer join: keep all rows from both tables
df_full = df_employee.join(
    df_department,
    on="deptno",
    how="full"
)

當兩個 DataFrame 的 join 欄位名稱相同時，將欄位名稱以字串傳遞。 此方法避免結果中出現重複欄位。

使用半連接與反連接篩選資料

半聯結和反聯結功能強大，可以根據資料在另一個資料表中是否存在來篩選資料。 半聯結會傳回左側資料表中符合條件的資料列，而不包含右側資料表的資料行。

-- Find employees who belong to listed departments
SELECT *
FROM employee
LEFT SEMI JOIN department ON employee.deptno = department.deptno;

反連接則返回相反的結果：沒有匹配的行。

-- Find employees in departments not listed
SELECT *
FROM employee
LEFT ANTI JOIN department ON employee.deptno = department.deptno;

在 PySpark 中，請使用 how="semi" 或 how="anti"：

# Employees with matching departments
df_semi = df_employee.join(df_department, on="deptno", how="semi")

# Employees without matching departments
df_anti = df_employee.join(df_department, on="deptno", how="anti")

使用集合運算子合併列

集合運算子是垂直運作的，將相同欄位結構的查詢列合併。 與連接不同，集合運算符不會在條件下匹配——它們會堆疊或比較整個結果集。

了解集合運算子

Azure Databricks 支援三個集合運算子：

Operator	Description	行為
UNION	結合兩個查詢的列	預設會移除重複項目，使用 ALL 來保留重複項目。
INTERSECT	傳回兩個查詢中都存在的資料列	只會出現相符的列數
EXCEPT	傳回來自第一個查詢，而不在第二個查詢中的資料列	預設移除重複項目

兩個查詢必須有相同數量且資料型別相容的欄位。

使用 UNION 合併資料集

UNION 將一個查詢的列附加到另一個查詢。 預設情況下，它會移除重複的：

-- Combine active and archived customers (no duplicates)
SELECT customer_id, name, email FROM active_customers
UNION
SELECT customer_id, name, email FROM archived_customers;

要保留所有列，包括重複的列，請使用 UNION ALL：

-- Combine all rows, keeping duplicates
SELECT customer_id, name, email FROM active_customers
UNION ALL
SELECT customer_id, name, email FROM archived_customers;

當你確定沒有重複或重複有意義時使用 UNION ALL 。 它的效能比較好，因為可以跳過重複刪除步驟。

在 PySpark 中，用 union() 合併 DataFrame。 請注意，PySpark union() 的行為如同 UNION ALL—它會保留重複項。

df_active = spark.table("active_customers")
df_archived = spark.table("archived_customers")

# Combine both tables (keeps duplicates)
df_combined = df_active.union(df_archived)

# Remove duplicates if needed
df_distinct = df_active.union(df_archived).distinct()

用 INTERSECT 尋找常見的列

INTERSECT 僅回傳同時出現於兩個查詢中的列。 這有助於識別重疊資料：

-- Find customers in both active and archived tables
SELECT customer_id FROM active_customers
INTERSECT
SELECT customer_id FROM archived_customers;

此查詢可識別同時出現在兩個資料表中的客戶，對資料品質檢查或需核對的紀錄非常有用。

排除帶有 EXCEPT 的列

EXCEPT 回傳第一個查詢中不存在的列。 這有助於辨識缺口或獨特紀錄：

-- Find active customers not in the archived table
SELECT customer_id FROM active_customers
EXCEPT
SELECT customer_id FROM archived_customers;

你也可以用 MINUS 作為 EXCEPT 的同義詞：

-- Same result using MINUS syntax
SELECT customer_id FROM active_customers
MINUS
SELECT customer_id FROM archived_customers;

像 UNION 一樣，INTERSECT 和 EXCEPT 預設都會移除重複項。 必要時加入 ALL 以保存重複資料。

在聯結與集合運算子之間選擇

連接與集合運算子的選擇取決於你的轉換目標。 連接是將相關表格的欄位水平組合，而集合運算符則是垂直組合列。

需要時使用連接：

• 透過新增相關資料表的欄位來豐富記錄
• 根據關鍵關係配對記錄
• 根據在另一個資料表中是否存在（半聯結／反聯結）來篩選資料

當你需要時，可以使用 集合運算子 ：

• 從多個具有相同結構的來源附加記錄
• 尋找跨資料集的共同紀錄
• 識別單一資料集獨有的紀錄

這兩種方法都構成了有效資料轉換流程的基礎。 在下一單元，你將探討如何將轉換後的資料載入 Unity Catalog 中的目標資料表。