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

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;错误2GROUP 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秒内完成。关键是要理解数据特点合理设计查询逻辑。