當計算欄位邂逅子查詢

來自專欄 數據分析師成長之路

我們平常用的計算欄位可能大多是 col1/col2，col1*col2，col1*10-col2 這種「小學」式的計算欄位，用子查詢來做計算欄位的還比較少見，今天給大家分享兩個例子，來一起學習一下。

例1：

假如有兩個表，一個是customers表，裡面有顧客的姓名，顧客所在地等個人信息，另一個是orders表，裡面記錄了訂單號，下單的顧客編號等信息，需要注意的是，有的顧客可能下過多個訂單，而有的可能一個都沒下（比如你女朋友過生日，你在某貓的Olay旗艦店給她買了兩支小白瓶，過了段時間情人節，又在SK-II旗艦店給買了一瓶神仙水，我呢比較窮，只註冊了賬號，什麼都沒買）。

那麼問題來了，我怎麼統計每個顧客下了多少訂單呢？一天好多個訂單的，除了土豪，是不是還有可能是刷單黨？

如果只看單個顧客，我們可以用下面的語句來查詢：

SELECT COUNT(*) AS ord_num

FROM orders

WHERE cust_name = iRunning ;

要對每個顧客執行COUNT(*) ，應該將它作為一個子查詢：

SELECT cust_name, cust_state

(SELECT COUNT(*)

FROM orders

WHERE orders.cust_id = customers.cust_id) AS orders

FROM customers

ORDER BY cust_name;

orders 是一個計算欄位，它是由圓括弧中的子查詢建立的。該子查詢對檢索出的每個顧客執行一次。

可能你注意到上面的子查詢語句和我們平時見的查詢不太一樣，我們平時見的類似的查詢大多是像下面這種：

SELECT col_a, col_b

FROM table_a, table_b

WHERE table_a.col_c= table_b.col_c

而上述的整體模式是：

SELECT col_a,

(SELECT COUNT(*)

FROM table_b

WHERE table_b.col_c = table_a.col_c) AS col_calc

FROM table_a;

例2：分數排名

編寫一個SQL查詢來實現分數排名。如果兩個分數相同，則兩個分數排名（Rank）相同。請注意，平分後的下一個名次應該是下一個連續的整數值。換句話說，名次之間不應該有「間隔」。

思路：假如每個人分數不一樣，那麼一個人的名次就是大於等於他分數的數量，例如4個人，大於等於最後一名的分數有四個，所以他是第4名。

假如有人分數一樣，比如3個人，兩個一樣，另一個比這兩個高，高的顯然是第一名，因為大於等於他分數的就一個，但是對於兩個分數一樣的，大於等於他們分數的數有3個，顯然不能是第三名，但大於等於他們分數的不同分數只有兩個，正好是第二名。

對上例中的組合進行遍歷，score_unique代表大於等於score的不同分數：

對score_unique進行計數後正好對應分數的名次。

我們用兩種寫法實現，一種是聯結表：

SELECT t1.Score as Score, COUNT(t2.Score) as Rank

FROM Scores t1

LEFT JOIN (SELECT DISTINCT Score FROM Scores) t2

ON t1.Score <= t2.Score

GROUP BY t1.id

ORDER BY t1.Score DESC;

另一種是用子查詢做計算欄位，對每個分數都計算大於等於它的不同分數的數量：

SELECT Score,

(SELECT COUNT(DISTINCT Score)

FROM Scores

WHERE Score >= t1.Score) rank

FROM Scores t1

ORDER BY Score DESC;

經過這兩個例子，相信對子查詢做計算欄位有一認識了。

參考資料：

《SQL必知必會》

http://leetcode.com