Hive中的两种行列转换
- 1.将列上的多个值打成一行（多列）的行列转换
- 案例一
- 场景一（列转行）
- 场景二（行转列）
- 2.字符串合并与拆分形式的行列转换
- 案例二
- 场景一（列转行）
- 场景二（行转列）
- 总结
1.将列上的多个值打成一行（多列）的行列转换
案例一
场景一（列转行）
数据如下：
name item score
张三 数学 58
张三 英语 83
张三 语文 89
李四 数学 67
李四 英语 35
李四 语文 92
王五 数学 75
王五 英语 88
王五 语文 70要求将每个人各科成绩打在一行上，分成三列显示，即，目标数据如下：
select
name,
sum(case when item='数学' then score end) as math,
sum(case when item='英语' then score end) as english,
sum(case when item='语文' then score end) as chinese
from col_to_row
group by name;查询结果：
上述使用的sum聚合函数只是为了分组，这里使用max和min也可以达到同样的效果
场景二（行转列）
现有上述查询结果的数据，要求查询出最开始的数据，这里用到一个比较有趣的函数，str_to_map(详解见文末)，先用用concat + & 取表字段拼接成map类型，然后用str_to_map函数根据"&“和组成map时候的KV键值对的中间的符号，一般为”=“或”:"，将KV值打在两列上。lateral view explode在案例二详细介绍
select b.name name,
a.item item,
a.score score
from row_to_col b
lateral view explode(
str_to_map(concat('数学=',math,'&英语=',english,'&语文=',chinese),'&','=')
) a as item,score;注：row_to_col是根据上述查询落的表
结果如下：
str_to_map(字符串参数, 分隔符1, 分隔符2)
使用两个分隔符将文本拆分为键值对。
分隔符1将文本分成KV对，分隔符2分割每个KV对。对于分隔符1默认分隔符是 ‘,’，对于分隔符2默认分隔符是 ‘=’。
2.字符串合并与拆分形式的行列转换
我们在工作中经常会遇到这种情况，数据中的一对多情况，需要变成一对一，把多的那一部分拼接到一个字段里面显示，比如一个代表处下面有多个办事处，但是现在需要把多个办事处放一起，一条数据展示；还比如一个合同可能相关跟进人很多，现在需要直接把这多个相关人都列出来在一个栏位里。
案例二
场景一（列转行）
数据如下：
region city
襄阳 老河口
襄阳 谷城
襄阳 南漳
襄阳 宜城
黄冈 黄州
黄冈 罗田
黄冈 蕲春要求将每个地区所辖的市县打在一行上，在一个栏位显示，即，目标数据如下：
region cities
襄阳 老河口,谷城,南漳,宜城
黄冈 黄州,罗田,蕲春查询语句：
select region,
concat_ws(',',collect_set(city)) cities
from region_city
group by region;查询结果：
取列表里面的值时还可以用另一个函数，collect_list，区别就在于collect_list允许重复值存在，而collect_set对重复值去重
场景二（行转列）
现有上述查询结果的数据，要求查询出案例二最开始的数据，这里我们也要用到letaral view explode
select region,
city
from split_to_city lateral view explode(split(cities, ',')) t_city as city;注：explode就是将hive一行中复杂的array或者map结构拆分成多行，lateral view与explode是极好的搭档，explode将复杂结构一行拆成多行，然后再用lateral view做各种聚合。
结果如下：
总结
行列转换是SQL中经常要用到的技巧，Hive sql也有其自身独特的实现方式，这也是面试中经常会遇到的。后续有时间再将几种数据库的行列转换做一个总结。
