1.case when

原始数据如下：

要求：统计各部门每个性别有多少人
使用传统的group by能达到上述要求：

select dept_id,sex,count(*) from emp_sex
group by dept_id,sex;

但如果查询的结果以dept_id和两个性别为列，那么group by就不行了，这时可以使用case when：

select dept_id,
sum(case sex when '男' then 1 else 0 end) male,
sum(case sex when '女' then 1 else 0 end) female
from emp_sex
group by dept_id;

2.collect_list/set(行转列）

原始数据如下：

要求：将星座和血型相同的人放到一起，结果如下所示：

思路：

• step1:
  先把问题简化，如果不是求相同星座、血型的人的名字，而是求人数，那么就和上例一样，使用group by就可以做到：

select constellation,blood_type,count(*) total
from person_info
group by constellation,blood_type;

• step2:
  现在考虑把前两列合并起来：使用concat可以做到：

select concat(constellation,',',blood_type) group_,count(*) total
from person_info
group by constellation,blood_type;

• step3:
  最后，只需要把人数换成姓名就可以，这里我们可以使用collect_list或collect_set实现，collect_list(xxx)将同一组里面的xxx聚合成一个数组（不去重），collect_set将同一组的xxx聚合成一个数组（去重）

select concat(constellation,',',blood_type) group_,collect_set(name) name_list
from person_info
group by constellation,blood_type;

• step4:
  可以看到，现在呈现出来的name还是一个数组，和我们最终想要的结果还有些许不同，我们最终想把多个名字以一个字符串的形式展现出来，使用concat_ws可以实现，concat_ws将数组里面的内容用指定分隔符连接起来

select concat(constellation,',',blood_type) group_,
concat_ws(',',collect_set(name)) name_list
from person_info
group by constellation,blood_type;

3.explode(列转行)

原始数据如下：

要求：将每部电影的多个分类切分开，结果如下所示：

思路：

• step1：
  原本的category是一个字符串，现在要把它切分开，使用split函数可以实现

select split(category,',') cate_split from movie_info ;

• step2:
  经过split后，每一行的cate_split都是一个array，现在要把array中的每个元素取出来单独成行，explode函数可以实现，explode函数可以将一个array或者map展开，其中explode(array)使得结果中将array列表里的每个元素生成一行；explode(map)使得结果中将map里的每一对元素作为一行，key为一列，value为一列。

select explode(split(category,',')) cate_split from movie_info;

• step3:
  与各自的电影名字对应起来，如果直接select会报错：
  这是因为explode是一个udtf函数，即一个输入，对应多个输出；那么一个电影名字，就会对应多个电影种类，这样是不能直接匹配的，使用lateral view来解决该问题:

select
    m.movie,
    cate_view.cate
from
    movie_info m
lateral view 
explode(split(category, ",")) cate_view as cate ;

lateral view 生成一个视图（也就是生成一张虚拟表），cate_view是生成的这个view的名字，cate是这个列的列名。
拓展练习： explode炸开map类型的数据
原始数据：


要求：求每个人的孩子及孩子的年龄

select explode(children) from test;

select t.name father_name,child_info.child_name,child_info.child_age
from test t
lateral view
explode(children) child_info as child_name,child_age;

4.行列转换结合

上述电影数据，求每个电影种类对应哪些电影

• step1：
  先用explode把每部电影对应的多个种类炸开

select
    m.movie,
    cate_view.cate
from
    movie_info m
lateral view 
explode(split(category, ",")) cate_view as cate ;

• step2：
  然后使用group by和collect_set把相同种类的电影聚集到一起

select cate,concat_ws('~',collect_set(movie)) movie_list
from (
select
    m.movie,
    cate_view.cate
from
    movie_info m
lateral view 
explode(split(category, ",")) cate_view as cate
) my_table
group by cate;