SQL 窗口函數實戰：PARTITION BY 解決分組統計與明細並存難題

需求：查詢所有部門（Department），並顯示每部門員工數。傳統 GROUP BY 強制聚合，無法同時輸出部門明細與統計值。窗口函數 OVER (PARTITION BY ...) 完美解決。

GROUP BY 的硬傷

-- 錯誤：deptName 未出現在 GROUP BY 或聚合函數中
SELECT depId, deptName, COUNT(*) AS peopleCount
FROM Employee e JOIN Department d ON e.depId = d.depId
GROUP BY depId;

標準 SQL 要求 SELECT 列表中的非聚合欄位必須出現在 GROUP BY。若加入 deptName，分組顆粒度變細，統計失真。

窗口函數語法與語意

SELECT 
    d.depId, 
    d.deptName,
    COUNT(*) OVER (PARTITION BY d.depId) AS peopleCount
FROM Department d
LEFT JOIN Employee e ON d.depId = e.depId
ORDER BY d.depId;

• OVER：宣告窗口函數
• PARTITION BY depId：依部門劃分窗口，各分區獨立計算 COUNT(*)
• 結果：每行保留原始明細，同時攜帶該分區的聚合值

去重策略：內聯視圖 + DISTINCT

上述查詢每部門會輸出 N 筆（N=員工數），需去重：

SELECT DISTINCT depId, deptName, peopleCount
FROM (
    SELECT d.depId, d.deptName,
           COUNT(*) OVER (PARTITION BY d.depId) AS peopleCount
    FROM Department d
    LEFT JOIN Employee e ON d.depId = e.depId
) x;

Access 不支援窗口函數的替代方案：標量子查詢

SELECT DISTINCT d.depId, d.deptName,
    (SELECT COUNT(*) FROM Employee e WHERE e.depId = d.depId) AS peopleCount
FROM Department d;

效能差異：窗口函數經查詢優化器專門優化（單次掃描 + 雜湊/排序分區），標量子查詢對每行執行子查詢（N+1 問題），大資料集下效能差距巨大。

進階窗口函數家族

結語

PARTITION BY 是「移動的 GROUP BY」：保留明細行、賦予分區聚合能力。掌握窗口函數，能將大量「子查詢/臨時表/應用層迴圈」邏輯下推至資料庫層，獲得數量級效能提升與程式碼簡化。