集合運算是關聯式資料函式庫的核心功能，允許開發者操作和比較多個資料集。理解集合運算對於撰寫高效的 SQL 查詢至關重要，特別是在需要合併、找出共同點或差異的場景。UNION 操作符合併多個結果集並去除重複項，適用於整合不同來源的相似資料。INTERSECT 運算子則識別多個結果集的交集部分，可用於找出共同的客戶、產品或其他實體。EXCEPT 運算子傳回第一個結果集中存在但第二個結果集中不存在的資料，適用於比對兩個資料集的差異。這些運算子在資料分析、報表生成和資料清洗等方面都有廣泛的應用。
第四章：集合運算
雖然你可以一次處理一行資料，但關聯式資料函式庫本質上是關於集合的。本章將探討集合運算元的使用，它允許你結合多個結果集的資料。首先，我將對集合論進行簡要的介紹，然後展示如何使用union、intersect和except集合運算元來混合多個資料集。
集合論基礎
你可能還記得在早期的數學課上看到過像圖4-1這樣的圖表。
圖4-1中的陰影區域代表集合A和B的聯集，重疊區域只被包含一次。下面是另一種方式來說明聯集運算，使用兩個整數集合： A = {1, 2, 4, 7, 9} B = {3, 5, 7, 9} A聯集B = {1, 2, 3, 4, 5, 7, 9}
在這個例子中，集合A包含5個整數，集合B包含4個整數。每個集合都有幾個獨特的值，但兩個集合都分享值7和9。A和B的聯集產生總共7個值，其中整數7和9只被包含一次。
接下來，讓我們看看兩個集合分享的區域，被稱為交集。圖4-2顯示了交集的圖形描述。
使用與上一個例子相同的整數集合，你可以看到A和B的交集只包含數字7和9： A = {1, 2, 4, 7, 9} B = {3, 5, 7, 9} A交集B = {7, 9}
如果兩個集合之間沒有重疊，交集將為空，並且intersect是唯一可以產生空集合的集合運算元。
第三個也是最後一個如圖4-3所示的圖表，說明瞭一個運算，其中傳回一個集合的內容減去與另一個集合的重疊部分。
被稱為except運算（但你也可以互換使用minus），這個運算產生集合A中不與集合B重疊的所有元素。下面是使用數字集合的相同運算：
A = {1, 2, 4, 7, 9}
B = {3, 5, 7, 9}
A except B = {1, 2, 4}
在這三個集合運算中，except是唯一一個如果交換集合的順序會產生不同結果的運算。以下是相同的例子，但顯示了B except A的結果：
A = {1, 2, 4, 7, 9}
B = {3, 5, 7, 9}
B except A = {3, 5}
這三個集合運算元涵蓋了很多情況，但你可能會想知道如何描述如圖4-4所示的情況。
圖4-4中所示的操作無法使用前面描述的三個集合運算元中的任何一個來實作。然而，可以透過組合多個集合運算元來實作。以下是兩種不同的方式來生成圖4-4中所示的陰影區域： (A union B) except (A intersect B) – 或 – (A except B) union (B except A)
本章的下一節將向你展示如何將這些集合運算元付諸實踐。
UNION運算元
union運算元允許你結合兩個資料集。以下是一個簡單的例子：
SELECT 1 AS numeric_col, 'ABC' AS string_col
UNION
SELECT 2 AS numeric_col, 'XYZ' AS string_col;
結果：
+
---
-
---
-
---
--+
---
-
---
-
---
-+
| NUMERIC_COL | STRING_COL |
|
---
-
---
-
---
--+
---
-
---
-
---
-|
| 1 | ABC |
| 2 | XYZ |
+
---
-
---
-
---
--+
---
-
---
-
---
-+
這是一個非常簡單的例子：兩個單行結果集，沒有重疊的值，結合起來形成一個兩行的結果集。這種型別的陳述式被稱為複合查詢，因為它包含兩個獨立的查詢，由一個集合運算元分隔。
在使用union運算元之前，有一些限制需要注意：
- 兩個集合必須包含相同數量的列。
- 列的資料型別必須匹配。
如果兩個集合具有不同的列數，Snowflake會傳回一個特定的錯誤訊息：
SELECT 1 AS numeric_col, 'ABC' AS string_col
UNION
SELECT 2 AS numeric_col, 'XYZ' AS string_col, 99 AS extra_col;
錯誤訊息：
001789 (42601): SQL compilation error: invalid number of result columns for set operator ...
如果列的資料型別不匹配，也會傳回錯誤訊息：
SELECT 1 AS numeric_col, 'ABC' AS string_col
UNION
SELECT 'XYZ' AS numeric_col, 2 AS string_col;
錯誤訊息：
100038 (22018): Numeric value 'ABC' is not recognized
如前所述，union運算元在構建結果集時刪除重複項，這意味著任何重疊的值只會被表示一次。以下是相同的兩個集合（{1, 2, 4, 7, 9}和{3, 5, 7, 9}），使用VALUES子句構建，然後使用union結合：
SELECT integer_val
FROM (VALUES (1), (2), (4), (7), (9)) AS set_a (integer_val)
UNION
SELECT integer_val
FROM (VALUES (3), (5), (7), (9)) AS set_b (integer_val);
結果：
+
---
-
---
-
---
--+
| INTEGER_VAL |
|
---
-
---
-
---
--|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
| 9 |
+
---
-
---
-
---
--+
如你所見，兩個集合都包含7和9，但這些值在結果集中只出現一次，因為union運算元對值進行排序並刪除重複項。
雖然這是預設行為，但在某些情況下，你可能不想刪除重複項。在這種情況下，可以使用UNION ALL：
SELECT integer_val
FROM (VALUES (1), (2), (4), (7), (9)) AS set_a (integer_val)
UNION ALL
SELECT integer_val
FROM (VALUES (3), (5), (7), (9)) AS set_b (integer_val);
結果：
+
---
-
---
-
---
--+
| INTEGER_VAL |
|
---
-
---
-
---
--|
| 1 |
| 2 |
| 4 |
| 7 |
| 9 |
| 3 |
| 5 |
| 7 |
| 9 |
+
---
-
---
-
---
--+
結果集現在包含九行，而不是七行，重複的7和9佔了額外的兩行。
練習題3-3解答
根據練習題3-3的要求，我們需要寫一個查詢，傳回每個寵物主人的名字，以及匹配的寵物的名字（如果存在的話）。結果應該包括Pet_Owner表中的每一行。
首先，我們來分析一下給定的表格結構：
Pet_Owner表：
| OWNER_ID | OWNER_NAME |
|---|---|
| … | … |
Pet表：
| PET_ID | OWNER_ID | PET_NAME | | — | — | | … | … | … |
要實作練習題3-3的要求，我們需要使用LEFT JOIN來結合Pet_Owner和Pet表格，以確保傳回Pet_Owner中的每一行，即使在Pet表中沒有匹配的行。
SELECT po.OWNER_NAME, p.PET_NAME
FROM Pet_Owner po
LEFT JOIN Pet p ON po.OWNER_ID = p.OWNER_ID;
練習題3-4解答
根據練習題3-4的要求，我們需要寫一個查詢，傳回每個寵物的名字，以及Owner #1和Owner #2的名字。結果集應該對應每個寵物有一行（總共4行）。一些業主的名字可能是空值。
首先，我們來分析一下給定的表格結構：
Pet_Owner表：
| OWNER_ID | OWNER_NAME |
|---|---|
| … | … |
Pet表：
| PET_ID | OWNER_ID1 | OWNER_ID2 | PET_NAME |
|---|
要實作練習題3-4的要求，我們需要使用LEFT JOIN來結合Pet表格與Pet_Owner表格兩次，分別對應OWNER_ID1和OWNER_ID2，以取得Owner #1和Owner #2的名字。
SELECT
p.PET_NAME,
po1.OWNER_NAME AS Owner1_Name,
po2.OWNER_NAME AS Owner2_Name
FROM Pet p
LEFT JOIN Pet_Owner po1 ON p.OWNER_ID1 = po1.OWNER_ID
LEFT JOIN Pet_Owner po2 ON p.OWNER_ID2 = po2.OWNER_ID;
Plantuml 圖表翻譯：
此圖示說明瞭兩個集合之間的聯集、交集和差集運算。
- 聯集（Union）：兩個集合的所有元素，去除重複項。
- 交集（Intersection）：兩個集合共有的元素。
- 差集（Except 或 Minus）：第一個集合中存在但第二個集合中不存在的元素。
圖表翻譯： 圖4-1顯示了兩個集合A和B的聯集，陰影區域代表了聯集的結果。 圖4-2顯示了兩個集合A和B的交集，陰影區域代表了交集的結果。 圖4-3顯示了兩個集合A和B的差集，陰影區域代表了差集的結果。
資料函式庫集合運算：Union、Intersect 與 Except 運算子詳解
在處理資料函式庫查詢時，我們經常需要合併或比較不同的資料集。SQL 提供了一系列的集合運算元，包括 UNION、INTERSECT 和 EXCEPT，用於處理這些需求。本文將探討這些運算子的使用方法及其規則。
UNION 運算子：合併資料集
UNION 運算子用於合併兩個或多個查詢的結果集，並去除重複的行。以下是一個範例查詢，用於找出在 1992 年或 1993 年下單總金額超過 35 萬美元的客戶：
SELECT DISTINCT o_custkey
FROM orders
WHERE o_totalprice > 350000
AND date_part(year, o_orderdate) = 1992
UNION
SELECT DISTINCT o_custkey
FROM orders
WHERE o_totalprice > 350000
AND date_part(year, o_orderdate) = 1993;
內容解密：
SELECT DISTINCT o_custkey：選擇唯一的客戶鍵值。FROM orders：從訂單表中查詢資料。WHERE o_totalprice > 350000：篩選訂單總金額超過 35 萬美元的訂單。AND date_part(year, o_orderdate) = 1992/1993：進一步篩選特定年份的訂單。UNION：合併兩個查詢結果並去除重複的行。
INTERSECT 運算子：找出資料集的交集
INTERSECT 運算子用於找出兩個查詢結果集的共同行。以下範例使用兩個數值資料集來說明其用法：
SELECT integer_val
FROM (VALUES (1), (2), (4), (7), (9))
AS set_a (integer_val)
INTERSECT
SELECT integer_val
FROM (VALUES (3), (5), (7), (9))
AS set_b (integer_val);
結果為：
+
---
-
---
-
---
--+
| INTEGER_VAL |
|
---
-
---
-
---
--|
| 7 |
| 9 |
+
---
-
---
-
---
--+
內容解密：
SELECT integer_val：選擇整數值。FROM (VALUES (...)) AS set_a/set_b (integer_val)：建立臨時資料集set_a和set_b。INTERSECT：找出兩個資料集的交集，即同時出現在set_a和set_b中的值。
EXCEPT 運算子：找出資料集的差異
EXCEPT 運算子用於傳回第一個查詢結果集中存在但第二個查詢結果集中不存在的行。以下範例同樣使用前述數值資料集來說明其用法：
SELECT integer_val
FROM (VALUES (1), (2), (4), (7), (9))
AS set_a (integer_val)
EXCEPT
SELECT integer_val
FROM (VALUES (3), (5), (7), (9))
AS set_b (integer_val);
結果為：
+
---
-
---
-
---
--+
| INTEGER_VAL |
|
---
-
---
-
---
--|
| 1 |
| 2 |
| 4 |
+
---
-
---
-
---
--+
內容解密：
SELECT integer_val：選擇整數值。FROM (VALUES (...)) AS set_a/set_b (integer_val)：建立臨時資料集。EXCEPT：傳回存在於set_a但不存在於set_b中的值。
集合運算規則
在使用集合運算元時，有幾項規則需要注意：
排序複合查詢結果：若要對結果進行排序，只能在整個查詢的末尾使用一個
ORDER BY子句，並且必須使用第一個查詢中的欄位名稱或別名。集合運算優先順序：當複合查詢包含多個不同集合運算元的查詢時，需考慮查詢的順序以達到預期結果。
Plantuml 圖示說明集合運算優先順序
@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333
title Plantuml 圖示說明集合運算優先順序
rectangle "UNION" as node1
rectangle "INTERSECT" as node2
node1 --> node2
@enduml圖表翻譯：此圖示展示了多個查詢透過不同集合運算元組合的過程。首先，查詢1和查詢2透過 UNION 合併為結果1。接著，結果1和查詢3透過 INTERSECT 得到最終結果2。
資料函式庫集合運算的應用與實踐
在前面的章節中，我們探討了SQL中的集合運算元，如union、intersect和except，這些運算元能夠有效地識別多個資料集之間的重疊部分。本章節將進一步探討如何使用這些集合運算元來分析資料之間的複雜關係。
使用集合運算元進行資料分析
對稱差集的計算
對稱差集是指兩個集合中不重複的元素，可以使用(A except B) union (B except A)來實作。以下是一個具體的SQL範例：
(select integer_val
from (values (1), (2), (4), (7), (9))
as set_a (integer_val)
except
select integer_val
from (values (3), (5), (7), (9))
as set_b (integer_val)
)
union
(select integer_val
from (values (3), (5), (7), (9))
as set_b (integer_val)
except
select integer_val
from (values (1), (2), (4), (7), (9))
as set_a (integer_val)
);
內容解密：
- 首先，使用
except運算元找出集合A中存在但集合B中不存在的元素，以及集合B中存在但集合A中不存在的元素。 - 然後，使用
union運算元將這兩個結果集合並起來，形成對稱差集。 - 需要注意的是，SQL運算元的執行順序是從上到下，因此需要使用括號來確保運算的正確順序。
資料型別的探討
Snowflake資料函式庫支援多種資料型別，其中varchar型別用於儲存字串資料，具有可變長度的特性，最大可儲存16 MB的資料。
字元資料的儲存
Snowflake使用Unicode UTF-8字元集來儲存資料，因此單個varchar欄位能夠儲存的字元數量取決於是否使用單位元組或多位元組字元。
favorite_movie varchar(100)
在定義欄位時，可以指定最大長度，如上例中的favorite_movie欄位。如果不指定最大長度，則預設為16 MB。
資料操作語言（DML）
本章節還介紹了SQL中的資料操作語言，包括insert、update、delete和merge等陳述式，用於建立和修改資料表中的資料。
字串的處理
在Snowflake中，所有字元欄位都是可變長度的，即使定義為char型別也不會被填充空格。
PUBLIC>select 'here is a string' as output_string;
+
---
-
---
-
---
-
---
---
+
| OUTPUT_STRING |
|
---
-
---
-
---
-
---
---
|
| here is a string |
+
---
-
---
-
---
-
---
---
+
當字串中包含單引號時，可以使用兩個連續的單引號來表示，或者使用雙美元符號（$$）作為分隔符。
PUBLIC>select $$string with 4 single quotes ' ' ' '$$ as output_string;
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--+
| OUTPUT_STRING |
|
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--|
| string with 4 single quotes ' ' ' ' |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--+
