Hive collect_set()、collect_list()列转行，和concat_ws()使用，并对转换后的行值排序

1、需求描述

对列值分组，并按一定顺序排序，最后多行合并一行，合并值左到右逆序排列。

2、考点：

  • sort_array(e: column, asc: boolean)将array中元素排序(自然排序)，默认asc为true，即默认排升序
  • collect_set() 和 collect_list()的区别是前者去重，后者不去重

3.1、直接上collect_list()代码实现：

select st_name,concat_ws(",",sort_array(collect_list(class),false)) ,concat_ws(",",sort_array(collect_list(class),true)),concat_ws(",",sort_array(collect_list(class))) from(select "jack" as st_name, '3' as classunion allselect "jack" as st_name, '1' as classunion allselect "jack" as st_name, '2' as classunion allselect "jack" as st_name, '3' as classunion allselect "jack" as st_name, '5' as class)tb_midgroup by st_name;

结果如下：

st_name concat_ws(,, sort_array(collect_list(class), false))concat_ws(,, sort_array(collect_list(class), true)) concat_ws(,, sort_array(collect_list(class), true))
jack5,3,3,2,11,2,3,3,51,2,3,3,5
Time taken: 0.16 seconds, Fetched 1 row(s)

3.2、直接上collect_set()代码实现：

select st_name,concat_ws(",",sort_array(collect_set(class),false)) ,concat_ws(",",sort_array(collect_set(class),true)),concat_ws(",",sort_array(collect_set(class))) from(select "jack" as st_name, '3' as classunion allselect "jack" as st_name, '1' as classunion allselect "jack" as st_name, '2' as classunion allselect "jack" as st_name, '3' as classunion allselect "jack" as st_name, '5' as class)tb_midgroup by st_name;

结果如下：

st_name concat_ws(,, sort_array(collect_set(class), false)) concat_ws(,, sort_array(collect_set(class), true)) concat_ws(,, sort_array(collect_set(class), true))
jack5,3,2,1 1,2,3,5 1,2,3,5
Time taken: 0.152 seconds, Fetched 1 row(s)