SQL 進階查詢完全實戰指南

在現代數據驅動的商業環境中,資料庫查詢能力直接影響企業的決策效率與競爭力。無論是電商平台分析用戶行為、金融機構評估風險模型,或是製造業優化供應鏈管理,複雜的資料分析需求都仰賴精湛的 SQL 查詢技術。基礎的 SELECT、JOIN 與 WHERE 語法雖能滿足日常需求,但面對多層次資料結構、動態條件判斷與多維度分析時,往往顯得力不從心。

SQL 的進階查詢功能提供了強大的工具箱,讓資料庫工程師與分析師能夠應對各種複雜場景。子查詢機制允許在單一語句中嵌套多層查詢邏輯,實現精確的資料篩選與關聯。遞迴查詢則突破了傳統關聯式資料庫對層級結構的限制,能夠優雅地處理組織架構、產品分類或社交網路等樹狀或圖狀資料。CASE 語法將條件判斷的能力直接整合到查詢語句中,讓資料轉換與分類變得簡潔直觀。

在台灣的資料庫開發與數據分析領域,掌握這些進階技術已成為專業人士的必備能力。從銀行業的客戶分群分析到零售業的商品銷售報表,從製造業的產能規劃到科技業的用戶行為追蹤,SQL 進階查詢技術無處不在。本文將從實務角度深入探討這些技術的原理與應用,透過豐富的實例展示如何運用進階語法解決真實的商業問題。

資料分組與聚合分析原理

資料庫中儲存的原始資料往往需要經過聚合與分組才能產生有價值的洞察。GROUP BY 子句是 SQL 聚合分析的核心機制,它將資料集按照指定的欄位值分成多個群組,然後對每個群組獨立執行聚合函數。這種機制讓我們能從海量的交易記錄中提取出各地區的銷售總額,從員工資料中統計各部門的平均薪資,或是從網站日誌中分析每小時的訪問量。

理解 GROUP BY 的執行邏輯對於編寫正確的聚合查詢至關重要。當資料庫引擎執行包含 GROUP BY 的查詢時,首先會根據分組欄位的值將資料分割成獨立的群組。對於每個群組,只能訪問該群組內的資料列,而不能訪問其他群組的資料。這也解釋了為什麼在 SELECT 子句中,除了聚合函數外,只能出現 GROUP BY 子句中指定的欄位。

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100

package "SQL 聚合查詢執行流程" {
  component "原始資料表" as raw_data
  component "WHERE 過濾" as where_filter
  component "GROUP BY\n分組" as group_by
  component "聚合計算\n(COUNT/SUM/AVG)" as aggregate
  component "HAVING 過濾" as having_filter
  component "ORDER BY 排序" as order_by
  component "查詢結果" as result
}

raw_data --> where_filter : 1. 列層級過濾
where_filter --> group_by : 2. 資料分組
group_by --> aggregate : 3. 群組聚合
aggregate --> having_filter : 4. 群組過濾
having_filter --> order_by : 5. 結果排序
order_by --> result : 6. 輸出結果

note right of where_filter
  過濾個別資料列
  在分組之前執行
end note

note bottom of group_by
  按欄位值分組
  形成獨立群組
end note

note right of having_filter
  過濾聚合結果
  在分組之後執行
end note

@enduml

HAVING 子句是聚合查詢中另一個重要的元件,它專門用於過濾分組後的結果。許多初學者容易將 WHERE 與 HAVING 混淆,理解兩者的區別至關重要。WHERE 子句在分組之前執行,用於過濾原始資料列。HAVING 子句則在分組與聚合計算完成後執行,用於過濾聚合結果。這種設計讓我們能夠實現諸如「找出訂單數量超過 100 的客戶」這類需求,因為訂單數量是聚合計算的結果,無法在 WHERE 子句中使用。

-- 進階聚合分析範例:尋找高價值供應商國家
-- 展示 GROUP BY、聚合函數與 HAVING 的整合運用

