常用統計求和 SQL 語句範例大全：按時間、分組、Top N 彙總

報表開發中，SUM、COUNT、GROUP BY、時間維度彙總、Top N 查詢是高頻需求。本文提供標準 SQL 寫法（兼容 MySQL/SQL Server/PostgreSQL），並針對效能給出索引建議。

範例一：單一欄位在時間區間內求和

-- 表：sales (id, amount, sale_date)
SELECT SUM(amount) AS total_amount
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

索引建議： INDEX idx_sale_date (sale_date) 或複合 (sale_date, amount) 覆蓋索引。

範例二：查詢明細並同時統計總和

SELECT *, SUM(amount) OVER () AS grand_total
FROM sales
WHERE sale_date >= '2023-01-01';

• 使用視窗函數 SUM() OVER () 避免子查詢或連接，MySQL 8.0+、PG、SQL Server 全支援。
• 若不支援視窗函數，退而求其次：SELECT *, (SELECT SUM(amount) FROM sales WHERE ...) AS total FROM sales WHERE ...

範例三：按年/月/日彙總銷售總額

-- 表：orders (ordertime DATETIME, money DECIMAL)

-- 1. 每年
SELECT YEAR(ordertime) AS yr, SUM(money) AS yearly_total
FROM orders
GROUP BY YEAR(ordertime)
ORDER BY yr;

-- 2. 每月
SELECT YEAR(ordertime) AS yr, MONTH(ordertime) AS mo, SUM(money) AS monthly_total
FROM orders
GROUP BY YEAR(ordertime), MONTH(ordertime)
ORDER BY yr, mo;

-- 3. 每日
SELECT DATE(ordertime) AS dt, SUM(money) AS daily_total
FROM orders
GROUP BY DATE(ordertime)
ORDER BY dt;

效能關鍵：在 ordertime 建立索引。MySQL 8.0 支援函數索引 INDEX idx_ordertime_date ((DATE(ordertime)))，直接加速分組。

範例四：統計某月銷量 Top 10 商品

-- 表：sales (id, product_name, price, sale_date)

-- 簡易版：按銷售金額 Top 10
SELECT product_name, SUM(price) AS total_revenue, COUNT(*) AS qty
FROM sales
WHERE MONTH(sale_date) = 7  -- 7月
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 10;

-- 進階：按銷量 Top 10，並顯示排名
SELECT 
    product_name,
    SUM(price) AS total_revenue,
    COUNT(*) AS qty,
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rank_by_qty
FROM sales
WHERE sale_date >= '2023-07-01' AND sale_date < '2023-08-01'
GROUP BY product_name
ORDER BY qty DESC
LIMIT 10;

• 時間範圍建議用 >= 起始 AND < 結束，避免函數導致索引失效。
• ROW_NUMBER() 需 MySQL 8.0+；舊版用變數模擬或應用層處理。

範例五：多維度交叉彙總（樞紐分析雛形）

SELECT 
    YEAR(ordertime) AS yr,
    MONTH(ordertime) AS mo,
    category,
    SUM(money) AS total,
    COUNT(*) AS orders
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY YEAR(ordertime), MONTH(ordertime), category
ORDER BY yr, mo, total DESC;

真正的樞紐表建議在應用層（Python pandas、Excel、BI 工具）轉換，SQL 僅負責彙總明細。

範例六：條件式彙總（單查詢多指標）

SELECT 
    DATE(ordertime) AS dt,
    SUM(CASE WHEN status = 'paid' THEN money ELSE 0 END) AS paid_amount,
    SUM(CASE WHEN status = 'refunded' THEN money ELSE 0 END) AS refund_amount,
    COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
    COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refund_orders
FROM orders
GROUP BY DATE(ordertime)
ORDER BY dt;

• SUM(CASE...) / COUNT(CASE...) 是條件彙總標準寫法，比多次子查詢快得多。

效能優化清單

1. 索引覆蓋：(查詢欄位, 分組欄位, 聚合欄位) 讓查詢只走索引不回表。
2. 分區表：大歷史表按 RANGE (YEAR(ordertime)) 分區，查詢自動剪枝。
3. 物化視圖/彙總表：極高頻報表，預先計算每日/每小時彙總存入摘要表，查詢毫秒級返回。
4. 避免 SELECT *：只取需要欄位，減少 I/O 與網路傳輸。

掌握這幾類核心彙總模式，配合索引設計與視窗函數，即能應對 95% 以上報表統計需求。大數據量場景請務必引入預計算彙總表或 OLAP 引擎。