MySQL：聚合函数与分组查询实战精讲

weixin_30326515

99人浏览 · 2026-07-12 10:58:25

weixin_30326515 · 2026-07-12 10:58:25 发布

1. 为什么需要聚合函数和分组查询？

想象你是一家电商公司的数据分析师，老板让你统计最近三个月每个品类的销售总额、平均订单金额和订单数量。如果手动计算，你需要先按品类分类，再逐个求和、求平均——这工作量简直让人崩溃。而MySQL的聚合函数和分组查询就是为解决这类问题而生的。

聚合函数就像是一个智能计算器，能自动对一组数据进行统计计算。比如：

SUM() 帮你求和
AVG() 自动算平均数
COUNT() 快速计数

而分组查询（GROUP BY）则像是一个自动分类器，能按照你指定的列（比如商品类别）将数据分成若干组，再对每个组应用聚合函数。两者结合使用，就能轻松完成老板交代的统计任务。

2. 五大核心聚合函数详解

2.1 求和与平均：SUM()和AVG()

这两个函数专门处理数值型数据。我最近用它们分析过销售数据：

-- 计算所有商品销售总额
SELECT SUM(amount) AS total_sales FROM orders;

-- 计算手机类目的平均订单金额
SELECT AVG(amount) AS avg_order 
FROM orders 
WHERE category = '手机';

踩坑提醒 ：AVG()计算时默认忽略NULL值。如果某商品的amount是NULL，它不会计入分母。如果需要将NULL视为0，可以这样写：

SELECT AVG(IFNULL(amount,0)) FROM orders;

2.2 极值函数：MAX()和MIN()

这两个函数很灵活，能处理数字、字符串甚至日期：

-- 找出最贵的商品价格（数字）
SELECT MAX(price) FROM products;

-- 找出字母排序最后的商品名（字符串）
SELECT MAX(product_name) FROM products;

-- 找出最早的注册日期（日期）
SELECT MIN(register_date) FROM users;

实测发现 ：对ENUM类型字段使用MAX()/MIN()时，MySQL比较的是实际存储的数值而非字符串值，这点要特别注意。

2.3 计数函数COUNT()的三种用法

COUNT()有几种常见写法，效果大不同：

-- 统计总行数（推荐）
SELECT COUNT(*) FROM orders;  

-- 统计非空的user_id数量
SELECT COUNT(user_id) FROM orders;

-- 统计不重复的用户数
SELECT COUNT(DISTINCT user_id) FROM orders;

性能对比 ：在InnoDB引擎下，COUNT(*)和COUNT(1)性能相当，都比COUNT(列名)快，因为前者可以直接读取索引统计信息。

3. GROUP BY分组实战技巧

3.1 单列分组基础用法

先看一个简单的分组统计：

-- 按部门统计平均薪资
SELECT 
    department,
    AVG(salary) AS avg_salary,
    COUNT(*) AS emp_count
FROM employees
GROUP BY department;

易错点 ：SELECT中的非聚合列必须出现在GROUP BY中。以下写法会报错：

-- 错误示例！
SELECT 
    employee_name,  -- 未出现在GROUP BY中
    department,
    AVG(salary)
FROM employees
GROUP BY department;

3.2 多列分组与ROLLUP

当需要多维分析时，可以用多列分组：

-- 按部门和职位统计薪资
SELECT 
    department,
    job_title,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title;

如果需要小计和总计，可以加上WITH ROLLUP：

-- 带层级汇总的分组
SELECT 
    IFNULL(department, '所有部门') AS department,
    IFNULL(job_title, '全部职位') AS job_title,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title WITH ROLLUP;

注意：ROLLUP与ORDER BY不能同时使用，且NULL值会被ROLLUP用作汇总行的占位符。

4. HAVING与WHERE的过滤区别

4.1 基础过滤对比

WHERE和HAVING都用于过滤，但时机不同：

WHERE在分组前过滤原始数据
HAVING在分组后过滤聚合结果

-- 先过滤再分组（效率高）
SELECT 
    category,
    AVG(price) AS avg_price
FROM products
WHERE price > 100  -- 先排除低价商品
GROUP BY category;

-- 先分组再过滤
SELECT 
    category,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING avg_price > 1000;  -- 筛选高均价品类

4.2 性能优化建议

在大数据量下，WHERE能显著提高性能：

-- 高效写法
SELECT 
    user_id,
    COUNT(*) AS order_count
FROM orders
WHERE create_time > '2023-01-01'  -- 先缩小数据范围
GROUP BY user_id
HAVING order_count > 5;

-- 低效写法（不推荐）
SELECT 
    user_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING order_count > 5 
   AND MIN(create_time) > '2023-01-01';

5. 完整SQL执行顺序解析

理解执行顺序能避免很多错误：

FROM ：确定数据来源
WHERE ：行级过滤
GROUP BY ：分组
HAVING ：组级过滤
SELECT ：选择字段
ORDER BY ：排序
LIMIT ：限制行数

举个例子：

SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 10000
ORDER BY avg_salary DESC
LIMIT 5;

这个查询的执行流程是：

从employees表取出数据
筛选2020年后入职的员工
按部门分组
过滤出平均薪资>1万的部门
计算每个部门的平均薪资
按平均薪资降序排列
只返回前5条记录

6. 实际业务场景案例

6.1 销售数据分析

假设需要分析季度销售数据：

SELECT 
    product_category,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order,
    COUNT(DISTINCT user_id) AS customer_count,
    MAX(amount) AS max_order
FROM sales
WHERE quarter = '2023-Q2'
GROUP BY product_category
HAVING total_sales > 100000
ORDER BY total_sales DESC;

6.2 用户行为统计

分析用户活跃度：

SELECT 
    user_level,
    COUNT(*) AS active_users,
    AVG(login_count) AS avg_logins,
    SUM(CASE WHEN last_login > CURDATE() - INTERVAL 7 DAY THEN 1 ELSE 0 END) 
        AS recent_active_users
FROM users
WHERE status = 'active'
GROUP BY user_level
HAVING active_users > 100;

7. 常见错误与解决方案

错误1 ：在WHERE中使用聚合函数

-- 错误写法
SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 10000  -- 聚合函数不能用在WHERE
GROUP BY department;

-- 正确写法
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 10000;

错误2 ：GROUP BY遗漏非聚合列

-- 错误写法
SELECT 
    product_id,
    product_name,  -- 未出现在GROUP BY中
    AVG(price)
FROM products
GROUP BY product_id;

-- 正确写法
SELECT 
    product_id,
    product_name,
    AVG(price)
FROM products
GROUP BY product_id, product_name;

错误3 ：混淆COUNT用法

-- 统计总行数（包含NULL行）
SELECT COUNT(*) FROM table;

-- 统计某列非NULL值数量
SELECT COUNT(column) FROM table;

-- 统计某列去重后的数量
SELECT COUNT(DISTINCT column) FROM table;

8. 性能优化技巧

为GROUP BY列添加索引 ：特别是大表分组时，索引能显著加快分组速度
先缩小数据范围 ：先用WHERE过滤再分组，减少处理的数据量
避免过度分组 ：只选择必要的分组列
考虑使用派生表 ：对大数据集可以先过滤再分组

-- 优化后的查询示例
SELECT 
    category,
    AVG(price) AS avg_price
FROM (
    SELECT category, price 
    FROM products
    WHERE create_date > '2023-01-01'
) AS recent_products
GROUP BY category;

在实际项目中，我曾用这些优化技巧将一个原本需要30秒的报表查询优化到2秒内完成。关键是要理解数据特点，合理设计查询逻辑。