SELECT 
    s_nationkey AS 國家代碼,
    COUNT(*) AS 供應商數量,
    MIN(s_acctbal) AS 最低帳戶餘額,
    MAX(s_acctbal) AS 最高帳戶餘額,
    AVG(s_acctbal) AS 平均帳戶餘額,
    STDDEV(s_acctbal) AS 餘額標準差
FROM 
    supplier
GROUP BY 
    s_nationkey
HAVING 
    COUNT(*) > 300 
    AND AVG(s_acctbal) > 0
ORDER BY 
    供應商數量 DESC,
    平均帳戶餘額 DESC;

這個查詢展示了聚合分析的完整流程。首先按國家代碼分組,然後計算每個國家的供應商數量與帳戶餘額的統計指標。HAVING 子句確保只保留供應商數量超過 300 且平均餘額為正的國家。這種分析在供應鏈管理中非常實用,能夠快速識別出具有大量供應商且財務狀況良好的市場。

多欄位分組提供了更細緻的分析維度。當我們同時按區域與國家分組時,能夠獲得階層式的統計結果,這對於跨國企業的區域化分析特別有價值。

-- 區域與國家的階層式聚合分析
-- 結合 JOIN 與多欄位分組實現跨表統計

SELECT 
    n.n_regionkey AS 區域代碼,
    r.r_name AS 區域名稱,
    s.s_nationkey AS 國家代碼,
    n.n_name AS 國家名稱,
    COUNT(*) AS 供應商數量,
    MIN(s.s_acctbal) AS 最低餘額,
    MAX(s.s_acctbal) AS 最高餘額,
    AVG(s.s_acctbal) AS 平均餘額,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY s.s_acctbal) AS 餘額中位數
FROM 
    supplier AS s
INNER JOIN 
    nation AS n ON s.s_nationkey = n.n_nationkey
INNER JOIN 
    region AS r ON n.n_regionkey = r.r_regionkey
GROUP BY 
    n.n_regionkey, r.r_name, s.s_nationkey, n.n_name
HAVING 
    COUNT(*) >= 50
ORDER BY 
    區域代碼, 供應商數量 DESC;

這個查詢透過三表 JOIN 整合了區域、國家與供應商資料,實現了地理維度的階層分析。PERCENTILE_CONT 函數計算餘額的中位數,這比平均值更能抵抗極端值的影響,在財務分析中是更穩健的指標。

多維度資料分析與 CUBE 運算

在商業智慧與資料倉儲領域,經常需要從多個角度同時分析資料。傳統的 GROUP BY 只能產生單一維度組合的聚合結果,若要獲得所有可能的維度組合,需要撰寫多個查詢並使用 UNION 合併。CUBE 運算子解決了這個問題,它能自動產生所有維度組合的聚合結果,包含總計、小計與明細。

CUBE 的威力在於其產生的結果集涵蓋了所有可能的分組組合。對於 n 個分組欄位,CUBE 會產生 2^n 種組合。例如,對於區域與國家兩個維度,CUBE 會產生四種組合,包含按區域與國家分組、僅按區域分組、僅按國家分組,以及總體統計。這種能力讓我們能用單一查詢產生完整的多維度報表。

-- CUBE 多維度分析範例
-- 同時產生多種聚合層級的統計結果

SELECT 
    COALESCE(CAST(n.n_regionkey AS VARCHAR), '總計') AS 區域,
    COALESCE(n.n_name, '小計') AS 國家,
    COUNT(*) AS 供應商數量,
    MIN(s.s_acctbal) AS 最低餘額,
    MAX(s.s_acctbal) AS 最高餘額,
    AVG(s.s_acctbal) AS 平均餘額,
    SUM(CASE WHEN s.s_acctbal > 0 THEN 1 ELSE 0 END) AS 正餘額數量,
    SUM(CASE WHEN s.s_acctbal < 0 THEN 1 ELSE 0 END) AS 負餘額數量,
    GROUPING(n.n_regionkey) AS 區域分組標記,
    GROUPING(n.n_name) AS 國家分組標記
FROM 
    supplier AS s
INNER JOIN 
    nation AS n ON s.s_nationkey = n.n_nationkey
