SQL 聚合函數完全指南：COUNT、SUM、AVG 進階用法

SQL 聚合函數核心概念與最佳實踐

聚合函數是資料分析的基石，將多行資料彙總為單一統計值。掌握其行為細節（NULL 處理、DISTINCT 去重、分組搭配）是撰寫高效查詢的關鍵。

五大核心聚合函數對照表

關鍵行為深度解析

COUNT(*) vs COUNT(欄位) 差異

-- 統計所有列（包含全 NULL 列）
SELECT COUNT(*) FROM orders

-- 統計特定欄位非 NULL 值個數
SELECT COUNT(customer_id) FROM orders

-- 去重統計
SELECT COUNT(DISTINCT customer_id) FROM orders

效能提示： COUNT(*) 通常比 COUNT(欄位) 快，因為不需檢查 NULL。

SUM/AVG 的 DISTINCT 陷阱

-- 銷售額去重求和（通常不合理，除非業務需求特殊）
SELECT SUM(DISTINCT amount) FROM sales

-- 正確做法：先去重再聚合
SELECT SUM(amount) FROM (SELECT DISTINCT amount FROM sales) t

字串與日期上的 MIN/MAX

-- 字串按字典序
SELECT MIN(name), MAX(name) FROM customers

-- 日期取最早/最晚
SELECT MIN(order_date), MAX(order_date) FROM orders

進階應用模式

條件式聚合

SELECT 
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS revenue
FROM orders

搭配 GROUP BY 多維分析

SELECT 
    YEAR(order_date) AS yr,
    MONTH(order_date) AS mo,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY yr, mo

窗口函數版聚合（不分組）

SELECT 
    order_id,
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
    AVG(amount) OVER () AS global_avg
FROM orders

效能優化建議

• 聚合前先用 WHERE 過濾，減少參與運算的行數
• GROUP BY 欄位建立索引可大幅加速
• 大資料量避免使用 DISTINCT 聚合，改用子查詢去重
• COUNT(*) 配合覆蓋索引可實現 Index Only Scan

常見錯誤避坑

1. ❌ SELECT name, COUNT(*) FROM users — 非聚合欄位未出現在 GROUP BY
2. ❌ WHERE COUNT(*) > 10 — 聚合條件必須用 HAVING
3. ✅ HAVING COUNT(*) > 10 — 正確寫法