2024年07月29日玄貓（BlackCat）

SQL條件邏輯與CASE表示式應用

本文探討SQL中條件邏輯的應用，重點講解CASE表示式的兩種形式：搜尋型和簡單型，並搭配實際案例說明如何根據不同條件傳回特定值。此外，文章也涵蓋了CASE表示式在資料透視、關係檢查等方面的應用，以及iff()、ifnull()、nvl()和decode()等相關函式的用法，提供更全面的條件邏輯處理方案。

SQL 資料函式庫

SQL CASE表示式條件邏輯資料函式庫查詢資料透視

SQL 中的條件邏輯對於根據不同情況執行不同操作至關重要。CASE 表示式是實作此目標的核心工具，它允許根據條件傳回不同的值，從而提升查詢的靈活性。簡單型 CASE 表示式適用於單一欄位的多值比對，而搜尋型 CASE 表示式則能處理更複雜的條件組合，甚至包含子查詢。這兩種形式各有優勢，可以根據實際需求選擇使用。此外，CASE 表示式在資料透視操作中扮演著關鍵角色，能將多行資料轉換為單行多列，方便資料分析和報表生成。另一個重要的應用是檢查特定關係是否存在，例如判斷客戶是否下過特定金額的訂單，這在商業分析中非常實用。

條件邏輯在SQL中的應用：CASE表示式詳解

在SQL查詢中，條件邏輯的應用至關重要，而CASE表示式是實作這一邏輯的主要工具。本文將探討CASE表示式的兩種型別及其在實際查詢中的多種應用場景。

CASE表示式的型別

SQL中的CASE表示式主要分為兩種型別：搜尋型（Searched Case Expressions）和簡單型（Simple Case Expressions）。

搜尋型CASE表示式

搜尋型CASE表示式是較為靈活的一種形式，其語法結構如下：

CASE
    WHEN condition1 THEN expression1
    WHEN condition2 THEN expression2
    ...
    WHEN conditionN THEN expressionN
    [ELSE expressionZ]
END

這種表示式允許根據不同的條件傳回不同的值。條件可以是任意的布林表示式，甚至可以包含子查詢。以下是一個實際的例子，用於計算零售價超過2000美元的零件被訂購的次數：

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;

簡單型CASE表示式

簡單型CASE表示式則相對簡單，其語法如下：

CASE expression
    WHEN value1 THEN expression1
    WHEN value2 THEN expression2
    ...
    WHEN valueN THEN expressionN
    [ELSE expressionZ]
END

這種表示式將一個表示式的值與多個值進行比較，並傳回第一個匹配的值對應的結果。以下是一個將訂單狀態碼轉換為可讀文字的例子：

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;

CASE表示式的應用場景

資料透視（Pivot Operations）

在資料分析和報告中，經常需要將多行資料轉換為單行多列的形式，即資料透視操作。以下是一個將每年總訂單價格透視到單行的例子：

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;

檢查是否存在特定關係

有時，我們需要檢查某種關係是否存在，而不關心具體的出現次數。例如，檢查客戶是否曾經下過超過40萬美元的訂單，可以使用帶有相關子查詢的CASE表示式來實作。

條件表示式在資料函式庫查詢中的應用

在資料函式庫查詢中，條件表示式是一種強大的工具，能夠根據特定的條件對資料進行分類別、更新或刪除。本篇文章將探討條件表示式的使用方法，並透過具體範例展示其在實際應用中的效果。

使用 Case 運算式進行資料分類別

Case 運算式允許我們根據特定的條件對資料進行分類別。以下是一個範例，展示如何使用 Case 運算式根據客戶的訂單總金額對客戶進行分類別：

SELECT 
  c_custkey, 
  c_name,
  CASE 
    WHEN EXISTS (
      SELECT 1 
      FROM orders o 
      WHERE o.o_custkey = c.c_custkey 
      AND o.o_totalprice > 400000
    ) THEN 'Big Spender'
    ELSE 'Regular'
  END AS cust_type
