| 函式 | 說明 |
|---|---|
| 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;
-- 表: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;
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;
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;
SELECT
category,
SUM(amount) AS cat_total,
ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 2) AS pct
FROM sales
GROUP BY category;
WHERE + GROUP BY 欄位建立複合索引掌握 GROUP BY、窗口函數、條件聚合、時間維度分組,即可覆蓋 95% 統計報表需求。重點在於索引設計配合查詢模式,以及大資料量下的預聚合策略。