GROUP BY 
    CUBE(n.n_regionkey, n.n_name)
ORDER BY 
    GROUPING(n.n_regionkey),
    GROUPING(n.n_name),
    區域,
    國家;

GROUPING 函數是理解 CUBE 結果的關鍵。它回傳 0 或 1,指示該欄位是否參與當前列的分組。當 GROUPING 回傳 1 時,表示該欄位在此列中是彙總層級,其值為 NULL。透過 COALESCE 函數,我們將這些 NULL 值替換為有意義的標籤,使報表更易讀。

ROLLUP 是 CUBE 的變體,它產生階層式的彙總結果。與 CUBE 產生所有可能組合不同,ROLLUP 只產生從右到左的累進彙總。對於區域、國家、城市三個維度,ROLLUP 會產生區域小計、國家小計、城市明細與總計,但不會產生僅按城市或僅按國家的分組。

-- ROLLUP 階層式彙總範例
-- 適合產生樹狀結構的報表

SELECT 
    COALESCE(r.r_name, '全球總計') AS 區域,
    COALESCE(n.n_name, 
        CASE WHEN r.r_name IS NOT NULL THEN '區域小計' ELSE NULL END) AS 國家,
    COUNT(DISTINCT s.s_suppkey) AS 供應商數量,
    COUNT(DISTINCT ps.ps_partkey) AS 供應零件種類,
    SUM(ps.ps_availqty) AS 總可用數量,
    AVG(ps.ps_supplycost) AS 平均供應成本
FROM 
    supplier AS s
INNER JOIN 
    nation AS n ON s.s_nationkey = n.n_nationkey
INNER JOIN 
    region AS r ON n.n_regionkey = r.r_regionkey
INNER JOIN 
    partsupp AS ps ON s.s_suppkey = ps.ps_suppkey
GROUP BY 
    ROLLUP(r.r_name, n.n_name)
ORDER BY 
    GROUPING(r.r_name),
    GROUPING(n.n_name),
    區域,
    國家;

這個查詢展示了 ROLLUP 在供應鏈分析中的應用。它產生了區域層級的小計與全球總計,讓管理者能夠快速了解各區域的供應能力與成本結構。

子查詢技術與查詢優化

子查詢是 SQL 中最強大且最靈活的特性之一,它允許在查詢中嵌套另一個查詢,實現複雜的資料篩選與關聯邏輯。子查詢可以出現在 SELECT、FROM、WHERE 或 HAVING 子句中,每種位置都有其特定的用途與語義。

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100

package "子查詢類型與執行策略" {
  package "不相關子查詢" {
    component "獨立執行\n一次性計算" as uncorr
  }
  
  package "相關子查詢" {
    component "外部查詢\n每列執行" as corr
  }
  
  package "純量子查詢" {
    component "回傳單一值\n用於比較" as scalar
  }
  
  package "表格子查詢" {
    component "回傳資料集\n用於 IN/EXISTS" as table
  }
}

[查詢優化器] --> uncorr : 快取結果
[查詢優化器] --> corr : 可能改寫為 JOIN
[查詢優化器] --> scalar : 可能物化
[查詢優化器] --> table : 考慮索引

note bottom of uncorr
  執行一次
  結果可重用
  效能較佳
end note

note bottom of corr
  重複執行
  依賴外部
  需優化
end note

@enduml

不相關子查詢是最簡單的子查詢形式,它獨立於外部查詢執行,只執行一次並將結果供外部查詢使用。這類子查詢通常用於 IN 或 NOT IN 運算子,實現基於集合的過濾。

-- 不相關子查詢範例:排除特定區域的國家
-- 子查詢獨立執行,結果用於外部查詢的過濾條件

SELECT 
    n_nationkey AS 國家代碼,
    n_name AS 國家名稱,
    n_regionkey AS 區域代碼
FROM 
    nation
WHERE 
    n_regionkey NOT IN (
        SELECT r_regionkey 
        FROM region 
        WHERE r_name IN ('AMERICA', 'ASIA')
    )
ORDER BY 
    n_name;