FROM customer c
WHERE c_custkey BETWEEN 74000 AND 74020;

內容解密：

CASE 關鍵字用於開始條件表示式。
WHEN EXISTS 子句檢查客戶是否至少有一個訂單總金額超過 400,000。
如果條件成立，則客戶被分類別為 ‘Big Spender’，否則為 ‘Regular’。
END 關鍵字標誌著條件表示式的結束。

使用 Case 運算式進行條件更新

除了分類別資料外，Case 運算式還可以用於更新資料表中的欄位。以下是一個範例，展示如何使用 Case 運算式更新客戶型別：

UPDATE customer AS c
SET cust_type = 
  CASE 
    WHEN EXISTS (
      SELECT 1 
      FROM orders AS o 
      WHERE o.o_custkey = c.c_custkey 
      AND o.o_totalprice > 400000
    ) THEN 'Big Spender'
    ELSE 'Regular'
  END;

內容解密：

UPDATE 陳述式用於更新資料表中的欄位。
SET 子句指定要更新的欄位和新的值。
CASE 運算式與前面的範例相同，用於根據客戶的訂單總金額進行分類別。

使用 Case 運算式進行條件刪除

Case 運算式也可以用於刪除滿足特定條件的資料。以下是一個範例，展示如何使用 Case 運算式刪除某些客戶：

DELETE FROM customer c
WHERE 1 = 
  CASE 
    WHEN NOT EXISTS (
      SELECT 1 
      FROM orders AS o 
      WHERE o.o_custkey = c.c_custkey 
      AND o.o_totalprice > 1000
    ) THEN 1
    WHEN '31-DEC-1995' > (
      SELECT MAX(o_orderdate) 
      FROM orders AS o 
      WHERE o.o_custkey = c.c_custkey
    ) THEN 1
    ELSE 0
  END;

內容解密：

DELETE 陳述式用於刪除資料表中的資料。
WHERE 子句指定要刪除的資料條件。
CASE 運算式檢查兩個條件：客戶是否從未下過總金額超過 1,000 的訂單，或者客戶最後一次下單日期是否早於 1995 年 12 月 31 日。

使用 iff() 函式進行簡單條件判斷

Snowflake 提供了一個 iff() 函式，用於簡化簡單的 if-then-else 邏輯。以下是一個範例，展示如何使用 iff() 函式：

SELECT 
  c_custkey, 
  c_name,
  CASE 
    WHEN EXISTS (
      SELECT 1 
      FROM orders o 
      WHERE o.o_custkey = c.c_custkey 
      AND o.o_totalprice > 400000
    ) THEN 'Big Spender'
    ELSE 'Regular'
  END AS cust_type_case,
  IFF(
    EXISTS (
      SELECT 1 
      FROM orders o 
      WHERE o.o_custkey = c.c_custkey 
      AND o.o_totalprice > 400000
    ),
    'Big Spender',
    'Regular'
  ) AS cust_type_iff
FROM customer c
WHERE c_custkey BETWEEN 74000 AND 74020;

圖表翻譯：

此查詢結果比較了使用 CASE 運算式和 IFF() 函式對客戶進行分類別的結果。

此圖示顯示了使用兩種不同方法進行條件判斷的結果比較。

內容解密：

IFF() 函式的第一個引數是條件，第二個引數是條件成立時的傳回值，第三個引數是條件不成立時的傳回值。
IFF() 函式可以用於簡化簡單的 if-then-else 邏輯，使查詢陳述式更加簡潔。

條件邏輯在SQL中的應用

在SQL查詢中，條件邏輯是一種非常重要的功能，可以幫助我們根據不同的條件傳回不同的結果。本章節將介紹如何在SQL中使用條件邏輯，包括使用CASE表示式、iff()函式、ifnull()函式、nvl()函式和decode()函式等。

CASE表示式

