常用統計彙總 SQL 實戰示例：按時間、TopN、條件分組聚合

常用統計彙總 SQL 實戰示例：按時間、TopN、條件分組聚合

業務報表、數據分析中高頻出現的統計場景：時間維度聚合、TopN 排名、條件分組求和。本文提供標準 SQL 寫法，兼容 MySQL、PostgreSQL、SQL Server，並針對性能給出優化建議。

一、 指定時間範圍內單字段求和

-- 表 sales(id, amount, created_at)
SELECT SUM(amount) AS total_amount
FROM sales
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

優化：created_at 建索引，避免對列使用函數（如 DATE(created_at)），保證索引下推。

二、 查詢明細同時彙總（窗口函數/子查詢）

-- MySQL 8.0+ / PostgreSQL / SQL Server：窗口函數
SELECT *, SUM(amount) OVER (PARTITION BY category_id) AS category_total
FROM sales
WHERE created_at >= '2024-01-01';

-- MySQL 5.7 及以下：子查詢或連接
SELECT s.*, c.category_total
FROM sales s
JOIN (
    SELECT category_id, SUM(amount) AS category_total
    FROM sales
    WHERE created_at >= '2024-01-01'
    GROUP BY category_id
) c ON s.category_id = c.category_id
WHERE s.created_at >= '2024-01-01';

三、 按年/月/日多維度統計銷售額

表 orders(order_time DATETIME, money DECIMAL(10,2))

1. 按年統計

SELECT YEAR(order_time) AS yr, SUM(money) AS yearly_total
FROM orders
GROUP BY YEAR(order_time)
ORDER BY yr;

2. 按月統計

SELECT YEAR(order_time) AS yr, MONTH(order_time) AS mon, SUM(money) AS monthly_total
FROM orders
GROUP BY YEAR(order_time), MONTH(order_time)
ORDER BY yr, mon;

3. 按日統計

SELECT DATE(order_time) AS dt, SUM(money) AS daily_total
FROM orders
GROUP BY DATE(order_time)
ORDER BY dt;

4. 通用寫法（兼容性最好）

-- 使用 DATE_FORMAT / TO_CHAR / FORMAT 格式化
SELECT DATE_FORMAT(order_time, '%Y-%m') AS ym, SUM(money) AS total
FROM orders
GROUP BY DATE_FORMAT(order_time, '%Y-%m');

四、 TopN 排名統計

表 sales(product_name, price, sale_date)

1. 全量 Top 10 產品銷量

SELECT product_name, COUNT(*) AS sales_cnt, SUM(price) AS total_revenue
FROM sales
GROUP BY product_name
ORDER BY sales_cnt DESC
LIMIT 10;

2. 指定月份 Top 10

SELECT product_name, COUNT(*) AS sales_cnt, SUM(price) AS total_revenue
FROM sales
WHERE MONTH(sale_date) = 1  -- 1 月
GROUP BY product_name
ORDER BY sales_cnt DESC
LIMIT 10;

3. 各分類 Top 3（窗口函數）

SELECT category, product_name, sales_cnt
FROM (
    SELECT category, product_name, COUNT(*) AS sales_cnt,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(*) DESC) AS rn
    FROM sales
    GROUP BY category, product_name
) t
WHERE rn <= 3;

五、 條件分組聚合（CASE WHEN / FILTER）

-- 單表多維度統計：總訂單、付費訂單、退款訂單、客單價
SELECT 
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refund_orders,
    SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) / NULLIF(SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), 0) AS avg_order_value
FROM orders
WHERE created_at >= '2024-01-01';

PostgreSQL 簡潔寫法：

SELECT 
    COUNT(*) FILTER (WHERE TRUE) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
    SUM(amount) FILTER (WHERE status = 'paid') / NULLIF(COUNT(*) FILTER (WHERE status = 'paid'), 0) AS avg_order_value
FROM orders;

六、 同比/環比計算

WITH daily AS (
    SELECT DATE(created_at) AS dt, SUM(amount) AS daily_gmv
    FROM orders
    WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
    GROUP BY DATE(created_at)
)
SELECT 
    d1.dt,
    d1.daily_gmv,
    d2.daily_gmv AS gmv_same_day_last_week,
    d3.daily_gmv AS gmv_same_day_last_month,
    CASE WHEN d2.daily_gmv > 0 THEN (d1.daily_gmv - d2.daily_gmv) / d2.daily_gmv * 100 ELSE NULL END AS wow_pct,
    CASE WHEN d3.daily_gmv > 0 THEN (d1.daily_gmv - d3.daily_gmv) / d3.daily_gmv * 100 ELSE NULL END AS yoy_pct
FROM daily d1
LEFT JOIN daily d2 ON d2.dt = DATE_SUB(d1.dt, INTERVAL 7 DAY)
LEFT JOIN daily d3 ON d3.dt = DATE_SUB(d1.dt, INTERVAL 30 DAY)
ORDER BY d1.dt DESC;

七、 性能優化清單

1. 時間範圍查詢：優先使用 WHERE col >= 'start' AND col < 'end'，避免 DATE(col) = '...' 導致索引失效。
2. 分組列建索引：高頻 GROUP BY 列創建索引或組合索引前綴。
3. 大表聚合：預聚合表/物化視圖/OLAP 引擎，避免實時全表掃描。
4. TopN 深分頁：避免 LIMIT 100000, 10，改用游標/ID 範圍查詢。
5. 只選所需列：禁止 SELECT *，減少網絡與內存開銷。