SQL 進階統計技巧：CASE 條件聚合實現單查詢多維度分類統計

核心概念：條件聚合

傳統做法：多次查詢、應用層迴圈累加、UNION ALL 合併。
進階做法：單一查詢內使用 CASE WHEN ... THEN ... ELSE ... END 搭配聚合函數 (SUM、COUNT、AVG 等)，一次掃描完成多維度統計。

CASE 語法兩種形式

簡單 CASE (Simple Case)

CASE input_expression
    WHEN when_expression THEN result_expression
    [ ...n ]
    [ ELSE else_result_expression ]
END

適用於「欄位值等於某固定值」的判斷。

搜尋 CASE (Searched Case)

CASE
    WHEN Boolean_expression THEN result_expression
    [ ...n ]
    [ ELSE else_result_expression ]
END

適用於複雜條件 (範圍、多欄位組合、函數運算)。
兩者功能等價，搜尋 CASE 更通用。

實戰範例：進出記錄統計

場景

表 AccessLog：UserName, LogTime, Direction (0=進入, 1=離開), Weight (可選，攜帶重量)

需求 1：單日進出人次統計

SELECT 
    SUM(CASE WHEN Direction = 0 THEN 1 ELSE 0 END) AS EnterCount,
    SUM(CASE WHEN Direction = 1 THEN 1 ELSE 0 END) AS ExitCount,
    COUNT(*) AS TotalRecords
FROM AccessLog
WHERE DATEDIFF(DAY, LogTime, GETDATE()) = 0;  -- 今日

需求 2：單日進出重量統計 (條件聚合運算欄位)

SELECT 
    SUM(CASE WHEN Direction = 0 THEN Weight ELSE 0 END) AS EnterWeight,
    SUM(CASE WHEN Direction = 1 THEN Weight ELSE 0 END) AS ExitWeight
FROM AccessLog
WHERE DATEDIFF(DAY, LogTime, GETDATE()) = 0;

需求 3：直接產出顯示友善標籤 (省去應用層轉換)

SELECT 
    UserName,
    LogTime,
    CASE Direction WHEN 0 THEN '進入' ELSE '離開' END AS ActionDesc
FROM AccessLog
WHERE DATEDIFF(DAY, LogTime, GETDATE()) = 0;

進階應用模式

多維度交叉統計 (單查詢產出報表矩陣)

SELECT 
    Department,
    -- 性別統計
    SUM(CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) AS MaleCount,
    SUM(CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) AS FemaleCount,
    -- 年齡層統計
    SUM(CASE WHEN Age BETWEEN 18 AND 25 THEN 1 ELSE 0 END) AS Age_18_25,
    SUM(CASE WHEN Age BETWEEN 26 AND 35 THEN 1 ELSE 0 END) AS Age_26_35,
    SUM(CASE WHEN Age BETWEEN 36 AND 50 THEN 1 ELSE 0 END) AS Age_36_50,
    SUM(CASE WHEN Age > 50 THEN 1 ELSE 0 END) AS Age_50Plus,
    -- 薪資區間統計
    SUM(CASE WHEN Salary < 30000 THEN 1 ELSE 0 END) AS Salary_LT_30K,
    SUM(CASE WHEN Salary BETWEEN 30000 AND 50000 THEN 1 ELSE 0 END) AS Salary_30K_50K,
    SUM(CASE WHEN Salary BETWEEN 50000 AND 80000 THEN 1 ELSE 0 END) AS Salary_50K_80K,
    SUM(CASE WHEN Salary > 80000 THEN 1 ELSE 0 END) AS Salary_GT_80K,
    COUNT(*) AS DeptTotal
FROM Employees
GROUP BY Department
ORDER BY Department;

動態欄位聚合 (PIVOT 風格)

-- 產出：年月 | 類別A筆數 | 類別B筆數 | 類別C筆數
SELECT 
    FORMAT(TransDate, 'yyyy-MM') AS YearMonth,
    SUM(CASE WHEN Category = 'A' THEN 1 ELSE 0 END) AS CatA_Count,
    SUM(CASE WHEN Category = 'B' THEN 1 ELSE 0 END) AS CatB_Count,
    SUM(CASE WHEN Category = 'C' THEN 1 ELSE 0 END) AS CatC_Count,
    COUNT(*) AS Total_Count
FROM Transactions
WHERE TransDate >= DATEADD(MONTH, -12, GETDATE())
GROUP BY FORMAT(TransDate, 'yyyy-MM')
ORDER BY YearMonth;

條件聚合搭配窗口函數

SELECT 
    UserID,
    LogTime,
    Direction,
    -- 當日迄今累計進入次數
    SUM(CASE WHEN Direction = 0 THEN 1 ELSE 0 END) OVER (
        PARTITION BY UserID, CAST(LogTime AS DATE) 
        ORDER BY LogTime ROWS UNBOUNDED PRECEDING
    ) AS DailyEnterSeq,
    -- 當日總進入次數
    SUM(CASE WHEN Direction = 0 THEN 1 ELSE 0 END) OVER (
        PARTITION BY UserID, CAST(LogTime AS DATE)
    ) AS DailyEnterTotal
FROM AccessLog
WHERE LogTime >= DATEADD(DAY, -30, GETDATE());

複雜條件：多欄位組合判斷

SELECT 
    COUNT(*) AS Total,
    SUM(CASE WHEN Status = 'Active' AND LastLogin > DATEADD(DAY, -30, GETDATE()) THEN 1 ELSE 0 END) AS Active_Recent,
    SUM(CASE WHEN Status = 'Active' AND LastLogin <= DATEADD(DAY, -30, GETDATE()) THEN 1 ELSE 0 END) AS Active_Inactive,
    SUM(CASE WHEN Status = 'Suspended' THEN 1 ELSE 0 END) AS Suspended,
    SUM(CASE WHEN Status = 'Deleted' THEN 1 ELSE 0 END) AS Deleted
FROM Users;

效能優化關鍵點

1. 單次掃描：條件聚合讓優化器產生單一 Table Scan/Index Seek 計畫，避免多次掃描
2. 索引支援：WHERE 條件欄位 (如 LogTime) 建立索引；CASE 內欄位若高選擇性可考慮過濾索引
3. 避免函數包裹索引欄位：WHERE DATEDIFF(DAY, LogTime, GETDATE()) = 0 會導致索引失效，改用 WHERE LogTime >= CAST(GETDATE() AS DATE) AND LogTime < DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
4. 大表分區：按時間分區表，查詢自動消除分區
5. 彙總表/物化視圖：極高頻報表預先計算存儲

各資料庫支援對照

常見錯誤與避坑指南

最佳實踐總結

1. 優先使用搜尋 CASE，通用性最強
2. ELSE 0 是計數/求和的標準做法
3. 別名語意化：AS EnterCount 而非 AS cnt1
4. 格式化對齊：多行 CASE 對齊 WHEN/THEN/ELSE，提升可讀性
5. 複雜邏輯封裝：極長 CASE 考慮移至視圖/函數/CTV
6. 測試邊界：NULL 值、邊界值、空結果集

條件聚合是 SQL 開發者從「寫查詢」進階到「寫報表」的關鍵技能，善用它能大幅減少資料庫往返、簡化應用代碼、提升報表效能。