返回文章列表
玄貓（BlackCat）

SQL 子查詢與層級查詢應用

本文探討 SQL 子查詢的多種應用場景，包含多欄位子查詢、相關子查詢以及 EXISTS 運算元。同時也介紹了通用表表達式（CTE）的用法，如何簡化複雜查詢的撰寫並提升可讀性。此外，文章也涵蓋了 Snowflake 中層級查詢的技巧，使用 CONNECT BY

資料函式庫 SQL
SQL 子查詢 CTE 層級查詢 CONNECT BY Snowflake

SQL 子查詢在處理複雜查詢邏輯時扮演著至關重要的角色，能有效地分解問題並提高程式碼的可讀性。多欄位子查詢允許傳回多個欄位值，方便進行更精確的資料篩選。相關子查詢則根據外部查詢的結果動態調整執行條件，實作更靈活的查詢邏輯。EXISTS 運算元則專注於判斷子查詢結果是否存在，適用於判斷特定條件是否滿足的場景。此外，通用表表達式（CTE）的引入，能有效簡化複雜 SQL 查詢的結構，提高程式碼的可維護性。對於具有層級關係的資料，Snowflake 提供的 CONNECT BY 子句能有效地進行層級遍歷，並透過 SYS_CONNECT_BY_PATH 函式顯示完整的層級路徑，方便資料的分析與理解。

子查詢型別

子查詢是SQL中一個強大的工具，能夠幫助我們處理複雜的查詢需求。在本章中，我們將探討不同型別的子查詢及其應用。

多欄位子查詢

到目前為止，我們所看到的子查詢範例都只傳回一個欄位。然而，在某些情況下，我們可以使用傳回兩個或更多欄位的子查詢。讓我們先來看看一個查詢，它能夠找出每年最大的訂單：

select date_part(year, o_orderdate), max(o_totalprice)
from orders
group by date_part(year, o_orderdate)
order by 1;

內容解密：

  • date_part(year, o_orderdate) 用於提取訂單日期的年份。
  • max(o_totalprice) 用於計算每年的最大訂單總價。
  • group by date_part(year, o_orderdate) 根據年份對訂單進行分組。
  • order by 1 根據第一個欄位（年份）進行排序。

接下來，假設我們想要找出這些最大訂單的更多細節，例如客戶鍵值和訂單日期。為了找到這些資料，我們需要建立一個包含查詢，比較每個訂單的年份和總價與子查詢傳回的兩個欄位：

select o_custkey, o_orderdate, o_totalprice
from orders
where (date_part(year, o_orderdate), o_totalprice) in
(select date_part(year, o_orderdate), max(o_totalprice)
from orders
group by date_part(year, o_orderdate))
order by 2;

內容解密：

  • (date_part(year, o_orderdate), o_totalprice) 建立了一個由年份和總價組成的元組，用於與子查詢傳回的結果進行比較。
  • 子查詢傳回每年的最大訂單總價及其對應的年份。
  • order by 2 根據第二個欄位（訂單日期）進行排序。

相關子查詢

到目前為止，本章中的所有子查詢都與包含陳述式無關，這意味著子查詢在包含陳述式執行之前執行一次。相關子查詢則參照包含陳述式中的一個或多個欄位，這意味著子查詢和包含查詢必須一起執行。例如，下面的查詢傳回下了至少150萬美元訂單的客戶集合：

select c.c_name
from customer c
where 1500000 <=
(select sum(o.o_totalprice)
from orders o
where o.o_custkey = c.c_custkey);

內容解密：

  • 子查詢中的 where o.o_custkey = c.c_custkey 條件參照了包含查詢中的 c_custkey 欄位，這使得它成為一個相關子查詢。
  • 對於客戶表中的每一行，子查詢都會執行以計算該客戶的所有訂單總價。
  • 由於客戶表中有66,076行，因此子查詢被執行了66,076次。

Exists 運算元

相關子查詢經常與 exists 運算元一起使用，當我們想要測試某種關係的存在性而不關心數量的時候，這非常有用。例如，假設我們想要找出所有曾經下過超過50萬美元訂單的客戶，而不關心訂單數量或訂單的確切金額。下面的查詢展示瞭如何使用 exists 運算元來實作這一目的：

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);