這個查詢首先執行子查詢,找出美洲與亞洲的區域代碼,然後外部查詢排除這些區域的國家。由於子查詢只執行一次,效能通常很好。

相關子查詢則依賴外部查詢的當前列,需要為外部查詢的每一列重複執行。這種特性使其能實現逐列的複雜條件判斷,但也可能導致效能問題。

-- 相關子查詢範例:每列獨立評估條件
-- 使用 EXISTS 運算子提升效能

SELECT 
    n_nationkey AS 國家代碼,
    n_name AS 國家名稱
FROM 
    nation AS n
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM region AS r 
        WHERE r.r_regionkey = n.n_regionkey 
        AND r.r_name IN ('AMERICA', 'ASIA')
    )
ORDER BY 
    n_name;

EXISTS 運算子通常比 IN 更高效,因為它只需確認是否存在符合條件的列,而不需要實際取得所有值。一旦找到第一個符合條件的列,EXISTS 就會短路並回傳真值。

在商業分析中,子查詢常用於找出符合特定聚合條件的實體。例如,找出在特定時間段內下單次數達到閾值的客戶,這類需求天然需要子查詢來表達。

-- 複雜子查詢範例:尋找活躍客戶
-- 結合時間過濾、分組與聚合條件

SELECT 
    c.c_custkey AS 客戶代碼,
    c.c_name AS 客戶名稱,
    c.c_acctbal AS 帳戶餘額,
    c.c_mktsegment AS 市場區隔,
    active_orders.訂單數量,
    active_orders.訂單總金額
FROM 
    customer AS c
INNER JOIN (
    SELECT 
        o_custkey,
        COUNT(*) AS 訂單數量,
        SUM(o_totalprice) AS 訂單總金額
    FROM 
        orders
    WHERE 
        EXTRACT(YEAR FROM o_orderdate) = 1997
    GROUP BY 
        o_custkey
    HAVING 
        COUNT(*) >= 4
) AS active_orders ON c.c_custkey = active_orders.o_custkey
WHERE 
    c.c_acctbal > 0
ORDER BY 
    active_orders.訂單總金額 DESC;

這個查詢展示了如何將子查詢放在 FROM 子句中作為衍生表格使用。子查詢先計算每位客戶在 1997 年的訂單統計,然後與客戶主表 JOIN,最終篩選出高活躍度且財務狀況良好的客戶。這種模式在客戶關係管理與精準行銷中非常常見。

相關子查詢也可以用於實現相同邏輯,但語義略有不同。

-- 相關子查詢替代方案
-- 直接在 WHERE 子句中評估聚合條件

SELECT 
    c.c_custkey AS 客戶代碼,
    c.c_name AS 客戶名稱,
    c.c_acctbal AS 帳戶餘額,
    (
        SELECT COUNT(*) 
        FROM orders AS o 
        WHERE o.o_custkey = c.c_custkey 
        AND EXTRACT(YEAR FROM o.o_orderdate) = 1997
    ) AS 訂單數量
FROM 
    customer AS c
WHERE 
    c.c_acctbal > 0
    AND 4 = (
        SELECT COUNT(*) 
        FROM orders AS o 
        WHERE o.o_custkey = c.c_custkey 
        AND EXTRACT(YEAR FROM o.o_orderdate) = 1997
    )
ORDER BY 
    訂單數量 DESC;

這種寫法更直觀地表達了「訂單數量等於 4」的條件,但可能導致子查詢執行兩次。現代查詢優化器通常能識別這種模式並優化執行計畫。

LATERAL JOIN 與動態關聯

LATERAL JOIN 是 SQL 標準中相對新穎的特性,它突破了傳統 JOIN 的限制,允許右側的表格表達式參考左側的列。這種能力在處理需要逐列計算或關聯的場景時特別有用,例如為每個實體找出其相關的前 N 筆記錄。

-- LATERAL JOIN 範例:為每個國家關聯供應商詳情
-- 展示動態關聯與列層級計算

