SQL 條件邏輯：活用 CASE 與 IFF 函式提升查詢效率

在資料函式庫的世界裡，條件邏輯就像魔法棒一樣，能讓你的 SQL 查詢更靈活、更有效率。Snowflake SQL 提供了 CASE 表示式和 IFF 函式這兩個強大的工具，讓你輕鬆駕馭各種條件判斷。CASE 表示式就像瑞士刀，能處理各種複雜的多條件判斷，而 IFF 函式則像一把鋒利的匕首，專門處理簡單的 if-then-else 邏輯。

CASE 表示式在處理多條件判斷時非常有用，例如根據訂單金額將客戶分類別、根據條件更新或刪除資料。它允許你根據不同的條件傳回不同的結果，讓你的 SQL 查詢更具彈性。而 IFF 函式則適用於簡單的單條件判斷，它能讓你的程式碼更簡潔易懂。透過 IFF 函式，你可以用更少的程式碼完成相同的任務，提升程式碼的可讀性和維護性。這兩個函式都能有效地處理 NULL 值，避免程式出錯或產生不正確的結果。熟練運用 CASE 和 IFF 函式，能讓你寫出更精簡、更有效率的 SQL 查詢，提升資料函式庫效能，讓你事半功倍。

SQL條件邏輯：`CASE`表示式與`IFF()`函式的應用

在資料倉管理中，條件邏輯是不可或缺的一環。透過條件邏輯，我們可以根據不同的條件，執行不同的操作或傳回不同的結果。在 Snowflake SQL 中，CASE表示式和IFF()函式是實作條件邏輯的兩個重要工具。玄貓將會探討這兩個工具的應用場景和使用方法。

`CASE`表示式：多條件判斷的利器

CASE表示式允許我們根據多個條件進行判斷，並傳回不同的結果。它非常適合處理複雜的條件邏輯。

根據訂單金額判斷客戶型別

假設我們想要根據客戶的訂單金額，將客戶分為「大戶 (Big Spender)」和「一般客戶 (Regular)」兩種型別。可以使用以下的 SQL 語法來實作：

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;

程式碼解密

SELECT c_custkey, c_name: 選擇客戶的鍵和名稱。
CASE WHEN EXISTS (...) THEN 'Big Spender' ELSE 'Regular' END AS cust_type: 使用CASE表示式判斷客戶型別。如果客戶存在至少一筆訂單金額超過 400000，則為「大戶」，否則為「一般客戶」。
FROM customer c: 從customer表查詢資料。
WHERE c_custkey BETWEEN 74000 AND 74020: 篩選客戶鍵在 74000 到 74020 之間的客戶。

這段程式碼會根據orders表中是否存在總價 (o_totalprice) 超過 400,000 的訂單來判斷客戶型別。如果子查詢傳回至少一筆資料，EXISTS 運算元就會評估為真 (true)，CASE表示式就會將客戶標記為「大戶 (Big Spender)」，否則標記為「一般客戶 (Regular)」。

執行上述 SQL 語法後，可能會得到類別似以下的結果：

+-----------+--------------------+-------------+
| C_CUSTKEY | C_NAME             | CUST_TYPE   |
|-----------+--------------------+-------------|
| 74003     | Customer#000074003 | Big Spender |
| 74008     | Customer#000074008 | Regular     |
| 74011     | Customer#000074011 | Big Spender |
| 74014     | Customer#000074014 | Regular     |
| 74015     | Customer#000074015 | Regular     |
| 74000     | Customer#000074000 | Regular     |
| 74009     | Customer#000074009 | Regular     |
| 74020     | Customer#000074020 | Regular     |
| 74017     | Customer#000074017 | Regular     |
| 74012     | Customer#000074012 | Regular     |
| 74005     | Customer#000074005 | Regular     |
+-----------+--------------------+-------------+

條件更新：使用`CASE`表示式更新客戶型別

除了在查詢中使用CASE表示式外，我們還可以在更新語法中使用它。假設我們想要將customer表中的cust_type欄位，根據客戶的訂單金額進行更新，可以使用以下的 SQL 語法：

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 customer AS c: 更新customer表，並給予別名c。
SET cust_type = CASE ... END: 設定cust_type欄位的值，根據CASE表示式判斷。
WHEN EXISTS (...) THEN 'Big Spender': 如果子查詢傳回至少一筆資料，表示客戶為「大戶」。
ELSE 'Regular': 否則，客戶為「一般客戶」。

條件刪除：使用`CASE`表示式刪除客戶

