在資料函式庫操作中，有效率地擷取和處理資料至關重要。SQL 的子查詢和 FROM 子句提供強大的工具，讓開發者能夠應付複雜的資料函式庫查詢需求。本文將探討這些進階技巧，並搭配實務案例說明，讓讀者更能掌握 SQL 的精髓。
子查詢允許我們在一個查詢內嵌入另一個查詢，實作更精確的資料篩選和操作。例如，要找出訂單金額超過平均值的客戶，可以利用子查詢先計算平均訂單金額，再以此為條件篩選客戶資料。相關子查詢則更進一步，它會根據外部查詢的每一筆資料執行，適用於需要關聯不同表格的複雜邏輯。此外，子查詢還能作為資料來源，在 FROM 子句中取代表格，提供更大的彈性。
為了提高程式碼可讀性，可以使用 Common Table Expressions (CTE) 將子查詢命名，讓複雜的查詢更容易理解和維護。階層式查詢則適用於處理樹狀結構資料，例如組織架構或產品分類別。Snowflake 的 Time Travel 功能允許我們查詢過去某個時間點的資料，對於資料分析和錯誤排查非常有用。
FROM 子句除了基本的表格指定外，還支援更進階的操作。例如，可以使用 SAMPLE 子句進行資料抽樣，方便測試和分析大型資料集。FULL OUTER JOIN 則能確保所有表格的資料都被包含在結果集中，即使聯結條件不成立。最後，樞紐分析可以將資料從列轉換為欄，方便產生報表和進行特定分析。
解鎖資料查詢的無限可能：子查詢的深度應用
在資料函式庫的世界中，子查詢是一種強大的工具，它允許我們在一個查詢中嵌入另一個查詢，從而實作更複雜的資料檢索和操作。今天，玄貓將帶領大家深入探索子查詢的各種應用場景，讓你能夠更靈活地運用這項技術，提升資料處理的效率和精確性。
超越簡單檢索：相關子查詢的實戰應用
相關子查詢是一種特殊的子查詢，它的執行依賴於外部查詢的每一行資料。這種特性使得相關子查詢在處理需要關聯不同表格的複雜邏輯時非常有用。
舉例來說，假設我們想要找出所有訂單總價超過 50 萬的客戶名稱。這時，我們可以使用相關子查詢來實作：
select c.c_name
from customer c
where exists (select 1 from orders o
where o.o_custkey = c.c_custkey
and o.o_totalprice > 500000);
這段程式碼中，子查詢會針對 Customer 表格中的每一行執行一次，檢查是否存在任何訂單總價超過 50 萬的記錄。exists 關鍵字在這裡扮演了重要的角色，它能夠有效地判斷子查詢是否傳回任何結果，一旦找到符合條件的記錄，子查詢就會立即停止執行，從而提高查詢效率。
資料更新與刪除的利器：相關子查詢在資料維護中的應用
除了在 select 陳述式中使用外，相關子查詢在 update 和 delete 陳述式中也同樣非常有用。例如，假設你的 IT 部門制定了一項政策，要求移除 Customer 表格中所有五年內沒有下過訂單的客戶記錄。這時，你可以使用以下陳述式來實作：
delete from customer c
where not exists
(select 1 from orders o
where o.o_custkey = c.c_custkey
and o.o_orderdate > dateadd(year, -5, current_date));
這段程式碼使用了 not exists 關鍵字，它會尋找 Customer 表格中，在 Orders 表格中沒有任何訂單日期大於當前日期減去五年的記錄。
如果政策改為將客戶記錄標記為非活躍狀態，而不是直接刪除記錄，你可以將上述陳述式修改為 update 陳述式：
update customer c
set inactive = 'Y'
where not exists
(select 1 from orders o
where o.o_custkey = c.c_custkey
and o.o_orderdate > dateadd(year, -5, current_date));
為了更好地理解這個概念，玄貓再提供一個範例，假設我們想要將 Employee 表格中，在 Person 表格中找不到對應記錄的員工標記為非活躍狀態。首先，我們需要在 Employee 表格中新增一個名為 inactive 的欄位：
alter table employee add inactive varchar(1);
然後，我們可以執行以下 update 陳述式：
update employee e set e.inactive = 'Y'
where not exists
(select 1 from person p
where p.first_name || ' ' || p.last_name = e.emp_name);
在這個範例中，如果 Person 表格中找不到與 Employee 表格中 emp_name 欄位比對的記錄，inactive 欄位就會被設定為 Y。
子查詢作為資料來源：解放資料檢索的靈活性
子查詢不僅可以作為條件使用，還可以作為資料來源，在 from 子句中替代表格。這種用法能夠讓我們更靈活地組織和處理資料。
表格由多行多列組成，而子查詢傳回的結果集也同樣由多行多列組成。因此，表格和子查詢都可以用在查詢的 from 子句中，甚至可以相互連線。
以下是一個範例，展示瞭如何將一個用於分組資料的子查詢連線到另一個表格，以檢索額外的資訊：
select c.c_name, o.total_dollars
from
(select o_custkey, sum(o_totalprice) as total_dollars
from orders
where 1998 = date_part(year, o_orderdate)
group by o_custkey
having sum(o_totalprice) >= 650000
) o
inner join customer c
on c.c_custkey = o.o_custkey
order by 1;
在這個範例中，子查詢被賦予了別名 o，然後使用 custkey 欄位連線到 Customer 表格。子查詢傳回了所有在 1998 年訂單總額達到或超過 65 萬的客戶，然後將結果集連線到 Customer 表格，以便傳回客戶名稱，而不是 custkey 值。
提升程式碼可讀性：善用 Common Table Expressions (CTE)
除了將子查詢放在 from 子句中，你還可以選擇將子查詢移到 with 子句中。with 子句必須始終出現在查詢的頂部，也就是 select 子句的上方。
以下是使用 with 子句重寫上述查詢的範例：
with big_orders as
(select o_custkey, sum(o_totalprice) as total_dollars
from orders
where 1998 = date_part(year, o_orderdate)
group by o_custkey
having sum(o_totalprice) >= 650000
)
select c.c_name, big_orders.total_dollars
from big_orders
inner join customer c
on c.c_custkey = big_orders.o_custkey
order by 1;
在這個範例中，我們使用 with 子句定義了一個名為 big_orders 的 CTE，它包含了所有在 1998 年訂單總額達到或超過 65 萬的客戶。然後，我們可以在後續的查詢中直接使用這個 CTE，從而提高程式碼的可讀性和可維護性。
總而言之，子查詢是一種非常強大的工具，它可以幫助我們更靈活地處理資料，實作更複雜的查詢邏輯。無論是在資料檢索、資料更新還是資料刪除方面，子查詢都能夠發揮重要的作用。
以通用表格表示式（CTE）提升SQL查詢可讀性：玄貓的實戰經驗
在資料函式庫查詢中，子查詢扮演著至關重要的角色，但過於複雜的子查詢結構往往會降低SQL陳述式的可讀性。通用表格表示式（Common Table Expressions，簡稱CTE）提供了一種更清晰、模組化的方式來組織和管理子查詢，讓SQL程式碼更易於理解和維護。身為資料函式庫工程師，玄貓在專案中大量使用CTE，以下分享一些實戰經驗。
CTE的優勢：化繁為簡
CTE 本質上是一個具名的子查詢，它允許你在一個查詢中定義一個臨時的結果集，並在後續的查詢中參照它。這種方式的主要優點包括：
- 提高可讀性： 將複雜的查詢分解為更小的、邏輯上獨立的單元，使查詢結構更清晰。
- 程式碼重用： CTE 可以在同一個查詢中被多次參照，避免重複撰寫相同的子查詢。
- 簡化遞迴查詢： CTE 支援遞迴查詢，這對於處理層級結構的資料非常有用。
CTE 實戰範例：找出年度大客戶
假設我們需要找出在 1998 年消費總額超過 650,000 美元的大客戶，並按消費金額分級。以下是使用 CTE 的解決方案：
WITH big_orders AS (
SELECT
o_custkey,
SUM(o_totalprice) AS total_dollars
FROM orders
WHERE EXTRACT(YEAR FROM o_orderdate) = 1998
GROUP BY o_custkey
HAVING SUM(o_totalprice) >= 650000
),
big_orders_with_names AS (
SELECT
c.c_name,
big_orders.total_dollars
FROM big_orders
INNER JOIN customer c ON c.c_custkey = big_orders.o_custkey
),
dollar_ranges AS (
SELECT *
FROM (
VALUES
(3, 'Bottom Tier', 650000, 700000),
(2, 'Middle Tier', 700001, 730000),
(1, 'Top Tier', 730001, 9999999)
) AS dr (range_num, range_name, low_val, high_val)
)
SELECT
dr.range_name,
SUM(ROUND(bon.total_dollars, 0)) AS rng_sum,
LISTAGG(bon.c_name, ',') WITHIN GROUP (ORDER BY bon.c_name) AS name_list
FROM big_orders_with_names AS bon
INNER JOIN dollar_ranges AS dr ON bon.total_dollars BETWEEN dr.low_val AND dr.high_val
GROUP BY dr.range_name;
程式碼解密：
big_ordersCTE：- 從
orders表格中選取資料。 o_custkey：客戶的 ID。SUM(o_totalprice) AS total_dollars：計算每個客戶的總消費金額，並將其命名為total_dollars。WHERE EXTRACT(YEAR FROM o_orderdate) = 1998：篩選出 1998 年的訂單。這裡使用EXTRACT函式提取訂單日期的年份。GROUP BY o_custkey：按照客戶 ID 分組，以便計算每個客戶的總消費金額。HAVING SUM(o_totalprice) >= 650000：篩選出總消費金額大於或等於 650000 的客戶。
- 從
big_orders_with_namesCTE：- 將
big_ordersCTE 的結果與customer表格連線起來，以取得客戶的姓名。 c.c_name：客戶的姓名。big_orders.total_dollars：從big_ordersCTE 中取得的客戶總消費金額。INNER JOIN customer c ON c.c_custkey = big_orders.o_custkey：使用客戶 ID (c.c_custkey和big_orders.o_custkey) 進行內連線。
- 將
dollar_rangesCTE：- 建立一個包含消費金額範圍的臨時表格。
range_num：範圍的編號。range_name：範圍的名稱（例如，“Bottom Tier”, “Middle Tier”, “Top Tier”）。low_val：範圍的下限值。high_val：範圍的上限值。
- 最終的
SELECT陳述式：- 將
big_orders_with_namesCTE 的結果與dollar_rangesCTE 連線起來，以將客戶按照消費金額分級。 dr.range_name：消費金額範圍的名稱。SUM(ROUND(bon.total_dollars, 0)) AS rng_sum：計算每個消費金額範圍的總消費金額，並將其命名為rng_sum。ROUND(bon.total_dollars, 0)用於將消費金額四捨五入到最接近的整數。LISTAGG(bon.c_name, ',') WITHIN GROUP (ORDER BY bon.c_name) AS name_list：將每個消費金額範圍內的客戶姓名合併成一個以逗號分隔的字串，並將其命名為name_list。LISTAGG函式用於字串聚合。WITHIN GROUP (ORDER BY bon.c_name)指定客戶姓名按照字母順序排列。FROM big_orders_with_names AS bon INNER JOIN dollar_ranges AS dr ON bon.total_dollars BETWEEN dr.low_val AND dr.high_val：使用big_orders_with_namesCTE (別名為bon) 和dollar_rangesCTE (別名為dr) 進行內連線。連線條件是bon.total_dollars落在dr.low_val和dr.high_val之間。GROUP BY dr.range_name：按照消費金額範圍的名稱分組，以便計算每個範圍的總消費金額和合併客戶姓名。
- 將
這個查詢首先使用 big_orders CTE 找出 1998 年消費總額超過 650,000 美元的大客戶。然後，big_orders_with_names CTE 將這些客戶的姓名加入結果集中。最後，它使用 dollar_ranges CTE 將客戶分為不同的消費層級，並計算每個層級的總消費金額和客戶列表。
CTE 的其他應用場景
除了上述範例，CTE 還可以應用於以下場景：
- 遞迴查詢： 處理組織結構、產品分類別等層級資料。
- 資料轉換： 在複雜的資料轉換過程中，使用 CTE 可以將轉換邏輯分解為更小的步驟。
- 報表生成： CTE 可以用於生成各種報表，例如銷售報表、客戶報表等。
玄貓的經驗分享
- 善用 CTE 提高程式碼可讀性： 當查詢邏輯變得複雜時，不要猶豫使用 CTE。
- 為 CTE 選擇有意義的名稱： 這有助於理解 CTE 的作用。
- 避免過度使用 CTE： 過多的 CTE 可能會降低查詢效能。
- 注意 CTE 的作用範圍： CTE 只在定義它的查詢中有效。
總之，CTE 是一個強大的 SQL 工具，它可以提高查詢的可讀性、可維護性和程式碼重用性。身為技術專家，玄貓建議大家在實際專案中積極使用 CTE，提升 SQL 程式碼的品質。
Snowflake 中 FROM 子句的進階應用：階層式查詢與更多技巧
在 Snowflake SQL 中，FROM 子句不僅僅是用於指定資料來源。除了在第三章討論過的各種 JOIN 型別（INNER、OUTER、CROSS）之外，FROM 子句還能實作更多進階功能。本文將探討這些選項，特別是階層式查詢，並提供實際範例。
範例資料表：Employee
為了演示這些功能，我們將使用 Employee 資料表。如果您的環境中還沒有這個資料表，可以使用以下語法建立：
create table employee (
empid number,
emp_name varchar(30),
mgr_empid number
) as
select *
from (
values
(1001, 'Bob Smith', null),
(1002, 'Susan Jackson', 1001),
(1003, 'Greg Carpenter', 1001),
(1004, 'Robert Butler', 1002),
(1005, 'Kim Josephs', 1003),
(1006, 'John Tyler', 1004)
);
這個資料表包含員工 ID（empid）、員工姓名（emp_name）以及其經理的員工 ID（mgr_empid）。
階層式查詢：CONNECT BY 子句
某些資料具有階層結構，例如家族樹或組織結構。在 Employee 資料表中，每位員工的 mgr_empid 欄位指向其經理，形成一個階層關係。
傳統上，要查詢這種階層關係，可能需要多次自連線 Employee 資料表，如下所示：
select
e_1.emp_name,
e_2.emp_name,
e_3.emp_name,
e_4.emp_name
from
employee e_1
inner join employee e_2 on e_1.mgr_empid = e_2.empid
inner join employee e_3 on e_2.mgr_empid = e_3.empid
inner join employee e_4 on e_3.mgr_empid = e_4.empid
where
e_1.emp_name = 'John Tyler';
這個查詢將 Employee 資料表連線了四次，對應管理階層中的四個層級。雖然這種方法在層級不深的情況下有效，但對於大型企業中複雜的管理結構來說，它變得難以維護。
為瞭解決這個問題，Snowflake 提供了 CONNECT BY 子句，這是一種更通用與靈活的方法，用於遍歷階層關係。以下是如何使用它來產生從 John Tyler 開始的管理階層：
select
emp_name
from
employee
start with
emp_name = 'John Tyler'
connect by
prior mgr_empid = empid;
START WITH 子句指定從哪一行開始，而 CONNECT BY 子句描述如何從一行移動到下一行。PRIOR 關鍵字用於表示目前層級。在這個例子中，查詢從 John Tyler 的行開始，prior mgr_empid 的值（1004）被用來移動到 empid 為 1004 的行（Robert Butler 的行）。
這種方法的優點是它可以處理階層中的任何層級數量，因此無論有多少管理階層級，查詢都保持不變。
讓我們看看如果查詢以相反的方向執行，從 Bob Smith 開始並向下移動階層會發生什麼：
select
emp_name
from
employee
start with
emp_name = 'Bob Smith'
connect by
prior empid = mgr_empid;
這次，Bob Smith 的行是起點，並且 CONNECT BY 子句被反轉，因為我們正在向下而不是向上遍歷樹狀結構。由於每位員工最終都向 Bob Smith 報告（包括他自己），因此列出了所有六名員工。
但是，結果沒有顯示任何中間關係，例如 Robert Butler 向 Susan Jackson 報告的事實。如果想要檢視這些關係，可以使用內建函式 SYS_CONNECT_BY_PATH() 來檢視到目前為止整個階層的描述：
select
emp_name,
sys_connect_by_path(emp_name, ' : ') management_path
from
employee
start with
emp_name = 'Bob Smith'
connect by
prior empid = mgr_empid;
在這個查詢中，SYS_CONNECT_BY_PATH() 函式會建立一個路徑，顯示從 Bob Smith 到每位員工的完整管理鏈。分隔符號 ' : ' 用於分隔路徑中的每個名稱。
Snowflake 時間旅行：回到過去的資料狀態
在資料分析中，有時我們需要檢視過去某個時間點的資料狀態。Snowflake 提供的 Time Travel 功能，讓我們可以輕鬆地執行查詢，檢視過去特定時間的資料。你可以使用 at 關鍵字，指定一個特定的時間或從目前時間的偏移量，Snowflake 就會檢索出當時的資料。
為了示範這個功能，玄貓先在 Employee 資料表中新增一列資料：
PUBLIC>insert into employee (empid, emp_name, mgr_empid)
values (9999, 'Tim Traveler',1006);
新增 Tim Traveler 後，Employee 資料表現在有七列資料：
PUBLIC>select empid, emp_name, mgr_empid
from employee;
+-------+----------------+-----------+
| EMPID | EMP_NAME | MGR_EMPID |
|-------+----------------+-----------|
| 1001 | Bob Smith | NULL |
| 1002 | Susan Jackson | 1001 |
| 1003 | Greg Carpenter | 1001 |
| 1004 | Robert Butler | 1002 |
| 1005 | Kim Josephs | 1003 |
| 1006 | John Tyler | 1004 |
| 9999 | Tim Traveler | 1006 |
+-------+----------------+-----------+
現在，玄貓可以使用 Time Travel，檢索一小時前的所有資料列：
PUBLIC>select empid, emp_name, mgr_empid
from employee
at(offset => -3600);
+-------+----------------+-----------+
| EMPID | EMP_NAME | MGR_EMPID |
|-------+----------------+-----------|
| 1001 | Bob Smith | NULL |
| 1002 | Susan Jackson | 1001 |
| 1003 | Greg Carpenter | 1001 |
| 1004 | Robert Butler | 1002 |
| 1005 | Kim Josephs | 1003 |
| 1006 | John Tyler | 1004 |
+-------+----------------+-----------+
這個查詢指定傳回一小時前（-3600 秒）的資料表狀態。如你所見，Tim Traveler 並不在結果集中。
Time Travel 的一個用途是識別在特定時間範圍內插入的資料列。下一個查詢使用 minus 運算元，比較資料表目前狀態與一小時前的狀態：
PUBLIC>select empid, emp_name, mgr_empid
from employee
minus
select empid, emp_name, mgr_empid
from employee
at(offset => -3600);
+-------+--------------+-----------+
| EMPID | EMP_NAME | MGR_EMPID |
|-------+--------------+-----------|
| 9999 | Tim Traveler | 1006 |
+-------+--------------+-----------+
這個查詢比較了目前資料表中的七列資料與一小時前的六列資料，並傳回差異，也就是 Tim Traveler 的資料列。Time Travel 預設可以查詢一天前的資料，但如果你使用的是 Snowflake Enterprise 版本，則可以查詢最多 90 天前的資料狀態。
###樞紐分析查詢：將資料列轉換為欄位
樞紐分析（Pivoting）是資料分析中常見的操作，它將資料列轉換為欄位。為了說明這一點，讓玄貓回到 Orders 資料表，撰寫一個查詢，計算 1995 年之後每年的總銷售額：
PUBLIC>select date_part(year, o_orderdate) as year,
round(sum(o_totalprice)) as total_sales
from orders
where 1995 <= date_part(year, o_orderdate)
group by date_part(year, o_orderdate)
order by 1;
+------+-------------+
| YEAR | TOTAL_SALES |
|------+-------------|
| 1995 | 3317521810 |
| 1996 | 3296373353 |
| 1997 | 3255086721 |
| 1998 | 1925196449 |
+------+-------------+
這個結果集包含四列資料，每列資料包含年份和該年的總銷售額。現在，假設你需要將這些結果格式化為一份報表，在一列中顯示這些資料，使用四個欄位分別顯示 1995 年、1996 年、1997 年和 1998 年的總銷售額：
+------------+------------+------------+------------+
| 1995_SALES | 1996_SALES | 1997_SALES | 1998_SALES |
+------------+------------+------------+------------+
| 3317521810 | 3296373353 | 3255086721 | 1925196449 |
+------------+------------+------------+------------+
對於這種型別的操作，Snowflake 提供了 pivot 子句，讓你指定希望如何呈現資料。以下是一個使用 pivot 產生所需結果的查詢：
PUBLIC>select round(yr_1995) as "1995_sales",
round(yr_1996) as "1996_sales",
round(yr_1997) as "1997_sales",
round(yr_1998) as "1998_sales"
from (select date_part(year, o_orderdate) as year,
o_totalprice
from orders
where 1995 <= date_part(year, o_orderdate)
)
pivot (sum(o_totalprice)
for year in (1995,1996,1997,1998))
as pvt(yr_1995, yr_1996, yr_1997, yr_1998);
+------------+------------+------------+------------+
| 1995_sales | 1996_sales | 1997_sales | 1998_sales |
+------------+------------+------------+------------+
| 3317521810 | 3296373353 | 3255086721 | 1925196449 |
+------------+------------+------------+------------+
from 子句中的子查詢檢索 1995 年及以後所有訂單的年份和總價，而 pivot 子句指定將 totalprice 值加總到四個欄位中，每個欄位分別對應 1995 年、1996 年、1997 年和 1998 年。pivot 子句中的第三行將欄位名稱分配給每個欄位 (yr_1995, …, yr_1998)，然後在 select 子句中使用這些欄位名稱來捨去每個值。
Snowflake 還提供了一個 unpivot 子句，它執行相反的轉換（將資料從欄位樞紐分析為資料列）。為了示範，玄貓將採用先前的樞紐分析查詢，將其放入一個 with 子句中，然後使用一個帶有 unpivot 的查詢將結果集還原到其原始狀態：
PUBLIC>with year_pvt as
(select round(yr_1995) as "1995",
round(yr_1996) as "1996",
round(yr_1997) as "1997",
round(yr_1998) as "1998"
from (select date_part(year, o_orderdate) as year,
o_totalprice
from orders
where 1995 <= date_part(year, o_orderdate)
)
pivot (sum(o_totalprice)
for year in (1995,1996,1997,1998))
### 資料透視的逆向操作：使用 UNPIVOT 轉換資料
在資料分析中，我們經常需要轉換資料的呈現方式。有時候，我們需要將寬表（wide table）轉換成長表（long table），這時就可以使用 `UNPIVOT`。簡單來說，`UNPIVOT` 就是 `PIVOT` 的反向操作。
以下是一個範例，展示如何使用 `UNPIVOT` 將年度銷售資料從寬格式轉換成長格式：
```sql
with year_data as (
select
3317521810 as yr_1995,
3296373353 as yr_1996,
3255086721 as yr_1997,
1925196449 as yr_1998
),
year_pvt as (
select *
from year_data
)
select *
from year_pvt
unpivot (total_sales for year in
("1995", "1996", "1997", "1998"));
這段程式碼首先建立一個名為 year_data 的 Common Table Expression (CTE)，其中包含 1995 年至 1998 年的銷售額。然後，它使用 UNPIVOT 運算元將這些年度銷售額轉換為長格式，其中 year 欄位包含年份，total_sales 欄位包含對應的銷售額。
程式碼解密
- WITH year_data AS (…): 定義一個 CTE，名為
year_data，用於模擬包含年度銷售資料的表格。 - SELECT 3317521810 AS yr_1995, …: 在
year_dataCTE 中，選取四個年度（1995、1996、1997、1998）的銷售額，並將它們分別命名為yr_1995、yr_1996、yr_1997和yr_1998。 - WITH year_pvt AS (…): 定義另一個 CTE，名為
year_pvt，它簡單地選取year_dataCTE 中的所有資料。 - SELECT * FROM year_pvt UNPIVOT (…): 從
year_pvtCTE 中選取所有資料，並應用UNPIVOT運算元。 - (total_sales FOR year IN (“1995”, “1996”, “1997”, “1998”)): 定義
UNPIVOT運算元的行為：total_sales：指定新的欄位，用於存放從原始欄位（yr_1995、yr_1996等）提取的值。year：指定新的欄位，用於存放原始欄位的名稱（例如 “1995”、“1996”）。IN ("1995", "1996", "1997", "1998")：列出要進行UNPIVOT操作的原始欄位名稱。
在 year_pvt 子查詢中，玄貓使用了 pivot 將結果集更改為具有四個欄位的單一列，在外部查詢中，玄貓使用了 unpivot 將結果集更改回具有兩列的四列。在後續章節中，玄貓將向您展示另一種使用 case 表示式透視資料的方法。
隨機抽樣：取得資料子集的技巧
在測試或分析時，我們有時需要從大型資料集中取得一個隨機子集。Snowflake 提供了 SAMPLE 子句，讓您可以輕鬆地指定要傳回的資料列百分比。
例如，假設 Supplier 表格有 7,400 筆資料列，但您只想傳回 1/1,000 的資料列來執行一些測試。以下是如何使用 SAMPLE 子句來實作這一點（您的結果可能會有所不同）：
select s_suppkey, s_name, s_nationkey
from supplier
sample (0.1);
在這個範例中，0.1 指定了機率，這表示任何特定資料列都有 0.1% 的機率被包含在結果集中。如果您多次執行此查詢，您將每次都獲得一組不同的資料列（以及可能不同的資料列數）。玄貓執行此陳述式五次，結果集包含少至四列，多至九列。如果您需要確切的資料列數，您可以指定資料列計數：
select s_suppkey, s_name, s_nationkey
from supplier
sample (10 rows);
程式碼解密
- SELECT s_suppkey, s_name, s_nationkey: 指定要從
supplier表格中選取的欄位。 - FROM supplier: 指定要從中選取資料的表格。
- SAMPLE (10 ROWS): 使用
SAMPLE子句來選取隨機樣本。10 ROWS表示要選取 10 列資料。
對於此變體，您的結果集將始終包含 10 筆資料列，但每次執行查詢時都會傳回一組不同的資料列。
完整外部聯結：包含所有資料列
在資料函式庫中，OUTER JOIN 用於從多個表格中檢索資料，即使聯結條件不成立。FULL OUTER JOIN 是一種特殊的 OUTER JOIN，它會傳回所有表格中的所有資料列，無論聯結條件是否成立。
讓玄貓看一個範例。假設您有兩個表格：orders 和 customer。orders 表格包含訂單編號和客戶金鑰，customer 表格包含客戶金鑰和客戶名稱。
select orders.ordernum, orders.custkey, customer.custname
from
(values (990, 101), (991, 102),
(992, 101), (993, 104))
as orders (ordernum, custkey)
left outer join
(values (101, 'BOB'), (102, 'KIM'), (103, 'JIM'))
as customer (custkey, custname)
on orders.custkey = customer.custkey;
這個查詢使用 LEFT OUTER JOIN 來聯結 orders 和 customer 表格。結果集包含 orders 表格中的所有資料列，以及 customer 表格中與 orders 表格中的資料列相符的資料列。如果 customer 表格中沒有與 orders 表格中的資料列相符的資料列，則結果集中的 customer 欄位將為 NULL。
在此範例中，結果集包含來自訂單資料集的第四列（訂單編號 993），但 custname 欄位為 null，因為客戶資料集中沒有客戶金鑰 104 的資料列。此範例包含沒有相符客戶的訂單，但也有沒有任何相符訂單的客戶 (Jim)。如果您希望結果集同時包含每個訂單和每個客戶，您可以指定 FULL OUTER JOIN 而不是 LEFT OUTER JOIN：
select orders.ordernum, orders.custkey, customer.custname
from
(values (990, 101), (991, 102),
(992, 101), (993, 104))
as orders (ordernum, custkey)
full outer join
(values (101, 'BOB'), (102, 'KIM'), (103, 'JIM'))
as customer (custkey, custname)
on orders.custkey = customer.custkey;
程式碼解密
- SELECT orders.ordernum, orders.custkey, customer.custname: 指定要選取的欄位，包括訂單編號、客戶金鑰和客戶名稱。
- FROM (VALUES (990, 101), (991, 102), (992, 101), (993, 104)) AS orders (ordernum, custkey): 使用
VALUES子句建立一個名為orders的虛擬表格，其中包含訂單編號和客戶金鑰。 - FULL OUTER JOIN (VALUES (101, ‘BOB’), (102, ‘KIM’), (103, ‘JIM’)) AS customer (custkey, custname): 使用
FULL OUTER JOIN將orders表格與另一個使用VALUES子句建立的虛擬表格customer聯結，其中包含客戶金鑰和客戶名稱。 - ON orders.custkey = customer.custkey: 指定聯結條件，即
orders表格中的custkey欄位必須等於customer表格中的custkey欄位。
結果集現在包含 Jim 的額外資料列，因此包含所有四個訂單和所有三個客戶。但是，結果集中 Jim 的 custkey 值為 null，因為 orders.custkey 在 select 子句中指定。這可以使用 nvl() 函式修正，以從訂單或客戶資料集傳回 custkey 值：
select orders.ordernum,
nvl(orders.custkey, customer.custkey) as custkey,
customer.custname
from
(values (990, 101), (991, 102),
(992, 101), (993, 104))
as orders (ordernum, custkey)
full outer join
(values (101, 'BOB'), (102, 'KIM'), (103, 'JIM'))
as customer (custkey, custname)
on orders.custkey = customer.custkey;
程式碼解密
- nvl(orders.custkey, customer.custkey) AS custkey: 使用
nvl()函式來處理FULL OUTER JOIN中可能出現的NULL值。如果orders.custkey為NULL，則使用customer.custkey的值；否則，使用orders.custkey的值。
探索 SQL 的 FROM 子句：進階應用與實戰演練
FROM 子句是 SQL 查詢的根本，但它不僅僅是用於指定資料表。玄貓（BlackCat）將帶領大家深入探索 FROM 子句的各種進階用法，從階層式查詢到樞紐分析，再到 Snowflake 的獨特 Time Travel 功能。
挑戰一：樞紐分析的實作
先前的查詢顯示了各市場區隔的客戶數量：
PUBLIC> SELECT c_mktsegment AS mktseg, COUNT(*) tot_custs
FROM customer
GROUP BY c_mktsegment;
現在，我們將以此為基礎，建立一個樞紐查詢，將每個市場區隔轉換為單獨的欄位。
解法：
樞紐分析在 SQL 中通常需要使用 CASE 語法來實作。以下是一種可能的解決方案：
SELECT
SUM(CASE WHEN c_mktsegment = 'AUTOMOBILE' THEN 1 ELSE 0 END) AS AUTOMOBILE,
SUM(CASE WHEN c_mktsegment = 'MACHINERY' THEN 1 ELSE 0 END) AS MACHINERY,
SUM(CASE WHEN c_mktsegment = 'BUILDING' THEN 1 ELSE 0 END) AS BUILDING,
SUM(CASE WHEN c_mktsegment = 'FURNITURE' THEN 1 ELSE 0 END) AS FURNITURE,
SUM(CASE WHEN c_mktsegment = 'HOUSEHOLD' THEN 1 ELSE 0 END) AS HOUSEHOLD
FROM customer;
內容解密：
CASE WHEN c_mktsegment = 'AUTOMOBILE' THEN 1 ELSE 0 END: 這段程式碼會檢查c_mktsegment欄位是否為'AUTOMOBILE'。如果是，則傳回 1，否則傳回 0。SUM(...): 將每個CASE表示式傳回的 1 和 0 加總，從而計算出每個市場區隔的客戶數量。AS AUTOMOBILE: 為結果欄位指定別名，使其更具可讀性。
挑戰二：橫向 Join 的應用
以下查詢統計了各國家的供應商數量：
PUBLIC> SELECT s_nationkey, COUNT(*) AS supplier_count
FROM supplier
GROUP BY s_nationkey;
現在，我們將使用橫向 JOIN 將 Nation.n_name 欄位加入查詢結果中。
解法：
橫向 JOIN 允許我們在 FROM 子句中使用子查詢，並將其結果與主查詢的每一行進行關聯。
SELECT
s.s_nationkey,
COUNT(*) AS supplier_count,
n.n_name
FROM
supplier s
LEFT JOIN
nation n ON s.s_nationkey = n.n_nationkey
GROUP BY
s.s_nationkey, n.n_name;
內容解密：
LEFT JOIN nation n ON s.s_nationkey = n.n_nationkey: 這段程式碼將supplier表格和nation表格進行LEFT JOIN，使用s_nationkey和n_nationkey欄位作為連線條件。LEFT JOIN確保即使供應商沒有對應的國家，也能傳回供應商的資訊。GROUP BY s.s_nationkey, n.n_name: 按照國家鍵和國家名稱進行分組，以便計算每個國家的供應商數量。
挑戰三：使用階層式查詢追蹤家族譜系
Smith_History 表格包含了 Smith 家族的譜系資訊。我們要使用 sys_connect_by_path() 函式來追蹤家族樹，從 Thomas 開始，顯示完整的祖先路徑。
解法：
WITH smith_history AS (
SELECT person_id, name, father_person_id
FROM (
VALUES
(1, 'Thomas', NULL),
(2, 'Clara', NULL),
(3, 'Samuel', 1),
(4, 'Charles', 1),
(5, 'Beth', 3),
(6, 'Steven', 4),
(7, 'Sarah', 6),
(8, 'Robert', 4),
(9, 'Dorothy', 8),
(10, 'George', 8)
) AS smith(person_id, name, father_person_id)
)
SELECT
person_id,
name,
SYS_CONNECT_BY_PATH(name, '/') AS ancestry_path
FROM smith_history
START WITH father_person_id IS NULL
CONNECT BY PRIOR person_id = father_person_id;
內容解密：
WITH smith_history AS (...): 定義一個 Common Table Expression (CTE)，名為smith_history，用於儲存家族譜系資料。SYS_CONNECT_BY_PATH(name, '/'): 這個函式用於建立從根節點到目前節點的路徑，使用/作為分隔符號。START WITH father_person_id IS NULL: 指定查詢的起始節點，這裡選擇father_person_id為NULL的人，即 Thomas 和 Clara。CONNECT BY PRIOR person_id = father_person_id: 定義節點之間的連線關係，PRIOR person_id表示父節點的person_id，father_person_id表示子節點的father_person_id。
SQL 條件邏輯：CASE 表示式詳解
在 SQL 中，條件邏輯允許查詢根據特定條件執行不同的操作。CASE 表示式是實作條件邏輯的主要工具，類別似於其他程式語言中的 if...then...else 陳述式。
CASE 表示式基礎
CASE 表示式可以出現在 SELECT、INSERT、UPDATE 和 DELETE 陳述式的各個子句中。以下是一個簡單的例子：
PUBLIC> SELECT c_custkey, c_name, c_acctbal,
CASE
WHEN c_acctbal < 0 THEN 'generate refund'
WHEN c_acctbal = 0 THEN 'no action'
ELSE 'send bill'
END AS month_end_action
FROM customer
LIMIT 15;
內容解密：
CASE: 標記CASE表示式的開始。WHEN c_acctbal < 0 THEN 'generate refund': 如果c_acctbal小於 0，則傳回'generate refund'。WHEN c_acctbal = 0 THEN 'no action': 如果c_acctbal等於 0，則傳回'no action'。ELSE 'send bill': 如果以上條件都不滿足，則傳回'send bill'。END AS month_end_action: 標記CASE表示式的結束，並為結果欄位指定別名。
多重條件判斷
CASE 表示式可以包含多個 WHEN 子句，條件會依序評估，直到找到第一個為真的條件。
PUBLIC> SELECT num.val,
CASE
WHEN num.val > 90 THEN 'huge number'
WHEN num.val > 50 THEN 'really big number'
WHEN num.val > 20 THEN 'big number'
WHEN num.val > 10 THEN 'medium number'
WHEN num.val <= 10 THEN 'small number'
END AS num_size
FROM (VALUES (11), (12), (25), (99), (3)) AS num (val);
內容解密：
- 條件依序評估，例如，當
num.val為 99 時，雖然多個條件都為真，但只會傳回第一個符合的結果'huge number'。
總的來說，FROM 子句和 CASE 表示式是 SQL 中非常強大的工具，可以幫助我們更靈活地查詢和處理資料。透過掌握這些進階技巧，玄貓（BlackCat）相信大家可以寫出更有效率和更具表達力的 SQL 程式碼。
SQL Case語法：解鎖資料分析的無限可能
在資料函式庫查詢中，CASE 語法猶如瑞士刀，賦予我們根據不同條件進行資料轉換和分析的強大能力。玄貓將帶領大家深入探索 CASE 語法的奧妙，讓你輕鬆駕馭各種複雜的資料處理任務。
探索Case語法：兩種不同的表達方式
CASE 語法主要分為兩種：搜尋式 Case 和 簡單 Case，它們各有千秋，適用於不同的場景。
搜尋式 Case：靈活的條件判斷
搜尋式 CASE 語法提供極高的靈活性，允許我們使用多個 WHEN 子句來定義不同的條件，並在滿足條件時傳回相應的表示式。其基本語法如下：
CASE
WHEN condition1 THEN expression1
WHEN condition2 THEN expression2
...
WHEN conditionN THEN expressionN
[ELSE expressionZ]
END
如果所有 WHEN 子句的條件都不成立，ELSE 子句（可選）將提供一個預設傳回值。更棒的是，CASE 語法支援傳回各種資料型別，甚至可以是子查詢的結果！
舉個例子，假設我們想根據零件的零售價格來判斷訂單數量：
SELECT
p_partkey,
p_retailprice,
CASE
WHEN p_retailprice > 2000 THEN
(SELECT COUNT(*)
FROM lineitem li
WHERE li.l_partkey = p.p_partkey)
ELSE 0
END AS num_bigticket_orders
FROM part p
WHERE p_retailprice BETWEEN 1990 AND 2010;
這段程式碼會檢查 part 資料表中零件的零售價格，如果價格超過 $2000，則透過子查詢計算該零件在 lineitem 資料表中的訂購次數；否則，傳回 0。
簡單 Case：精簡的等值判斷
相較於搜尋式 CASE，簡單 CASE 語法更注重簡潔，它將一個表示式的值與一系列的值進行比較，如果找到比對項，則傳回相應的表示式。其語法如下：
CASE expression
WHEN value1 THEN expression1
WHEN value2 THEN expression2
...
WHEN valueN THEN expressionN
[ELSE expressionZ]
END
簡單 CASE 語法適用於等值判斷的場景，例如將訂單狀態程式碼轉換為更易讀的文字描述：
SELECT
o_orderkey,
CASE o_orderstatus
WHEN 'P' THEN 'Partial'
WHEN 'F' THEN 'Filled'
WHEN 'O' THEN 'Open'
END AS status
FROM orders
LIMIT 20;
在這個例子中，我們將 orders 資料表中的 o_orderstatus 欄位的值，根據不同的程式碼轉換為 ‘Partial’、‘Filled’ 或 ‘Open’。
Case語法的妙用：資料分析例項
CASE 語法在資料分析中用途廣泛，以下玄貓列舉幾個常見的應用場景：
樞紐分析：將資料從列轉為欄
有時候，我們需要將資料從多列轉換為單列的多個欄位，這就是所謂的樞紐分析。雖然 Snowflake 提供了 PIVOT 語法，但我們也可以使用 CASE 語法來實作相同的效果。
例如，假設我們想將每年訂單的總價格彙總到單一行中：
SELECT
ROUND(SUM(CASE WHEN 1995 = DATE_PART(YEAR, o_orderdate) THEN o_totalprice ELSE 0 END)) AS "1995",
ROUND(SUM(CASE WHEN 1996 = DATE_PART(YEAR, o_orderdate) THEN o_totalprice ELSE 0 END)) AS "1996",
ROUND(SUM(CASE WHEN 1997 = DATE_PART(YEAR, o_orderdate) THEN o_totalprice ELSE 0 END)) AS "1997",
ROUND(SUM(CASE WHEN 1998 = DATE_PART(YEAR, o_orderdate) THEN o_totalprice ELSE 0 END)) AS "1998"
FROM orders
WHERE DATE_PART(YEAR, o_orderdate) >= 1995;
這段程式碼使用 CASE 語法，根據訂單日期將總價格分配到不同的欄位中，從而實作了樞紐分析的效果。由於每個欄位都使用了彙總函式 SUM()，因此不需要使用 GROUP BY 子句。
存在性檢查：判斷特定關係是否存在
有時候，我們只需要知道某種關係是否存在，而不需要關心發生的次數。例如，我們想知道是否有任何客戶下過超過 $400,000 的訂單，可以使用 CASE 語法結合相關子查詢來實作：
SELECT
c_name,
CASE
WHEN EXISTS (SELECT 1
FROM orders o
WHERE o.o_custkey = c.c_custkey
AND o.o_totalprice > 400000)
THEN 'Yes'
ELSE 'No'
END AS has_big_order
FROM customer c;
這段程式碼會檢查每個客戶是否有下過超過 $400,000 的訂單，如果存在，則傳回 ‘Yes’；否則，傳回 ‘No’。
玄貓結語
CASE 語法是 SQL 中不可或缺的一部分，它賦予我們根據不同條件進行資料轉換和分析的能力。無論是簡單的等值判斷，還是複雜的條件邏輯，CASE 語法都能幫助我們輕鬆應對。掌握 CASE 語法，將為你的資料分析之路開啟無限可能。
