case when简单案例

数据：

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

建表：

0: jdbc:hive2://hadoop:11240> create table dept(name string,dept_id string,sex string)
. . . . . . . . . . . . . . > row format delimited
. . . . . . . . . . . . . . > fields terminated by '\t';
0: jdbc:hive2://hadoop:11240> load data local inpath '/home/xiaokang/hivedata/dept.txt'
. . . . . . . . . . . . . . > into table dept;
0: jdbc:hive2://hadoop:11240> select * from dept;
+------------+---------------+-----------+
| dept.name  | dept.dept_id  | dept.sex  |
+------------+---------------+-----------+
| 悟空         | A             | 男         |
| 大海         | A             | 男         |
| 宋宋         | B             | 男         |
| 凤姐         | A             | 女         |
| 婷姐         | B             | 女         |
| 婷婷         | B             | 女         |
+------------+---------------+-----------+

需求：求出不同部门男女各多少人

#方法一：
0: jdbc:hive2://hadoop:11240> select dept_id,
. . . . . . . . . . . . . . > sum(case sex when '男' then 1 else 0 end) male_count,
. . . . . . . . . . . . . . > sum(case sex when '女' then 1 else 0 end) female_count
. . . . . . . . . . . . . . > from dept group by dept_id;
+----------+-------------+---------------+
| dept_id  | male_count  | female_count  |
+----------+-------------+---------------+
| A        | 2           | 1             |
| B        | 1           | 2             |
+----------+-------------+---------------+

#方法二：
0: jdbc:hive2://hadoop:11240> select dept_id,
. . . . . . . . . . . . . . > sum(if(sex="男",1,0)) male_count,
. . . . . . . . . . . . . . > sum(if(sex="女",1,0)) female_count
. . . . . . . . . . . . . . > from dept group by dept_id;
+----------+-------------+---------------+
| dept_id  | male_count  | female_count  |
+----------+-------------+---------------+
| A        | 2           | 1             |
| B        | 1           | 2             |
+----------+-------------+---------------+