0


Hive collect_set()、collect_list()列转行,并对转换后的行值排序

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 class
  union all
  select "jack" as st_name, '1' as class
  union all
  select "jack" as st_name, '2' as class
  union all
  select "jack" as st_name, '3' as class
  union all
  select "jack" as st_name, '5' as class
)tb_mid
group 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))
jack 5,3,3,2,1 1,2,3,3,5 1,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 class
  union all
  select "jack" as st_name, '1' as class
  union all
  select "jack" as st_name, '2' as class
  union all
  select "jack" as st_name, '3' as class
  union all
  select "jack" as st_name, '5' as class
)tb_mid
group 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))
jack 5,3,2,1 1,2,3,5 1,2,3,5
Time taken: 0.152 seconds, Fetched 1 row(s)


本文转载自: https://blog.csdn.net/helloxiaozhe/article/details/135938824
版权归原作者 Data_IT_Farmer 所有, 如有侵权,请联系我们删除。

“Hive collect_set()、collect_list()列转行,并对转换后的行值排序”的评论:

还没有评论