SQL 聚合函数

聚合函数对一组值进行计算，返回单个值。

常用聚合函数

函数	描述
COUNT()	统计数量
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

演示表

假设有 orders 表：

id	user_id	product	category	amount	quantity	order_date
1	1	iPhone	Electronics	8000	1	2024-01-15
2	1	MacBook	Electronics	10000	1	2024-02-20
3	2	iPad	Electronics	3000	2	2024-01-10
4	3	Keyboard	Electronics	500	3	2024-03-05
5	4	Mouse	Electronics	200	1	2024-02-28

COUNT() 计数

统计所有行

-- 统计订单总数
SELECT COUNT(*) FROM orders;

统计非 NULL 值

-- 统计有产品名称的订单
SELECT COUNT(product) FROM orders;

统计去重

-- 统计有多少不同的用户下过单
SELECT COUNT(DISTINCT user_id) FROM orders;

SUM() 求和

-- 统计总销售额
SELECT SUM(amount) FROM orders;

-- 统计总销售数量
SELECT SUM(quantity) FROM orders;

带条件

-- 某个用户的总消费
SELECT SUM(amount) FROM orders WHERE user_id = 1;

AVG() 平均值

-- 平均订单金额
SELECT AVG(amount) FROM orders;

-- 平均订单数量
SELECT AVG(quantity) FROM orders;

MAX() / MIN() 最大值/最小值

-- 最高订单金额
SELECT MAX(amount) FROM orders;

-- 最低订单金额
SELECT MIN(amount) FROM orders;

-- 最贵的商品
SELECT MAX(product) FROM orders;

GROUP BY 分组

基本语法

SELECT 列1, 聚合函数(列2)
FROM 表名
GROUP BY 列1;

按用户统计

-- 统计每个用户的订单数量
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

结果：

| user_id | order_count |
|---------|-------------|
| 1       | 2           |
| 2       | 1           |
| 3       | 1           |
| 4       | 1           |

按用户统计消费总额

-- 统计每个用户的消费总额
SELECT 
    user_id, 
    SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;

多列分组

-- 按类别和日期分组
SELECT 
    category, 
    order_date,
    SUM(amount) AS daily_amount
FROM orders
GROUP BY category, order_date;

HAVING 分组过滤

WHERE 用于过滤行，HAVING 用于过滤分组。

-- 统计消费超过5000的用户
SELECT 
    user_id, 
    SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 5000;

WHERE vs HAVING

-- WHERE: 过滤行（在分组前）
-- 金额大于1000的订单，按用户分组
SELECT user_id, SUM(amount)
FROM orders
WHERE amount > 1000
GROUP BY user_id;

-- HAVING: 过滤分组（在分组后）
-- 总消费大于5000的用户
SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 5000;

综合示例

统计报表

SELECT 
    category AS 类别,
    COUNT(*) AS 订单数,
    SUM(quantity) AS 总数量,
    SUM(amount) AS 总金额,
    AVG(amount) AS 平均金额,
    MAX(amount) AS 最高金额,
    MIN(amount) AS 最低金额
FROM orders
GROUP BY category;

结果：

| 类别       | 订单数 | 总数量 | 总金额 | 平均金额 | 最高金额 | 最低金额 |
|------------|--------|--------|--------|----------|----------|----------|
| Electronics| 5      | 8      | 21700  | 4340     | 10000    | 200      |

复杂查询

-- 统计每个用户的订单数、消费总额、平均消费
SELECT 
    u.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_amount,
    COALESCE(AVG(o.amount), 0) AS avg_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0
ORDER BY total_amount DESC;

常见错误

1. SELECT 中包含非聚合列

-- 错误：product 没有被聚合
SELECT product, COUNT(*)
FROM orders
GROUP BY user_id;

-- 正确：要么聚合，要么放在 GROUP BY 中
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id;

2. 忘记 GROUP BY

-- 错误
SELECT user_id, SUM(amount) FROM orders;

-- 正确
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

3. HAVING 位置错误

-- 错误：HAVING 必须在 GROUP BY 之后
SELECT user_id, SUM(amount)
FROM orders
HAVING SUM(amount) > 5000
GROUP BY user_id;

-- 正确
SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 5000;

小结

本章我们学习了：

1. 常用聚合函数（COUNT, SUM, AVG, MAX, MIN）
2. GROUP BY 分组
3. HAVING 分组过滤
4. 综合查询

练习

1. 统计所有订单的总金额
2. 统计每个类别的订单数量和总金额
3. 找出消费最高的用户
4. 统计消费超过10000的用户