mysql中可以使用CASE WHEN函数完成数据分组。
CASE WHEN函数用来对数据进行判断和分组
来自MySQL触发器里的流程控制语句 知识。
CASE WHEN是SQL编程中常用的条件控制语句。
CASE WHEN的功能:
- 新数据项加工
用途:根据现有字段,利用Case When语句进行逻辑判断,可加工得到新的字段。
比如计算得到新字段“年龄层”、“资产等级”
- 汇总信息加工
用途:Case When 语句与汇总函数(如Sum函数)相结合,能够实现更加灵活的汇总信息加工功能。
Tips:想对group by之后对某些字段进行统计,使用很方便
就好比groupby之后想对性别进行统计
- 筛选条件控制
用途:Case When 还可以用在 筛选条件中,以实现更加灵活的筛选条件控制。
case when 的语法有两种:这两种语法有什么区别呢?
1、简单函数:枚举这个字段所有可能的值*
CASE [col_name]
WHEN [value1] THEN [result1]
WHEN [value2] THEN [result2]
…
ELSE [default]
END
2、搜索函数(条件判断)
可以写判断,并且搜索函数只会返回第一个符合条件的值,其他case被忽略
CASE
WHEN [expr] THEN [result1]
WHEN [expr] THEN [result2]
…
ELSE [default]
END
-
新数据项加工
例;(简单函数)根据英雄的名字,匹配属于他们的装备(“装备”为新数据项)
SELECT
NAME '英雄',
CASE NAME
WHEN '德莱文' THEN '斧子'
WHEN '德玛西亚-盖伦' THEN '大宝剑'
ELSE '无'
END [as] '装备'
FROM user_info;
例:(搜索函数)根据年龄,创建新数据项“年龄段”,字段分组
-- when 表达式中可以使用 and 连接条件
SELECT
NAME '英雄', age '年龄',
CASE
WHEN age < 18 THEN '少年'
WHEN age < 30 THEN '青年'
WHEN age >= 30 AND age < 50 THEN '中年'
ELSE '老年'
END [as] '年龄段'
FROM user_info;
-
汇总信息加工
例:(配合聚集函数)统计80前与80后的人中,男、女客户的人数。
SELECT
CASE WHEN birth_dt < mdy(1,1,1980) THEN '80前' ELSE '80后' END as 年龄段
, SUM( CASE WHEN gender = '1' THEN 1 ELSE 0 END) as 男性数量
, SUM( CASE WHEN gender = '2' THEN 1 ELSE 0 END) as 女性数量
FROM custom_info
GROUP BY 年龄段
问题:group by根据年龄段,但是好像没看到分组后的聚集,思考SQL执行过程,像是先得到新数据项‘年龄段’,再根据年龄段进行男女数量的统计。
例:按cls_id统计每个班的男、女生数量
use sc_sys;
-- 查看学生表(结果1)
SELECT * FROM tb_student;
-- 按cls_id 分班,创建新的数据项“班别”(结果2)
SELECT *,
CASE cls_id
WHEN 1 THEN '1班'
WHEN 2 THEN '2班'
WHEN 3 THEN '3班'
WHEN 4 THEN '4班'
WHEN 5 THEN '5班'
WHEN 6 THEN '6班'
WHEN 7 THEN '7班'
WHEN 8 THEN '8班'
ELSE '其他班级'
END as 班别
FROM tb_student ORDER BY 班别, ssex DESC;
-- 按cls_id创建班别,并统计每个班别的男、女生数量(结果3)
SELECT
CASE cls_id
WHEN 1 THEN '1班'
WHEN 2 THEN '2班'
WHEN 3 THEN '3班'
WHEN 4 THEN '4班'
WHEN 5 THEN '5班'
WHEN 6 THEN '6班'
WHEN 7 THEN '7班'
WHEN 8 THEN '8班'
ELSE '其他班级'
END 班别
, SUM( CASE WHEN ssex = '男' THEN 1 ELSE 0 END) as 男性数量
, SUM( CASE WHEN ssex = '女' THEN 1 ELSE 0 END) as 女性数量
FROM tb_student
GROUP BY 班别;
Tips:想对group by之后对某些字段进行统计,使用很方便
就好比groupby之后想对性别进行统计
-- 按cls_id创建班别,并统计每个班别的男、女生数量(结果4)
SELECT
CASE cls_id
WHEN 1 THEN '1班'
WHEN 2 THEN '2班'
WHEN 3 THEN '3班'
WHEN 4 THEN '4班'
WHEN 5 THEN '5班'
WHEN 6 THEN '6班'
WHEN 7 THEN '7班'
WHEN 8 THEN '8班'
ELSE '其他班级'
END 班别, ssex, COUNT(*) 数量
FROM tb_student
GROUP BY 班别, ssex DESC
ORDER BY 班别;
-- 若报错,将班别改为class,即字段命名最好还用英文
-- Unknown column '班別' in 'order clause'
-
筛选条件控制
例:筛选目标客户名单,
对于0200地区,以资产大于等于100万的客户为目标客户,
对于其它地区,以资产大于等于50万为目标客户。
输出目标客户的客户编号、姓名、手机号码、地区号和总资产。
SELECT Party_Id, Name, Mobile, Zone_Num, Total_Asset
FROM Custom_Info
WHERE CASE WHEN Zone_Num='0200' THEN Total_Asset>=1000000
ELSE Total_Asset>=500000
END
-- 即针对0200地区,使用条件Total_Asset>=1000000
-- 而针对其他地区,使用条件Total_Asset>=500000
-
其他功能:行转列
例:聚合函数 sum 配合 case when 的简单函数实现行转列,别名
-- 聚合函数 sum 配合 case when 的简单函数实现行转列
SELECT st.stu_id '学号', st.stu_name '姓名',
sum( CASE co.course_name WHEN '大学语文' THEN sc.scores ELSE 0 END ) '大学语文',
sum( CASE co.course_name WHEN '新视野英语' THEN sc.scores ELSE 0 END ) '新视野英语',
FROM edu_student st
LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
LEFT JOIN edu_courses co ON co.course_no = sc.course_no
GROUP BY st.stu_id
ORDER BY NULL;
-- 注释:group by后默认排序 后面跟上order by null表示 不排序,查询速度更快。
这种功能有点像之前的帖子想要实现的功能,《数据划分处理(基于python的pandas中的dataframe数据结构)》,链接https://blog.csdn.net/Cameback_Tang/article/details/102876947
Case when函数还能用于groupby中
-- 查询output的分布
select
case when output < -500 then '(, -500)'
when output < -250 then '[-500, -250)'
when output < -200 then '[-250, -200)'
when output < -128 then '[-200, -128)'
when output < -88 then '[-128, -88)'
when output < -18 then '[-88, -18)'
when output < 0 then '[-18, 0)'
when output = 0 then '0'
else 'else' end output_bin
, count(user_id) cnt
from data_table
group by case when output < -500 then '(, -500)'
when output < -250 then '[-500, -250)'
when output < -200 then '[-250, -200)'
when output < -128 then '[-200, -128)'
when output < -88 then '[-128, -88)'
when output < -18 then '[-88, -18)'
when output < 0 then '[-18, 0)'
when output = 0 then '0'
else 'else' end