数据:
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 |
+----------+-------------+---------------+