內容解密：

  • exists 用於測試子查詢是否傳回至少一行資料。
  • 子查詢中的 select 1 只是一個佔位符，因為 exists 只關心是否有資料被傳回，而不關心資料的內容。
  • o.o_custkey = c.c_custkey 將訂單表中的客戶鍵值與客戶表中的客戶鍵值進行匹配。

相關子查詢的應用與實踐

在資料函式庫查詢中，相關子查詢（Correlated Subqueries）是一種強大的工具，能夠讓我們根據外部查詢的結果動態地調整子查詢的執行。本章節將探討相關子查詢的使用場景、語法結構及其在不同型別的SQL陳述式中的應用。

相關子查詢的基本概念

相關子查詢是指在子查詢中參照了外部查詢中的欄位。這種查詢方式使得子查詢能夠根據外部查詢的每一行資料進行動態調整，從而實作更為複雜的查詢邏輯。

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);

內容解密：

  • select c.c_name from customer c：從客戶表customer中選取客戶名稱c_name。
  • where exists (select 1 from orders o where o.o_custkey = c.c_custkey and o.o_totalprice > 500000)：使用exists來檢查客戶是否至少有一個訂單總價超過50萬。
    • exists子句中的子查詢會對每個客戶檢查其訂單記錄。
    • 當找到至少一個符合條件的訂單時，exists傳回true，客戶名稱就會被包含在結果集中。

相關子查詢在更新與刪除陳述式中的應用

相關子查詢不僅可以在select陳述式中使用，也能在update和delete陳述式中發揮作用。例如，若要刪除過去5年內未下訂單的客戶記錄，可以使用以下SQL陳述式：

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));

內容解密：

  • delete from customer c：從客戶表customer中刪除記錄。
  • 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來找出過去5年內沒有訂單的客戶。
    • 子查詢檢查客戶在過去5年內是否有訂單記錄。
    • 若無訂單記錄，則客戶記錄將被刪除。

若要將這些客戶標記為不活躍而非直接刪除，可以使用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));

內容解密：

  • update customer c set inactive = 'Y'：將客戶記錄標記為不活躍。
  • where not exists ...：條件與刪除陳述式相同，用於找出過去5年內沒有訂單的客戶。

子查詢作為資料來源

子查詢可以傳回結果集，因此可以用作from子句中的資料來源。這種用法使得複雜的查詢可以被分解為多個簡單的子查詢，提高了SQL陳述式的可讀性和維護性。

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;

內容解密：

  • 子查詢(select o_custkey, sum(o_totalprice) as total_dollars from orders ...)：計算1998年每個客戶的訂單總金額，並篩選出總金額大於或等於65萬的客戶。
  • inner join customer c on c.c_custkey = o.o_custkey：將子查詢結果與客戶表連線，取得客戶名稱。

常見表表達式（CTE）

常見表表達式（Common Table Expressions, CTE）提供了一種更清晰的方式來組織複雜的查詢。CTE可以看作是臨時結果集，能夠在查詢中被多次參照。

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 as (...)：定義了一個名為big_orders的CTE，計算1998年訂單總金額大於或等於65萬的客戶。
  • select c.c_name, big_orders.total_dollars from big_orders inner join customer c：將CTE結果與客戶表連線，取得客戶名稱和總金額。

SQL 子查詢與通用表表達式（CTE）的應用

在 SQL 查詢中，子查詢是一種強大的工具，能夠幫助我們處理複雜的資料檢索需求。本文將探討子查詢的使用方法，特別是在 WITH 子句中的應用，以及如何利用通用表表達式（CTE）來簡化查詢邏輯和提升可讀性。

子查詢的基本概念

子查詢是指巢狀在另一個 SQL 查詢中的查詢陳述式。它可以用於多種場合，例如作為資料來源、篩選條件或是計算欄位的值。子查詢可以出現在 SELECT、FROM、WHERE 和 HAVING 等子句中。

使用 WITH 子句和 CTE

WITH 子句允許我們定義一個或多個臨時結果集，這些結果集可以在主查詢中被參照。這些臨時結果集被稱為通用表表達式（CTE）。CTE 能夠提高查詢的可讀性，並且可以被多次參照，從而簡化複雜查詢的撰寫。

簡單 CTE 示例

