目录

0 需求描述

1 需求分析

2 小 结

0 需求描述

有如下表格

partner	label	value
001	原始证件号	9111030275820228X7
001	统一社会信用代码	9111030275820228X7
002	原始证件号	6111030255820228Y7
002	统一社会信用代码	6111030255820228Y7

需要转换如下结果&＃xff1a;

partner	原始证件号	统一社会信用代码
001	9111030275820228X7	8111030275820228X7
002	6111030255820228Y7	5111030255820228Y7

1 需求分析

&＃xff08;1&＃xff09;创建数据

create table t as
select &＃39;001&＃39; as partner , &＃39;原始证件号&＃39; as label,&＃39;9111030275820228X7&＃39; as value
union all
select &＃39;001&＃39; as partner , &＃39;统一社会信用代码&＃39; as label,&＃39;8111030275820228X7&＃39; as value
union all
select &＃39;002&＃39; as partner , &＃39;原始证件号&＃39; as label,&＃39;6111030255820228Y7&＃39; as value
union all
select &＃39;002&＃39; as partner , &＃39;统一社会信用代码&＃39; as label,&＃39;5111030255820228Y7&＃39; as value

(2) 数据分析

方法1&＃xff1a;借助于聚合函数max()与case when实现。代码如下

select partner,max(case when label&＃61;&＃39;原始证件号&＃39; then value else null end) as a,max(case when label&＃61;&＃39;统一社会信用代码&＃39; then value else null end) as b
from t
group by partner

 利用max()函数可以有效的去除NULL值&＃xff0c;max()函数在计算的时候会忽略NULL值

方法2&＃xff1a;借助于str_to_map实现

str_to_map(字符串参数, 分隔符1, 分隔符2)

使用两个分隔符将文本拆分为键值对。

分隔符1将文本分成K-V对&＃xff0c;分隔符2分割每个K-V对。对于分隔符1默认分隔符是 &＃39;,&＃39;&＃xff0c;对于分隔符2默认分隔符是 &＃39;&＃61;&＃39;

测试如下&＃xff1a;

select str_to_map(&＃39;aaa:11&bbb:22&＃39;, &＃39;&&＃39;, &＃39;:&＃39;)&＃43;--------------------------&＃43;
| _c0 |
&＃43;--------------------------&＃43;
| {"bbb":"22","aaa":"11"} |
&＃43;--------------------------&＃43;
2 rows selected (2.226 seconds)

结果返回一个json对象。其中第一个参数用来分割整个串&＃xff0c;指名需要按照哪个符号来分割K-V对&＃xff0c;第二个参数用来指明K-V对中的分隔符。

从这测试案例也可以看出如果需要将数据拼接成一个json数组&＃xff0c;也可以使用str_to_map的形式。

针对本问题&＃xff1a;我们先做如下变换

第一步&＃xff1a;先将label 和value拼接成K-V对

select concat_ws(&＃39;:&＃39;,label,value)from t &＃43;------------------------------&＃43;
| _c0 |
&＃43;------------------------------&＃43;
| 统一社会信用代码:8111030275820228X7 |
| 原始证件号:9111030275820228X7 |
| 原始证件号:6111030255820228Y7 |
| 统一社会信用代码:5111030255820228Y7 |
&＃43;------------------------------&＃43;

第二步&＃xff1a; 利用collect_list()函数将多行转成单列

select concat_ws(&＃39;,&＃39;,collect_list(concat_ws(&＃39;:&＃39;,label,value))) from t group by partner&＃43;----------------------------------------------------&＃43;
| _c0 |
&＃43;----------------------------------------------------&＃43;
| 原始证件号:6111030255820228Y7,统一社会信用代码:5111030255820228Y7 |
| 统一社会信用代码:8111030275820228X7,原始证件号:9111030275820228X7 |
&＃43;----------------------------------------------------&＃43;

第三步&＃xff1a;利用str_to_map()函数组合成json形式

select str_to_map(concat_ws(&＃39;,&＃39;,collect_list(concat_ws(&＃39;:&＃39;,label,value))),&＃39;,&＃39;,&＃39;:&＃39; )from t group by partner&＃43;----------------------------------------------------&＃43;
| _c0 |
&＃43;----------------------------------------------------&＃43;
| {"原始证件号":"6111030255820228Y7","统一社会信用代码":"5111030255820228Y7"} |
| {"统一社会信用代码":"8111030275820228X7","原始证件号":"9111030275820228X7"} |
&＃43;----------------------------------------------------&＃43;

第四步&＃xff1a;map结构的取数方式进行行转列。具体SQL如下

select partner,mmap[&＃39;原始证件号&＃39;] as a ,mmap[&＃39;统一社会信用代码&＃39;] as b from (select partner,str_to_map(concat_ws(&＃39;,&＃39;,collect_list(concat_ws(&＃39;:&＃39;,label,value))),&＃39;,&＃39;,&＃39;:&＃39; ) as mmapfrom t group by partner) t

&＃43;----------&＃43;---------------------&＃43;---------------------&＃43;
| partner | a | b |
&＃43;----------&＃43;---------------------&＃43;---------------------&＃43;
| 002 | 6111030255820228Y7 | 5111030255820228Y7 |
| 001 | 9111030275820228X7 | 8111030275820228X7 |
&＃43;----------&＃43;---------------------&＃43;---------------------&＃43;2 rows selected (1.178 seconds)

方法1和方法2对比来看&＃xff0c;方法2采用str_to_map的方法更优雅&＃xff0c;效率更高一点&＃xff0c;这是因为hive底层在这种高级的数据结构上做了优化&＃xff0c;逻辑更优一点。

2 小 结

本文分析了一种采用str_to_map的方法进行行转列的方法&＃xff0c;该方法结构优雅&＃xff0c;从执行效率上来看性能更优&＃xff0c;比通用的聚合函数加case when的方法效率更高。通过本文也可以利用str_to_map来拼接json串&＃xff0c;不失为一种很好的技巧。