SQL CASE 表示式

黃子毅發表於2022-03-22
SQL

CASE 表示式分為簡單表示式與搜尋表示式，其中搜尋表示式可以覆蓋簡單表示式的全部能力，我也建議只寫搜尋表示式，而不要寫簡單表示式。

簡單表示式：

SELECT CASE city
WHEN '北京' THEN 1
WHEN '天津' THEN 2
ELSE 0
END AS abc
FROM test

搜尋表示式：

SELECT CASE
WHEN city = '北京' THEN 1
WHEN city = '天津' THEN 2
ELSE 0
END AS abc
FROM test

明顯可以看出，簡單表示式只是搜尋表示式 a = b 的特例，因為無法書寫任何符號，只要條件換成 a > b 就無法勝任了，而搜尋表示式不但可以輕鬆勝任，甚至可以寫聚合函式。

CASE 表示式裡的聚合函式

為什麼 CASE 表示式裡可以寫聚合函式？

因為本身表示式就支援聚合函式，比如下面的語法，我們不會覺得奇怪：

SELECT sum(pv), avg(uv) from test

本身 SQL 就支援多種不同的聚合方式同時計算，所以將其用在 CASE 表示式裡，也是順其自然的：

SELECT CASE
WHEN count(city) = 100 THEN 1
WHEN sum(dau) > 200 THEN 2
ELSE 0
END AS abc
FROM test

只要 SQL 表示式中存在聚合函式，那麼整個表示式都聚合了，此時訪問非聚合變數沒有任何意義。所以上面的例子，即便在 CASE 表示式中使用了聚合，其實也不過是聚合了一次後，按照條件進行判斷罷了。

這個特性可以解決很多實際問題，比如將一些複雜聚合判斷條件的結果用 SQL 結構輸出，那麼很可能是下面這種寫法：

SELECT CASE
WHEN 聚合函式(欄位) 符合什麼條件 THEN xxx
... 可能有 N 個
ELSE NULL
END AS abc
FROM test

這也可以認為是一種行轉列的過程，即 把行聚合後的結果通過一條條 CASE 表示式形成一個個新的列。

聚合與非聚合不能混用

我們希望利用 CASE 表示式找出那些 pv 大於平均值的行，以下這種想當然的寫法是錯誤的：

SELECT CASE
WHEN pv > avg(pv) THEN 'yes'
ELSE 'no'
END AS abc
FROM test

原因是，只要 SQL 中存在聚合表示式，那麼整條 SQL 就都是聚合的，所以返回的結果只有一條，而我們期望查詢結果不聚合，只是判斷條件用到了聚合結果，那麼就要使用子查詢。

為什麼子查詢可以解決問題？因為子查詢的聚合發生在子查詢，而不影響當前父查詢，理解了這一點，就知道為什麼下面的寫法才是正確的了：

SELECT CASE
WHEN pv > ( SELECT avg(pv) from test ) THEN 'yes'
ELSE 'no'
END AS abc
FROM test

這個例子也說明了 CASE 表示式裡可以使用子查詢，因為子查詢是先計算的，所以查詢結果在哪兒都能用，CASE 表示式也不例外。

WHERE 中的 CASE

WHERE 後面也可以跟 CASE 表示式的，用來做一些需要特殊列舉處理的篩選。

比如下面的例子：

SELECT * FROM demo WHERE
CASE
WHEN city = '北京' THEN true
ELSE ID > 5
END

本來我們要查詢 ID 大於 5 的資料，但我想對北京這個城市特別對待，那麼就可以在判斷條件中再進行 CASE 分支判斷。

這個場景在 BI 工具裡等價於，建立一個 CASE 表示式欄位，可以拖入篩選條件生效。

GROUP BY 中的 CASE

想不到吧，GROUP BY 裡都可以寫 CASE 表示式：

SELECT isPower, sum(gdp) FROM test GROUP BY CASE
WHEN isPower = 1 THEN city, area
ELSE city
END

上面例子表示，計算 GDP 時，對於非常發達的城市，按照每個區粒度檢視聚合結果，也就是看的粒度更細一些，而對於欠發達地區，本身 gdp 也不高，直接按照城市粒度看聚合結果。

這樣，就按照不同的條件對資料進行了分組聚合。由於返回行結果是混在一起的，像這個例子，可以根據 isPower 欄位是否為 1 判斷，是否按照城市、區域進行了聚合，如果沒有其他更顯著的標識，可能導致無法區分不同行的聚合粒度，因此謹慎使用。

ORDER BY 中的 CASE

同樣，ORDER BY 使用 CASE 表示式，會將排序結果按照 CASE 分類進行分組，每組按照自己的規則排序，比如：

SELECT * FROM test ORDER BY CASE
WHEN isPower = 1 THEN gdp
ELSE people
END

上面的例子，對發達地區採用 gdp 排序，否則採用人口數量排序。

總結

CASE 表示式總結一下有如下特點：

  1. 支援簡單與搜尋兩種寫法，推薦搜尋寫法。
  2. 支援聚合與子查詢，需要注意不同情況的特點。
  3. 可以寫在 SQL 查詢的幾乎任何地方，只要是可以寫欄位的地方，基本上就可以替換為 CASE 表示式。
  4. 除了 SELECT 外，CASE 表示式還廣泛應用在 INSERT 與 UPDATE，其中 UPDATE 的妙用是不用將 SQL 拆分為多條，所以不用擔心資料變更後對判斷條件的二次影響。
討論地址是：精讀《SQL CASE 表示式》· Issue #404 · ascoders/weekly

如果你想參與討論，請 點選這裡，每週都有新的主題，週末或週一釋出。前端精讀 - 幫你篩選靠譜的內容。

關注 前端精讀微信公眾號

<img width=200 src="https://img.alicdn.com/tfs/TB165W0MCzqK1RjSZFLXXcn2XXa-258-258.jpg">

版權宣告：自由轉載-非商用-非衍生-保持署名（創意共享 3.0 許可證）

相關文章