SELECT 
    n.n_name AS 國家名稱,
    n.n_regionkey AS 區域代碼,
    supplier_stats.供應商數量,
    supplier_stats.平均餘額,
    supplier_stats.餘額標準差
FROM 
    nation AS n
INNER JOIN LATERAL (
    SELECT 
        COUNT(*) AS 供應商數量,
        AVG(s.s_acctbal) AS 平均餘額,
        STDDEV(s.s_acctbal) AS 餘額標準差
    FROM 
        supplier AS s
    WHERE 
        s.s_nationkey = n.n_nationkey
) AS supplier_stats ON TRUE
WHERE 
    supplier_stats.供應商數量 > 0
ORDER BY 
    supplier_stats.供應商數量 DESC;

LATERAL JOIN 讓子查詢能夠訪問外部查詢的當前列,這在語義上類似相關子查詢,但提供了更清晰的語法與更好的優化可能性。ON TRUE 子句表示無條件 JOIN,因為實際的關聯邏輯已經在 LATERAL 子查詢的 WHERE 子句中表達。

LATERAL JOIN 的強大之處在於能實現複雜的逐列計算邏輯,這在傳統 JOIN 中很難表達。

-- LATERAL JOIN 進階應用:為每個供應商找出其供應的熱門零件
-- 展示 TOP-N 查詢與複雜關聯

SELECT 
    s.s_suppkey AS 供應商代碼,
    s.s_name AS 供應商名稱,
    s.s_nationkey AS 國家代碼,
    top_parts.零件代碼,
    top_parts.可用數量,
    top_parts.供應成本
FROM 
    supplier AS s
CROSS JOIN LATERAL (
    SELECT 
        ps.ps_partkey AS 零件代碼,
        ps.ps_availqty AS 可用數量,
        ps.ps_supplycost AS 供應成本
    FROM 
        partsupp AS ps
    WHERE 
        ps.ps_suppkey = s.s_suppkey
    ORDER BY 
        ps.ps_availqty DESC
    LIMIT 3
) AS top_parts
WHERE 
    s.s_acctbal > 0
ORDER BY 
    s.s_suppkey,
    top_parts.可用數量 DESC;

這個查詢為每個供應商找出其可用數量最多的前三個零件。LATERAL 子查詢能夠參考外部的供應商代碼,並執行排序與限制,這在傳統 JOIN 中無法實現。這種模式在推薦系統與資料探勘中非常有用。

遞迴查詢與階層資料處理

許多真實世界的資料具有階層結構,例如組織架構圖、產品分類樹、地理區域層級或社交網路關係。傳統的關聯式資料庫不直接支援樹狀或圖狀結構的查詢,但透過遞迴 CTE,我們能優雅地處理這類資料。

遞迴 CTE 包含兩個部分,即錨定成員與遞迴成員。錨定成員定義遞迴的起點,通常是樹的根節點或圖的種子節點。遞迴成員則定義如何從當前層級導航到下一層級,它會反覆執行直到不再產生新的列。

-- 遞迴查詢範例:追蹤家族族譜
-- 展示階層資料的遞迴遍歷

WITH RECURSIVE smith_lineage AS (
    -- 錨定成員:族譜起點
    SELECT 
        person_id,
        name,
        father_person_id,
        0 AS generation,
        CAST(name AS VARCHAR(1000)) AS lineage_path
    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 family(person_id, name, father_person_id)
    WHERE name = 'Thomas'
    
    UNION ALL
    
    -- 遞迴成員:向下追蹤後代
    SELECT 
        f.person_id,
        f.name,
        f.father_person_id,
        sl.generation + 1,
        sl.lineage_path || ' -> ' || f.name
    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 f(person_id, name, father_person_id)
    INNER JOIN 
        smith_lineage AS sl ON f.father_person_id = sl.person_id
)
SELECT 
    name AS 姓名,
    generation AS 世代,
    lineage_path AS 血統路徑
FROM 
    smith_lineage
ORDER BY 
    generation,
    name;