CASE表示式是SQL中實作條件邏輯的最常用方法。它允許我們根據一個或多個條件傳回不同的值。CASE表示式有兩種形式：簡單CASE表示式和搜尋CASE表示式。

簡單CASE表示式

簡單CASE表示式用於將一個表示式與多個值進行比較，並傳回對應的值。例如：

SELECT 
  o_orderkey,
  CASE o_orderstatus
    WHEN 'P' THEN 'Partial'
    WHEN 'F' THEN 'Filled'
    WHEN 'O' THEN 'Open'
  END AS status_case
FROM orders
LIMIT 20;

搜尋CASE表示式

搜尋CASE表示式允許我們根據多個條件傳回不同的值。例如：

SELECT 
  ps_partkey, 
  ps_suppkey, 
  ps_availqty,
  CASE 
    WHEN ps_availqty < 100 THEN 'order now'
    WHEN ps_availqty BETWEEN 101 AND 1000 THEN 'order soon'
    ELSE 'plenty in stock'
  END AS order_status
FROM partsupp
WHERE ps_partkey BETWEEN 148300 AND 148450;

內容解密：

此查詢使用搜尋CASE表示式根據ps_availqty的值傳回不同的訂單狀態。如果ps_availqty小於100，則傳回'order now'；如果在101到1000之間，則傳回'order soon'；否則傳回'plenty in stock'。

iff()函式

iff()函式是一種簡化的條件邏輯函式，等同於簡單CASE表示式。它接受三個引數：條件、真值和假值。例如：

SELECT 
  name,
  iff(favorite_color IS NULL, 'Unknown', favorite_color) AS favorite_color_iff
FROM (VALUES ('Thomas','yellow'), ('Catherine','red'), ('Richard','blue'), ('Rebecca',NULL)) AS person (name, favorite_color);

內容解密：

此查詢使用iff()函式根據favorite_color是否為NULL傳回不同的值。如果為NULL，則傳回'Unknown'；否則傳回原值。

ifnull()和nvl()函式

ifnull()和nvl()函式用於將NULL值替換為指定的值。例如：

SELECT 
  name,
  nvl(favorite_color, 'Unknown') AS favorite_color_nvl,
  ifnull(favorite_color, 'Unknown') AS favorite_color_isnull
FROM (VALUES ('Thomas','yellow'), ('Catherine','red'), ('Richard','blue'), ('Rebecca',NULL)) AS person (name, favorite_color);

內容解密：

此查詢使用nvl()和ifnull()函式將NULL的favorite_color替換為'Unknown'。

decode()函式

decode()函式類別似於簡單CASE表示式，用於將一個表示式與多個值進行比較，並傳回對應的值。例如：

SELECT 
  o_orderkey,
  decode(o_orderstatus, 'P', 'Partial', 'F', 'Filled', 'O', 'Open') AS status_decode
FROM orders
LIMIT 20;

內容解密：

此查詢使用decode()函式根據o_orderstatus的值傳回不同的狀態描述。

練習題

在以下查詢中新增一個名為order_status的列，使用CASE表示式根據ps_availqty的值傳回不同的訂單狀態。

SELECT 
  ps_partkey, 
  ps_suppkey, 
  ps_availqty
FROM partsupp
WHERE ps_partkey BETWEEN 148300 AND 148450;

將以下查詢改寫為使用搜尋CASE表示式。

SELECT 
  o_orderdate, 
  o_custkey,
  CASE o_orderstatus
    WHEN 'P' THEN 'Partial'
    WHEN 'F' THEN 'Filled'
    WHEN 'O' THEN 'Open'
  END status
FROM orders
WHERE o_orderkey > 5999500;

修改以下查詢，使用CASE表示式將資料樞轉化。

SELECT 
  r_name, 
  COUNT(*)
FROM nation n
INNER JOIN region r ON r.r_regionkey = n.n_regionkey
INNER JOIN supplier s ON s.s_nationkey = n.n_nationkey
GROUP BY r_name;