CASE表示式也可以用於刪除語法中。例如，我們想要刪除所有沒有下過超過 1000 元訂單，或者 1996 年以後沒有下過任何訂單的客戶，可以使用以下的 SQL 語法：

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 '1995-12-31' > (
            SELECT MAX(o_orderdate)
            FROM orders AS o
            WHERE o.o_custkey = c.c_custkey
        ) THEN 1
        ELSE 0
    END;

程式碼解密

DELETE FROM customer c: 從customer表刪除資料，並給予別名c。
WHERE 1 = CASE ... END: 根據CASE表示式的結果判斷是否刪除客戶。
WHEN NOT EXISTS (...) THEN 1: 如果客戶沒有下過超過 1000 元的訂單，則傳回 1。
WHEN '1995-12-31' > (...) THEN 1: 如果客戶在 1996 年以後沒有下過任何訂單，則傳回 1。
ELSE 0: 否則傳回 0，不刪除客戶。

這段程式碼首先檢查客戶是否曾經下過超過 1000 元的訂單。如果沒有，NOT EXISTS 條件成立，CASE表示式傳回 1，客戶將被刪除。接下來，程式碼檢查客戶是否在 1996 年或之後下過任何訂單。如果沒有，CASE表示式同樣傳回 1，客戶也會被刪除。

`IFF()`函式：簡潔的單條件判斷

如果只需要一個簡單的 if-then-else 表示式，可以使用IFF()函式。IFF()函式接受三個引數：一個條件表示式，一個如果條件為真的傳回值，以及一個如果條件為假的傳回值。

使用`IFF()`函式判斷客戶型別

以下範例使用IFF()函式來判斷客戶型別，並且使用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_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;

程式碼解密

SELECT c_custkey, c_name: 選擇客戶的鍵和名稱。
CASE ... END AS cust_type_case: 使用CASE表示式判斷客戶型別，與先前的範例相同。
IFF(..., 'Big Spender', 'Regular') AS cust_type_iff: 使用IFF()函式判斷客戶型別。如果EXISTS條件成立，則為「大戶」，否則為「一般客戶」。
FROM customer c: 從customer表查詢資料。
WHERE c_custkey BETWEEN 74000 AND 74020: 篩選客戶鍵在 74000 到 74020 之間的客戶。

執行上述 SQL 語法後，可能會得到類別似以下的結果：

+-----------+--------------------+----------------+--------------+
| C_CUSTKEY | C_NAME             | CUST_TYPE_CASE | CUST_TYPE_IFF|
|-----------+--------------------+----------------+--------------|
| 74011     | Customer#000074011 | Big Spender    | Big Spender  |
| 74003     | Customer#000074003 | Big Spender    | Big Spender  |
| 74005     | Customer#000074005 | Regular        | Regular      |
| 74015     | Customer#000074015 | Regular        | Regular      |
| 74017     | Customer#000074017 | Regular        | Regular      |
| 74008     | Customer#000074008 | Regular        | Regular      |
| 74020     | Customer#000074020 | Regular        | Regular      |
| 74000     | Customer#000074000 | Regular        | Regular      |
| 74012     | Customer#000074012 | Regular        | Regular      |
| 74009     | Customer#000074009 | Regular        | Regular      |
| 74014     | Customer#000074014 | Regular        | Regular      |
+-----------+--------------------+----------------+--------------+

從結果中可以看出，CASE表示式和IFF()函式的結果是一致的。

條件邏輯的靈活運用：解鎖 SQL 的更多可能性

在資料函式庫操作中，條件邏輯不僅能提升程式碼的靈活性，還能讓你以更少的程式碼完成更多工。玄貓將帶你深入探索 SQL 中條件邏輯的應用，包括 ifnull()、nvl()、coalesce() 和 decode() 函式，並分享如何在實際場景中運用它們。

`ifnull()` 和 `nvl()`：處理遺失值的利器

在處理資料時，遺失值（NULL）是個常見的問題。ifnull() 和 nvl() 函式能讓你輕鬆地用預設值替換 NULL 值，從而避免程式出錯或產生不正確的結果。

以下範例展示如何使用 ifnull() 和 nvl() 函式，將 favorite_color 欄位中的 NULL 值替換為 ‘Unknown’：

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

這個技巧在報表製作中特別有用，能確保報表呈現完整與易於理解的資訊。

雙重來源取值：`ifnull()` 和 `nvl()` 的進階應用