這個遞迴查詢從 Thomas 開始,逐代追蹤其所有後代,並建構完整的血統路徑。generation 欄位記錄世代數,lineage_path 欄位則以字串形式呈現從祖先到當前人物的完整路徑。這種模式可以應用到組織架構分析、產品 BOM 展開或任何樹狀結構的遍歷。

遞迴查詢也可以向上追溯,從葉節點回溯到根節點。

-- 反向遞迴查詢:從後代追溯祖先
-- 展示雙向遍歷能力

WITH RECURSIVE ancestor_tree AS (
    -- 錨定成員:從特定後代開始
    SELECT 
        person_id,
        name,
        father_person_id,
        0 AS ancestor_level,
        CAST(name AS VARCHAR(1000)) AS ancestor_chain
    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 family(person_id, name, father_person_id)
    WHERE name = 'Sarah'
    
    UNION ALL
    
    -- 遞迴成員:向上追溯父系
    SELECT 
        f.person_id,
        f.name,
        f.father_person_id,
        at.ancestor_level + 1,
        f.name || ' <- ' || at.ancestor_chain
    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 f(person_id, name, father_person_id)
    INNER JOIN 
        ancestor_tree AS at ON f.person_id = at.father_person_id
)
SELECT 
    name AS 祖先姓名,
    ancestor_level AS 祖先層級,
    ancestor_chain AS 祖先鏈
FROM 
    ancestor_tree
ORDER BY 
    ancestor_level DESC;

這個查詢從 Sarah 開始,向上追溯其所有祖先,建構祖先鏈。在企業應用中,這種模式可用於追蹤成本中心的上層組織、零件的父組件或地理位置的上層區域。

CASE 條件判斷與資料轉換

CASE 表達式是 SQL 中的條件判斷機制,它允許在查詢中根據不同條件回傳不同的值。這種能力讓資料轉換、分類與動態計算變得簡潔直觀,是資料分析與報表開發中不可或缺的工具。

CASE 表達式有兩種形式,即簡單 CASE 與搜尋 CASE。簡單 CASE 適用於對單一表達式進行多值比對,而搜尋 CASE 則允許每個分支有獨立的條件判斷。

-- 簡單 CASE 範例:訂單狀態解碼
-- 將狀態代碼轉換為可讀文字

SELECT 
    o_orderkey AS 訂單編號,
    o_orderdate AS 訂單日期,
    o_custkey AS 客戶代碼,
    o_totalprice AS 訂單金額,
    CASE o_orderstatus
        WHEN 'P' THEN '部分完成'
        WHEN 'F' THEN '已完成'
        WHEN 'O' THEN '處理中'
        ELSE '未知狀態'
    END AS 訂單狀態,
    CASE o_orderpriority
        WHEN '1-URGENT' THEN '緊急'
        WHEN '2-HIGH' THEN '高'
        WHEN '3-MEDIUM' THEN '中'
        WHEN '4-NOT SPECIFIED' THEN '未指定'
        WHEN '5-LOW' THEN '低'
        ELSE '其他'
    END AS 優先等級
FROM 
    orders
WHERE 
    o_orderkey > 5999500
ORDER BY 
    o_orderdate DESC;

簡單 CASE 的語法清晰,適合處理狀態碼轉換這類單欄位多值對應的場景。在企業系統中,資料庫往往儲存簡短的代碼以節省空間,而報表則需要顯示完整的描述文字,CASE 表達式完美解決了這個需求。

搜尋 CASE 提供了更大的靈活性,允許每個分支有複雜的條件判斷。

-- 搜尋 CASE 範例:庫存狀態分類與補貨建議
-- 展示複雜條件判斷與業務邏輯

SELECT 
    ps_partkey AS 零件代碼,
    ps_suppkey AS 供應商代碼,
    ps_availqty AS 可用數量,
    ps_supplycost AS 供應成本,
    CASE
        WHEN ps_availqty = 0 THEN '缺貨'
        WHEN ps_availqty <= 100 THEN '急需補貨'
        WHEN ps_availqty <= 500 THEN '庫存偏低'
        WHEN ps_availqty <= 2000 THEN '庫存正常'
        ELSE '庫存充足'
    END AS 庫存狀態,
    CASE
        WHEN ps_availqty <= 100 AND ps_supplycost < 100 THEN '立即採購大量'
        WHEN ps_availqty <= 100 AND ps_supplycost >= 100 THEN '緊急採購適量'
        WHEN ps_availqty <= 500 THEN '規劃採購'
        ELSE '暫無需求'
    END AS 補貨建議,
    ps_availqty * ps_supplycost AS 庫存總值
