case when语法练习

题目需求

1）数据准备
name dept_id sex
悟空	A	男
大海	A	男
宋宋	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女
2）需求
求出不同部门男女各多少人。结果如下：
dept_Id     男       女
A     		2       1
B     		1       2

测试数据

vi emp_sex.txt
悟空	A	男
大海	A	男
宋宋	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女

建表并上传数据

create table emp_sex(
name string, 
dept_id string, 
sex string) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/hive/datas/emp_sex.txt' into table emp_sex;

过程分析

--过程分析
/*
 结果只有两行---->行数减少了,说明肯定用到group by
 结果有三列,我们可以在添加一列sex
 */
select dept_id,
       sex,
       sex
from emp_sex;
-- 结果有6行,我们可以group by count一下
select dept_id,
       count(sex),
       count(sex)
from emp_sex
group by dept_id;
--发现离结果很接近,但是二三列值都是3.显然count()不行
--思考,我们有没有办法将男,女用1/0替代,然后进行sum求和?
select dept_id,
       case sex when '男' then 1 else 0 end as `男`,
       case sex when '女' then 1 else 0 end as `女`
from emp_sex
group by dept_id, sex; --t1
select t1.dept_id,
       sum(t1.`男`),
       sum(t1.`女`)
from (
         select dept_id,
                case sex when '男' then 1 else 0 end `男`,
                case sex when '女' then 1 else 0 end `女`
         from emp_sex
     ) t1
group by t1.dept_id;

最终代码

--最终结果
--方式1
select dept_id,
       sum(case sex when '男' then 1 else 0 end) as `男`,
       sum(case sex when '女' then 1 else 0 end) as `女`
from emp_sex
group by dept_id, sex;
--方式2
select dept_id,
       sum(if(sex='男',1,0)) `男`,
       sum(if(sex='女',1,0)) `女`
from emp_sex
group by dept_id;

知识点总结

/*
 知识点复习:case when 语法
case 列 when 常量  then 结果
       when  常量  then 结果
       ....
       else 结果n
end
 知识点复习:case when 可以理解为输出只有一个的函数,这种输出只有一个的函数可以用到sql的任意位置
 */