SQL SELECT 入門：GROUP BY 與窗函數實戰練習

前面我們學了很多資料整理的技巧，包括排序、聚合、GROUP BY、窗函數等等。

這篇文章要透過三個實戰題目，幫你把這些技巧串起來，學會如何分析需求、拆解問題，然後寫出對應的 SQL。

實戰練習一：處理空值的排序

題目

老闆說：「請你幫我把這些資料按照更新時間，從新到舊排序。」

假設我們有一張資料表：

你會發現，文件B 和文件D 的更新時間是 NULL，表示它們從來沒被更新過。

你是一個學過資料庫的工程師，馬上就想到一個問題：

「有些資料沒被更新過，更新時間是空值，這樣怎麼辦？」

老闆說：「那就排在最後好了。」

需求分析

把需求翻譯成 SQL 的語言：

• 「按照更新時間，從新到舊」→ ORDER BY 更新時間 DESC
• 「空值排在最後」→ NULLS LAST

SQL 寫法

SELECT *
FROM 資料表
ORDER BY 更新時間 DESC NULLS LAST

SELECT *
FROM 資料表
ORDER BY 更新時間 DESC NULLS LAST

這樣就完成了，空值會自動排在最後面。

排序後的結果：

你可以看到，有更新時間的資料按照時間從新到舊排序，而更新時間是 NULL 的文件B 和文件D 則排在最後面。

實戰練習一（延伸）：用 COALESCE 處理空值排序

題目

PM 跑來說：「老闆覺得把沒更新過的資料放在最後面，時間軸看起來會很怪。可以改成這樣嗎？如果沒有更新時間，就用創建時間來排序。」

需求分析

這是一個「處理空值」的問題。

如果更新時間是空值，就用創建時間來代替。

這不就是我們學過的 COALESCE（逢空補值） 嗎？

SQL 寫法

SELECT *
FROM 資料表
ORDER BY COALESCE(更新時間, 創建時間) DESC

SELECT *
FROM 資料表
ORDER BY COALESCE(更新時間, 創建時間) DESC

為什麼 COALESCE 可以放在 ORDER BY 裡面？

之前我們學 COALESCE 的時候，都是放在 SELECT 裡面：

SELECT COALESCE(備註, 部門, '未知') AS 處理結果
FROM 員工表

SELECT COALESCE(備註, 部門, '未知') AS 處理結果
FROM 員工表

其實 COALESCE 是一個「函數」，它會回傳一個值。

只要是需要「值」的地方，都可以放函數。

ORDER BY 後面需要的是「用什麼值來排序」，所以也可以放 COALESCE。

ORDER BY COALESCE(更新時間, 創建時間) DESC

ORDER BY COALESCE(更新時間, 創建時間) DESC

SQL 在執行的時候，會針對每一筆資料，先計算 COALESCE(更新時間, 創建時間) 得到一個時間值，然後再用這個時間值來決定排序的順序。

最後根據「排序依據」欄位做遞減排序，結果就會是：文件A → 文件D → 文件C → 文件B。

因為創建時間不會是空值（資料創建的當下就會自動填入），所以 COALESCE 一定會回傳一個有效的時間值，不會有 NULL 的問題。

排序結果

對比一下第一題用 NULLS LAST 的結果：

用 COALESCE 的排序結果更合理，因為文件D 雖然沒有更新過，但它的創建時間是 2024-03-01，比文件C 的更新時間 2024-02-28 還新，所以排在前面。

實戰練習二：GROUP BY 分組篩選

題目

老闆說：「請你找出人數不超過 5 人，但總銷售額達到 500 萬的精英小組。然後挑出平均銷售額最高的前 3 組。」

假設我們有一張組員業績表：

從這張表可以看到：

• A組：3 人
• B組：6 人
• C組：3 人
• D組：4 人

需求分析

我們來拆解這個需求：

1. 「人數不超過 5 人」→ 需要計算每組人數 → COUNT(*) <= 5
2. 「總銷售額達到 500 萬」→ 需要計算每組總額 → SUM(銷售額) >= 5000000
3. 「每組」→ 需要分組 → GROUP BY 組別
4. 「平均銷售額最高的前 3 組」→ 排序 + 限制筆數 → ORDER BY ... DESC LIMIT 3

因為條件是針對「分組後的結果」做篩選，所以要用 HAVING，不是 WHERE。

思考流程

第一步：決定分組方式

要算「每組」的人數和總額，所以要 GROUP BY 組別。

第二步：設定篩選條件

分組後要篩選：人數 <= 5 且 總額 >= 500 萬。

這是分組後的條件，所以用 HAVING。

第三步：決定要顯示的欄位

老闆會想看：組別、人數、總銷售額、平均銷售額。

第四步：排序並限制筆數

按照平均銷售額從高到低排序，只取前 3 組。

SQL 寫法