FROM 
    partsupp
WHERE 
    ps_partkey BETWEEN 148300 AND 148450
ORDER BY 
    ps_availqty ASC,
    ps_supplycost DESC;

這個查詢展示了 CASE 表達式在庫存管理中的應用。第一個 CASE 根據可用數量判斷庫存狀態,第二個 CASE 則結合數量與成本提供補貨建議。這種多維度的條件判斷在供應鏈管理系統中非常常見。

CASE 表達式也能用於聚合分析,實現條件計數與分類統計。

-- CASE 聚合範例:客戶價值分群分析
-- 展示條件聚合與透視統計

SELECT 
    c_mktsegment AS 市場區隔,
    COUNT(*) AS 客戶總數,
    SUM(CASE WHEN c_acctbal < 0 THEN 1 ELSE 0 END) AS 負餘額客戶,
    SUM(CASE WHEN c_acctbal >= 0 AND c_acctbal < 5000 THEN 1 ELSE 0 END) AS 低價值客戶,
    SUM(CASE WHEN c_acctbal >= 5000 AND c_acctbal < 10000 THEN 1 ELSE 0 END) AS 中價值客戶,
    SUM(CASE WHEN c_acctbal >= 10000 THEN 1 ELSE 0 END) AS 高價值客戶,
    AVG(c_acctbal) AS 平均餘額,
    SUM(c_acctbal) AS 餘額總計
FROM 
    customer
GROUP BY 
    c_mktsegment
ORDER BY 
    客戶總數 DESC;

透過在 SUM 聚合函數中使用 CASE 表達式,我們實現了條件計數,將客戶按餘額分組統計。這種技術在客戶關係管理與市場分析中極為實用。

CASE 表達式也可以用於 UPDATE 語句,實現條件更新。

-- CASE UPDATE 範例:批次調整帳戶狀態
-- 展示條件更新與業務規則應用

UPDATE customer
SET 
    c_comment = c_comment || 
    CASE
        WHEN c_acctbal < -1000 THEN ' [信用警告]'
        WHEN c_acctbal < 0 THEN ' [餘額不足]'
        WHEN c_acctbal > 50000 THEN ' [VIP客戶]'
        ELSE ''
    END,
    c_acctbal = 
    CASE
        WHEN c_acctbal < 0 AND c_mktsegment = 'HOUSEHOLD' THEN c_acctbal * 1.05
        WHEN c_acctbal < 0 THEN c_acctbal * 1.02
        ELSE c_acctbal
    END
WHERE 
    c_custkey IN (
        SELECT c_custkey 
        FROM customer 
        WHERE c_acctbal < 0 OR c_acctbal > 50000
    );

這個更新語句根據客戶的帳戶餘額與市場區隔,動態調整註解與餘額。CASE 表達式讓複雜的業務規則能夠以聲明式的方式表達,提升程式碼的可讀性與維護性。

PIVOT 與資料透視分析

資料透視是將列資料轉換為欄資料的技術,它能將聚合結果以矩陣形式呈現,讓資料更易於閱讀與分析。PIVOT 運算子提供了標準化的語法來實現這種轉換。

-- PIVOT 範例:市場區隔客戶分佈
-- 將分組統計轉換為單列多欄格式

SELECT 
    *
FROM (
    SELECT 
        c_mktsegment AS 市場區隔,
        COUNT(*) AS 客戶數量
    FROM 
        customer
    GROUP BY 
        c_mktsegment
) AS source_data
PIVOT (
    MAX(客戶數量) 
    FOR 市場區隔 IN (
        'AUTOMOBILE' AS 汽車,
        'MACHINERY' AS 機械,
        'BUILDING' AS 建築,
        'FURNITURE' AS 家具,
        'HOUSEHOLD' AS 家用
    )
) AS pivot_result;