假設我們要找出 1998 年訂單總金額大於或等於 650,000 的客戶及其訂單總金額。首先，我們可以使用 CTE 來計算每個客戶的訂單總金額：

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
),
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
)
SELECT *
FROM big_orders_with_names
ORDER BY 1;

內容解密：

  1. 第一個 CTE (big_orders)：計算每個客戶在 1998 年的訂單總金額，並篩選出總金額大於或等於 650,000 的客戶。

    • date_part(year, o_orderdate) 用於提取訂單日期的年份。
    • SUM(o_totalprice) 計算每個客戶的訂單總金額。
    • HAVING 子句用於篩選符合條件的客戶。

  2. 第二個 CTE (big_orders_with_names)：將第一個 CTE 的結果與客戶表進行內連線，以取得客戶名稱。

    • INNER JOIN 用於結合兩個表的資料，根據 c_custkey 和 o_custkey 的匹配。

  3. 主查詢：從第二個 CTE 中選擇所有欄位，並按客戶名稱排序結果。

多重 CTE 的應用

我們可以定義多個 CTE，並在主查詢中參照它們。下面是一個例子，展示瞭如何根據客戶的訂單總金額將其分為不同的範圍：

WITH 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)
),
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
),
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
)
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;

圖表說明：

此查詢的流程可以用以下 Plantuml 圖表示：

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title SQL 子查詢與層級查詢應用

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

圖表翻譯： 此圖展示了查詢的執行流程，從定義多個 CTE 到最終的主查詢和結果輸出。

深入理解SQL中的層級查詢

在處理具有層級關係的資料時，例如公司組織架構或家族樹，傳統的SQL查詢方法可能變得複雜且難以維護。本章將探討如何在Snowflake中使用CONNECT BY子句來處理這種層級資料。

層級查詢的基本概念

層級查詢主要用於處理具有上下級關係的資料。例如，在一個員工表中，每個員工都有一個對應的長官主管。利用層級查詢，我們可以輕鬆地遍歷這些關係，無論是向上查詢某員工的長官，還是向下查詢某主管下的所有員工。

使用CONNECT BY進行層級查詢

Snowflake提供了CONNECT BY子句來支援層級查詢。以下是一個基本的例子：

SELECT emp_name
FROM employee
START WITH emp_name = 'John Tyler'
CONNECT BY PRIOR mgr_empid = empid;

內容解密：

  1. START WITH子句指定了查詢的起始點，在這個例子中是員工名為’John Tyler’的那一行。
  2. CONNECT BY子句定義瞭如何從當前行移動到下一行。PRIOR關鍵字用於指代當前行的某個欄位值。在這裡，PRIOR mgr_empid = empid表示根據當前行的mgr_empid（即長官主管的ID）來找到下一行的empid（即員工ID）。
  3. 這樣，從’John Tyler’開始，查詢會沿著管理階層級向上遍歷，直到最高層。

反向遍歷層級結構

我們也可以反向進行層級查詢，即從最高層開始向下遍歷所有下屬：

SELECT emp_name
FROM employee
START WITH emp_name = 'Bob Smith'
CONNECT BY PRIOR empid = mgr_empid;

內容解密：

  1. 這次查詢從’Bob Smith’開始，因為他是最高層（沒有長官）。
  2. CONNECT BY PRIOR empid = mgr_empid表示根據當前行的員工ID來找到下一行中具有相同主管ID的員工。
  3. 這樣，查詢會列出’Bob Smith’下的所有員工，無論是直接下屬還是間接下屬。

顯示完整的層級路徑

有時候，我們不僅想知道某個員工的所有長官或下屬，還想看到完整的層級路徑。Snowflake提供了SYS_CONNECT_BY_PATH函式來實作這一點：

SELECT emp_name,
       SYS_CONNECT_BY_PATH(emp_name, ' : ') AS management_path
FROM employee
START WITH emp_name = 'Bob Smith'
CONNECT BY PRIOR empid = mgr_empid;

內容解密：

  1. SYS_CONNECT_BY_PATH函式的第一個引數是要顯示的欄位，第二個引數是分隔符。
  2. 在這個例子中，它會為每個員工顯示從’Bob Smith’到該員工的完整管理路徑，使用’ : ‘作為分隔符。
  3. 這樣，我們可以清晰地看到每個員工在組織架構中的位置。