萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

常用統計求和 SQL 語句範例大全:按時間維度、Top N、分組聚合實戰

基礎聚合函式速查

函式說明
SUM(col)求和
COUNT(*)行數(含 NULL)
COUNT(col)非 NULL 行數
AVG(col)平均值
MAX/MIN(col)最大/最小值
GROUP_CONCAT(col)分組串聯(MySQL)

範例一:指定時間區間金額總和

-- 表: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'; -- 含首不含尾,避免時分秒邊界問題

範例二:查詢記錄並同時統計總和(窗口函數)

-- MySQL 8.0+ / PostgreSQL / SQL Server
SELECT 
    id, amount, created_at,
    SUM(amount) OVER () AS grand_total,
    SUM(amount) OVER (PARTITION BY DATE(created_at)) AS daily_total
FROM sales
WHERE created_at >= '2024-01-01';

MySQL 5.7 相容寫法:子查詢或變數模擬。

範例三:按年/月/日多維度銷售統計

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

-- 年度統計
SELECT YEAR(ordertime) AS yr, SUM(money) AS yearly_total
FROM orders
GROUP BY YEAR(ordertime)
ORDER BY yr;

-- 月度統計
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;

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

範例四:銷量 Top N 商品(含月份篩選)

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

-- 某月銷量前 10
SELECT product_name, 
       SUM(price) AS total_revenue,
       COUNT(*) AS qty_sold
FROM sales
WHERE MONTH(sale_date) = 7  -- 7月
  AND YEAR(sale_date) = 2024
GROUP BY product_name
ORDER BY qty_sold DESC
LIMIT 10;

-- 通用 Top N 寫法(標準 SQL)
SELECT * FROM (
    SELECT product_name, COUNT(*) AS qty,
           ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
    FROM sales
    WHERE sale_date >= '2024-07-01' AND sale_date < '2024-08-01'
    GROUP BY product_name
) t WHERE rn <= 10;

進階技巧

1. 條件聚合(單次掃描多指標)

SELECT 
    SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_total,
    SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refund_total,
    COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders
FROM orders;

2. 累積求和(Running Total)

SELECT 
    dt, 
    daily_total,
    SUM(daily_total) OVER (ORDER BY dt) AS cumulative_total
FROM (
    SELECT DATE(ordertime) AS dt, SUM(money) AS daily_total
    FROM orders
    GROUP BY DATE(ordertime)
) d;

3. 百分比佔比

SELECT 
    category,
    SUM(amount) AS cat_total,
    ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 2) AS pct
FROM sales
GROUP BY category;

效能優化關鍵

  1. 索引覆蓋:WHERE + GROUP BY 欄位建立複合索引
  2. 分區表:大量歷史資料按時間分區,查詢自動裁剪分區
  3. 預聚合表:每日/每小時定時任務彙總寫入 summary 表,查詢秒級回應
  4. 避免 SELECT *:只取需要欄位

總結

掌握 GROUP BY、窗口函數、條件聚合、時間維度分組,即可覆蓋 95% 統計報表需求。重點在於索引設計配合查詢模式,以及大資料量下的預聚合策略。

copyright © 萬盛學電腦網 all rights reserved