SELECT 
    組別,
    COUNT(*) AS 人數,
    SUM(銷售額) AS 總銷售額,
    AVG(銷售額) AS 平均銷售額
FROM 組員業績表
GROUP BY 組別
HAVING COUNT(*) <= 5 AND SUM(銷售額) >= 5000000
ORDER BY 平均銷售額 DESC
LIMIT 3

SELECT 
    組別,
    COUNT(*) AS 人數,
    SUM(銷售額) AS 總銷售額,
    AVG(銷售額) AS 平均銷售額
FROM 組員業績表
GROUP BY 組別
HAVING COUNT(*) <= 5 AND SUM(銷售額) >= 5000000
ORDER BY 平均銷售額 DESC
LIMIT 3

因為 ORDER BY 在 SELECT 之後執行，所以可以直接用別名 平均銷售額 來排序。

執行結果

B組雖然總銷售額有 5000000，但人數是 6 人，超過 5 人的限制，所以被篩選掉了。

實戰練習三：窗函數搭配子查詢

題目

老闆說：「另外，請你找出人數大於 5 人的小組當中，業績佔比超過 40% 的業績王。」

什麼是業績王？

假設一個組有 5 個人，平均每人應該貢獻 20% 的業績。

如果有人一個人就扛了 40% 以上的業績，那他就是「業績王」。

需求分析

這題要挑出的是「個別的組員」，不是「組別」。

如果用 GROUP BY，資料會被壓縮，就看不到個別組員了。

所以這題要用窗函數來做。

我們需要算出：

1. 每組的人數 → 用來篩選「人數 > 5」
2. 每個人的業績佔比 → 用來篩選「佔比 > 40%」

這兩個都需要「分區聚合」，但又要保留每一筆個別資料。

這就是窗函數的用武之地。

思考流程

這題比較複雜，我們分成兩個步驟來做。

第一步：用窗函數算出需要的欄位

先用子查詢把「組員人數」和「銷售佔比」算出來：

• 組員人數：COUNT(*) OVER (PARTITION BY 組別)
• 銷售佔比：銷售額 / SUM(銷售額) OVER (PARTITION BY 組別)

為什麼要用子查詢？

因為窗函數是在 SELECT 階段計算的，而 WHERE 在 SELECT 之前執行。

所以你不能直接在 WHERE 裡面用窗函數的結果來篩選。

解決方法就是：先用子查詢把窗函數的結果算出來，變成一張「虛擬表」，然後在外層再用 WHERE 篩選。

子查詢產生的「虛擬表」長這樣：

有了這張虛擬表，外層的 WHERE 就可以篩選「組員人數 > 5 且 銷售佔比 > 0.4」的資料了。

第二步：篩選出符合條件的資料

從第一步的結果中，篩選出：

• 組員人數 > 5
• 銷售佔比 > 0.4

SQL 寫法

SELECT *
FROM (
    SELECT 
        組員,
        組別,
        銷售額,
        COUNT(*) OVER (PARTITION BY 組別) AS 組員人數,
        銷售額 / SUM(銷售額) OVER (PARTITION BY 組別) AS 銷售佔比
    FROM 組員業績表
) AS 子查詢
WHERE 組員人數 > 5 AND 銷售佔比 > 0.4

SELECT *
FROM (
    SELECT 
        組員,
        組別,
        銷售額,
        COUNT(*) OVER (PARTITION BY 組別) AS 組員人數,
        銷售額 / SUM(銷售額) OVER (PARTITION BY 組別) AS 銷售佔比
    FROM 組員業績表
) AS 子查詢
WHERE 組員人數 > 5 AND 銷售佔比 > 0.4

執行結果

以這個範例資料來說，B組是唯一人數大於 5 人的組別，但 B組 的業績佔比最高的是 Emily（0.22），沒有人超過 40%。

所以查詢結果是空的，沒有找到業績王。

如果把條件放寬成「銷售佔比 > 0.2」，就會找到 Emily：

什麼時候用 GROUP BY？什麼時候用窗函數？

經過這三個練習，你應該有感覺了：

簡單的判斷方式：

• 如果最後要顯示的是「每一組」的結果 → 用 GROUP BY
• 如果最後要顯示的是「每一個人」的結果 → 用窗函數

小結

這篇文章透過三個實戰練習，複習了：

1. COALESCE：處理空值，逢空補值
2. GROUP BY + HAVING：分組後篩選，用於找出符合條件的「組」
3. 窗函數 + 子查詢：分區聚合但保留明細，用於找出符合條件的「個人」

遇到複雜的需求時，記得先拆解問題：

1. 最後要顯示什麼？（個別資料 or 分組結果）
2. 需要哪些計算？（聚合函數、佔比、排名…）
3. 篩選條件是什麼？（WHERE or HAVING）
4. 要不要排序？要取幾筆？

一步步拆解，再組合成完整的 SQL，就不會覺得太難了！