1. MySQL数据汇总实战CASE WHEN与GROUP BY的黄金组合上周排查一个报表性能问题时我发现团队里不少初级开发对CASE WHEN和GROUP BY的组合使用存在理解偏差——有人用十几行子查询实现的逻辑其实两行CASE WHEN就能搞定。这种条件统计在业务系统中实在太常见了统计各状态订单量、计算不同年龄段用户数、汇总各品类销售额占比...今天我就用电商场景的实例带你掌握这个高效的数据汇总技巧。2. CASE WHEN的本质SQL中的条件表达式2.1 基础语法结构CASE WHEN本质上是一个条件表达式其标准结构如下CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END与编程语言中的switch-case不同MySQL的CASE WHEN是顺序执行的——当第一个满足的条件出现后就会返回对应结果后续条件不再判断。这特性在优化查询时非常有用。2.2 两种常见形式对比我经常看到这两种写法被混用-- 简单CASE表达式值比较 CASE column_name WHEN value1 THEN result1 WHEN value2 THEN result2 ... END -- 搜索CASE表达式条件判断 CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... END关键区别在于前者只能做等值比较后者可以包含任何条件表达式如WHEN salary 10000 THEN 高薪。实际项目中我推荐始终使用搜索表达式灵活性更高。3. GROUP BY分组聚合的隐藏技巧3.1 标准分组统计最基础的分组统计大家都会SELECT department, COUNT(*) as employee_count FROM employees GROUP BY department但遇到需要多维度交叉统计时新手往往会写多个独立查询。比如要同时统计各部门的薪资等级分布3.2 进阶交叉统计方案低效做法-- 查询1统计高薪人数 SELECT department, COUNT(*) FROM employees WHERE salary 10000 GROUP BY department; -- 查询2统计中薪人数 SELECT department, COUNT(*) FROM employees WHERE salary BETWEEN 5000 AND 10000 GROUP BY department;高效方案SELECT department, COUNT(*) AS total, SUM(CASE WHEN salary 10000 THEN 1 ELSE 0 END) AS high_salary_count, SUM(CASE WHEN salary BETWEEN 5000 AND 10000 THEN 1 ELSE 0 END) AS medium_salary_count FROM employees GROUP BY department这种单次扫描条件计数的方式性能通常比多个查询高3-5倍特别是在大表场景下。4. 电商订单分析实战案例假设有订单表orders结构如下CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), status ENUM(pending,paid,shipped,completed,cancelled), create_time DATETIME );4.1 场景一状态分布统计需求统计各状态订单数及占总订单比例SELECT COUNT(*) AS total_orders, SUM(CASE WHEN status pending THEN 1 ELSE 0 END) AS pending_count, SUM(CASE WHEN status paid THEN 1 ELSE 0 END) AS paid_count, SUM(CASE WHEN status shipped THEN 1 ELSE 0 END) AS shipped_count, ROUND(SUM(CASE WHEN status completed THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS completed_percent FROM orders4.2 场景二时段销售分析需求分析不同时间段的销售额分布SELECT SUM(amount) AS total_sales, SUM(CASE WHEN HOUR(create_time) BETWEEN 7 AND 12 THEN amount ELSE 0 END) AS morning_sales, SUM(CASE WHEN HOUR(create_time) BETWEEN 13 AND 18 THEN amount ELSE 0 END) AS afternoon_sales, SUM(CASE WHEN HOUR(create_time) BETWEEN 19 AND 23 THEN amount ELSE 0 END) AS evening_sales FROM orders WHERE create_time BETWEEN 2023-01-01 AND 2023-12-314.3 场景三用户分层统计需求按消费金额将用户分层统计SELECT CASE WHEN total_spend 10000 THEN 钻石用户 WHEN total_spend 5000 THEN 黄金用户 WHEN total_spend 1000 THEN 白银用户 ELSE 普通用户 END AS user_level, COUNT(*) AS user_count, SUM(total_spend) AS total_revenue FROM ( SELECT user_id, SUM(amount) AS total_spend FROM orders GROUP BY user_id ) AS user_stats GROUP BY user_level ORDER BY total_revenue DESC5. 性能优化与避坑指南5.1 索引使用策略CASE WHEN条件中的列如果没索引会导致全表扫描。对于高频查询建议添加复合索引-- 为状态统计场景优化 ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time); -- 为时段分析场景优化 ALTER TABLE orders ADD INDEX idx_create_time_amount (create_time, amount);5.2 NULL值处理陷阱当CASE WHEN没有ELSE子句且所有条件都不满足时会返回NULL。这可能导致统计误差-- 有风险的写法 SUM(CASE WHEN status cancelled THEN 1 END) -- 可能包含NULL -- 安全写法 SUM(CASE WHEN status cancelled THEN 1 ELSE 0 END)5.3 大数据量优化当处理百万级以上数据时可以考虑使用物化视图预计算在应用层分片处理用WITH ROLLUP获取小计和总计SELECT status, COUNT(*) AS count FROM orders GROUP BY status WITH ROLLUP6. 真实业务中的复杂案例最近我们实现了一个促销活动效果分析报表需要同时统计各渠道带来的订单量不同商品类别的转化率各时间段的客单价分布最终方案简化版SELECT channel, COUNT(DISTINCT o.order_id) AS order_count, SUM(CASE WHEN p.category electronics THEN 1 ELSE 0 END) AS electronic_orders, SUM(o.amount) / COUNT(DISTINCT o.user_id) AS avg_order_value, SUM(CASE WHEN HOUR(o.create_time) BETWEEN 9 AND 17 THEN o.amount ELSE 0 END) AS daytime_sales FROM orders o JOIN products p ON o.product_id p.id WHERE o.create_time BETWEEN 2023-06-01 AND 2023-06-30 GROUP BY channel这个查询用到了多表JOIN时间函数条件聚合去重计数计算字段执行时间从原来的8秒优化到1.2秒关键就在于合理利用CASE WHEN减少查询次数。