hive sql系列（八）——把 1234 转成 “原因1 原因2 原因3 原因4”

每天分享一个sql，帮助大家找到sql的快乐——鸣谢网友老蕉供稿

需求

需求：根据聚合在一起的编码转换成聚合在一起的码值
一个字段是   “1,2,3,4”  
这个1234分别代表原因1  原因2 原因3  原因4  
现在要把 1234  办成  “原因1  原因2 原因3  原因4”

建表语句

create table wangyou1(
    codeStr string
)
row format delimited fields terminated by '\t'
;

实现

insert overwrite table wangyou1 values
("1,2,3,4"),
("1,2"),
("2,3"),
("2,3,4");

结果

select
    t2.codeStr,
    concat_ws(",",collect_list(t2.codeValue))
from
    (select
        t1.codeStr,
        case t1.codeId
            when "1" then "原因1"
            when "2" then "原因2"
            when "3" then "原因3"
            when "4" then "原因4"
         end codeValue
    from
        (select
            codeStr,
            codeId
        from
            wangyou1
        lateral view explode(split(codeStr,",")) tmp as codeId
        )t1
    )t2
group by t2.codeStr
;

分析

1、这里需要将字符串1变成字符串2，hive并没有直接提供类似索引的
2、思路是切割字符串使之变成数组、使用炸裂函数行转列、case when进行字符串转换、聚合函数实现列转行
3、如果字符串是固定的可以使用replace这种进行直接替换
4、这里的实现方式更适合通用型、不确定的，但是值比较固定

扩展

--今天的扩展部分是使用map替换case when
--替换后的效果速度上比实现部分减少了130毫秒，代码行数减少了6行
select
    str_to_map('{"1":"原因1","2":"原因2","3":"原因3","4":"原因4"}')

select
    map("1","原因1","2","原因2","3","原因3","4","原因4")

select
    t2.codeStr,
    concat_ws(",",collect_list(t2.codeValue))
from
    (select
        t1.codeStr,
        map("1","原因1","2","原因2","3","原因3","4","原因4")[codeId] as codeValue
    from
        (select
            codeStr,
            codeId
        from
            wangyou1
        lateral view explode(split(codeStr,",")) tmp as codeId
        )t1
    )t2
group by t2.codeStr
;
Total MapReduce CPU Time Spent: 4 seconds 50 msec
OK
t2.codestr	_c1
1,2	原因1,原因2
1,2,3,4	原因1,原因2,原因3,原因4
2,3	原因2,原因3
2,3,4	原因2,原因3,原因4
Time taken: 14.648 seconds, Fetched: 4 row(s)

知识点

1、split(字符串，分割符)：使用分割符切割字符串，返回一个数组
2、lateral view explode(数组):将数组字段拆分成多行
3、concat_ws(连接符,字符串，字符串)：连接多个字符串
4、collect_list(分组键)：将分组中的某列聚合成一个数组，数组中元素与分组后的数据保持一致
5、map：将多个排列好的k,v,k,v...变成一个map结构，这是初始化map结构的方式，取数据是map[key]