有時，欄位的值可能來自不同的資料表。這時，ifnull() 和 nvl() 函式就能派上用場，讓你從多個來源中選取非 NULL 的值。

考慮以下使用 FULL OUTER JOIN 的例子：

PUBLIC> SELECT
    orders.ordernum,
    CASE
        WHEN orders.custkey IS NOT NULL THEN orders.custkey
        WHEN customer.custkey IS NOT NULL THEN customer.custkey
    END AS custkey_case,
    nvl(orders.custkey, customer.custkey) AS custkey_nvl,
    ifnull(orders.custkey, customer.custkey) AS custkey_ifnull,
    customer.custname AS name
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;

在這個例子中，custkey 的值可能來自 orders 或 customer 資料表。透過 nvl() 或 ifnull() 函式，我們可以確保選取到非 NULL 的 custkey 值。

`coalesce()`：多重選擇的終極方案

當需要在多個欄位中尋找非 NULL 值時，coalesce() 函式是你的最佳選擇。它可以接受任意數量的表示式，並傳回第一個非 NULL 的值。

-- 範例：從三個欄位中選取第一個非 NULL 值
SELECT
    coalesce(column1, column2, column3) AS first_non_null
FROM
    your_table;

`decode()`：簡潔的條件判斷

decode() 函式類別似於簡單的 CASE 表示式，它將一個表示式與一系列值進行比較，並在找到比對項時傳回相應的值。

以下範例展示如何使用 decode() 函式，將 o_orderstatus 欄位的值轉換為易於理解的狀態描述：

PUBLIC> SELECT
    o_orderkey,
    CASE
        o_orderstatus
        WHEN 'P' THEN 'Partial'
        WHEN 'F' THEN 'Filled'
        WHEN 'O' THEN 'Open'
    END AS status_case,
    decode(
        o_orderstatus,
        'P',
        'Partial',
        'F',
        'Filled',
        'O',
        'Open'
    ) AS status_decode
FROM
    orders
LIMIT
    20;

雖然 decode() 函式更簡潔，但玄貓更推薦使用 CASE 表示式，因為它更易於理解，與在不同的資料函式庫伺服器之間具有更好的移植性。

測驗你的知識

以下練習旨在測試你對條件邏輯的理解程度。

練習 10-1

在以下查詢中新增一個名為 order_status 的欄位，使用 CASE 表示式根據 ps_availqty 的值傳回不同的結果：

若 ps_availqty 小於 100，傳回 ‘order now’
若 ps_availqty 介於 101 和 1000 之間，傳回 ‘order soon’
否則，傳回 ‘plenty in stock’

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

練習 10-2

將以下查詢改寫為使用 searched CASE 表示式，而不是 simple CASE 表示式：

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

練習 10-3

以下查詢傳回每個地區的供應商數量：

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

修改此查詢，使用 CASE 表示式將資料透視成以下格式：

+---------+--------+--------+-------------+------+
| AMERICA | AFRICA | EUROPE | MIDDLE_EAST | ASIA |
+---------+--------+--------+-------------+------+
| 1532    | 1444   | 1474   | 1491        | 1459 |
+---------+--------+--------+-------------+------+

玄貓

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

SQL 條件邏輯：活用 CASE 與 IFF 函式提升查詢效率

SQL條件邏輯：CASE表示式與IFF()函式的應用

CASE表示式：多條件判斷的利器

根據訂單金額判斷客戶型別

條件更新：使用CASE表示式更新客戶型別

條件刪除：使用CASE表示式刪除客戶

IFF()函式：簡潔的單條件判斷

使用IFF()函式判斷客戶型別

條件邏輯的靈活運用：解鎖 SQL 的更多可能性

ifnull() 和 nvl()：處理遺失值的利器

雙重來源取值：ifnull() 和 nvl() 的進階應用

coalesce()：多重選擇的終極方案

decode()：簡潔的條件判斷

測驗你的知識

練習 10-1

練習 10-2

練習 10-3

玄貓

SQL條件邏輯：`CASE`表示式與`IFF()`函式的應用

`CASE`表示式：多條件判斷的利器

條件更新：使用`CASE`表示式更新客戶型別

條件刪除：使用`CASE`表示式刪除客戶

`IFF()`函式：簡潔的單條件判斷

使用`IFF()`函式判斷客戶型別

`ifnull()` 和 `nvl()`：處理遺失值的利器

雙重來源取值：`ifnull()` 和 `nvl()` 的進階應用

`coalesce()`：多重選擇的終極方案

`decode()`：簡潔的條件判斷