PIVOT 將原本五列的結果轉換為單列五欄,讓資料更適合製作報表或儀表板。這種格式在商業智慧工具中特別受歡迎。

對於不支援 PIVOT 語法的資料庫系統,可以使用 CASE 表達式實現相同效果。

-- 手動 PIVOT 替代方案
-- 使用 CASE 聚合實現透視效果

SELECT 
    MAX(CASE WHEN c_mktsegment = 'AUTOMOBILE' THEN 客戶數量 END) AS 汽車,
    MAX(CASE WHEN c_mktsegment = 'MACHINERY' THEN 客戶數量 END) AS 機械,
    MAX(CASE WHEN c_mktsegment = 'BUILDING' THEN 客戶數量 END) AS 建築,
    MAX(CASE WHEN c_mktsegment = 'FURNITURE' THEN 客戶數量 END) AS 家具,
    MAX(CASE WHEN c_mktsegment = 'HOUSEHOLD' THEN 客戶數量 END) AS 家用
FROM (
    SELECT 
        c_mktsegment,
        COUNT(*) AS 客戶數量
    FROM 
        customer
    GROUP BY 
        c_mktsegment
) AS aggregated_data;

這種方法雖然繁瑣,但具有更好的相容性,且能實現更複雜的轉換邏輯。

多維度透視能產生更豐富的分析結果。

-- 多維透視範例:區域與市場區隔交叉分析
-- 展示複雜的矩陣式報表

SELECT 
    區域名稱,
    COALESCE(汽車, 0) AS 汽車,
    COALESCE(機械, 0) AS 機械,
    COALESCE(建築, 0) AS 建築,
    COALESCE(家具, 0) AS 家具,
    COALESCE(家用, 0) AS 家用,
    汽車 + 機械 + 建築 + 家具 + 家用 AS 總計
FROM (
    SELECT 
        r.r_name AS 區域名稱,
        c.c_mktsegment AS 市場區隔,
        COUNT(*) AS 客戶數量
    FROM 
        customer AS c
    INNER JOIN 
        nation AS n ON c.c_nationkey = n.n_nationkey
    INNER JOIN 
        region AS r ON n.n_regionkey = r.r_regionkey
    GROUP BY 
        r.r_name, c.c_mktsegment
) AS source_data
PIVOT (
    MAX(客戶數量)
    FOR 市場區隔 IN (
        'AUTOMOBILE' AS 汽車,
        'MACHINERY' AS 機械,
        'BUILDING' AS 建築,
        'FURNITURE' AS 家具,
        'HOUSEHOLD' AS 家用
    )
) AS pivot_table
ORDER BY 
    區域名稱;

這個查詢產生區域與市場區隔的交叉統計表,每列代表一個區域,每欄代表一個市場區隔,儲存格則顯示客戶數量。這種矩陣式報表在業務分析中極為常見。

總結與最佳實務

SQL 進階查詢技術為資料分析與處理提供了強大的工具箱。從資料分組與聚合到多維度分析,從子查詢優化到遞迴結構處理,從條件判斷到資料透視,這些技術涵蓋了絕大多數商業場景的需求。

在台灣的資料庫開發與數據分析領域,掌握這些進階技術能顯著提升工作效率與分析深度。無論是金融業的風險評估、零售業的銷售分析、製造業的供應鏈優化或科技業的用戶行為分析,SQL 進階查詢技術都是不可或缺的核心能力。

在實務應用中,應該根據具體場景選擇最適合的技術。對於需要多維度統計的報表,CUBE 與 ROLLUP 能大幅簡化查詢邏輯。對於階層資料的遍歷,遞迴 CTE 提供了優雅的解決方案。對於複雜的條件判斷與資料轉換,CASE 表達式是首選工具。理解這些技術的原理與適用場景,並在實際專案中靈活運用,將有助於建構更高效、更易維護的資料分析系統。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。