【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景

这篇具有很好参考价值的文章主要介绍了【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。


一、collect_set()/collect_list()

在 Hive 中想实现按某字段分组,对另外字段进行合并,可通过collect_list()或者collect_set()实现。

  • collect_set()函数与collect_list()函数:列转行专用函数,都是将分组中的某列转为一个数组返回。有时为了字段拼接效果,多和concat_ws()函数连用。

  • collect_set()与collect_list()的区别:

    • collect_list()函数 - - 不去重
    • collect_set()函数 - - 去重

有点类似于Python中的列表与集合。


二、实际运用

创建测试表及插入数据

drop table test_1;
create table test_1(
id string,
cur_day string,
rule string
) 
row format delimited fields terminated by ',';

insert into test_1 values
('a','20230809','501'),('a','20230811','502'),('a','20230812','503'),('a','20230812','501'),('a','20230813','512'),('b','20230809','511'),('b','20230811','512'),('b','20230812','513'),('b','20230812','511'),('b','20230813','512'),('b','20230809','511'),('c','20230811','512'),('c','20230812','513'),('c','20230812','511'),('c','20230813','512');

把同一分组的不同行的数据聚合成一个行

举例1:按照id,cur_day分组,取出每个id对应的所有rule(不去重)。

select id,cur_day,collect_list(rule) as rule_total  from test_1 group by id,cur_day order by id,cur_day;

【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景,hive,hive,hadoop,数据仓库
举例2:按照id,cur_day分组,取出每个id对应的所有rule(去重)。

select id,cur_day,collect_set(rule) as rule_total from test_1 group by id,cur_day order by id,cur_day;

【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景,hive,hive,hadoop,数据仓库

用下标可以随机取某一个

select id,cur_day,collect_list(rule)[0] as rule_one from test_1 group by id,cur_day order by id,cur_day;

select id,cur_day,collect_set(rule)[0] as rule_one from test_1 group by id,cur_day order by id,cur_day;

【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景,hive,hive,hadoop,数据仓库

聚合后的中的值用‘|’分隔开

select id,cur_day,concat_ws('|',collect_list(rule)) as rule_total from test_1 group by id,cur_day order by id,cur_day;

select id,cur_day,concat_ws('|',collect_set(rule)) as rule_totalfrom test_1 group by id,cur_day order by id,cur_day;

【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景,hive,hive,hadoop,数据仓库

使用collect_set()/collect_list()使得全局有序

现在需求:严格按照同一个id进行分组,规则按时间升序排序,使用collect_list()将时间与规则按升序排序且一 一 对应展示出来。

1.原数据详情:

【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景,hive,hive,hadoop,数据仓库
2.要求输出结果如下:按id分组,将rule按cur_day升序排序,将cur_day,rule放在一个列表中,并且列表中cur_day与rule是按升序一一对应的关系。
【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景,hive,hive,hadoop,数据仓库

3.实现思路:将其使用row_number()over(partition by id order by cur_day as)排序,然后再使用collect_list()或者collect_list()/collect_set()进行聚合就可以了。

drop table test_2 ;
create table test_2 as 
select id,collect_list(cur_day),collect_list(rule) 
from (
select t.id,t.cur_day,t.rule,row_number() over(partition by id order by cur_day asc) rn from test_1 t
)t group by id ;

select * from test_2 group by id order by id;

【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景,hive,hive,hadoop,数据仓库

4.发现问题:cur_day数组内的时间并没有按照升序排序输出。

5.原因分析:

  • HiveQL执行时,大部分情况都会转换为MR来执行,当开户多个Mapper的时候,Mapper1可能处理的是id为a,cur_day排名为1、2、3的数据,Mapper2可能处理的id为a,cur_day排名为4、5、6的数据。
  • collect_list()的底层是ArrayList来实现的,当put到ArrayList的时候,不一定是哪个Mapper先,哪个Mapper后,所以会出现20230811、20230812、20230813在20230809前面的情况。所以,row_number() over(partitiion by order by) 与collect_list一起使用只能实现局部有序,不能实现全局有序。

6.解决方案:

  • 方法一:全局 order by
drop table test_2 ;
create table test_2 as 
select id,collect_list(cur_day),collect_list(rule) 
from (
	select t.* from(
		select t.id,t.cur_day,t.rule,row_number() over(partition by id order by cur_day asc) rn from test_1 t
	) t order by rn 
)t group by id ;

select * from test_2 group by id order by id;

【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景,hive,hive,hadoop,数据仓库

  • 方法二:distribute by + order by
select
id,collect_list(cur_day),collect_list(rule) 
from(
	select
	t.id,t.cur_day,t.rule
	,row_number()over(partition by id order by cur_day asc) as rn
	from(
		select
		t.id,t.cur_day,t.rule
		from test_1 t
		distribute by id sort by cur_day asc
	)t
)t 
group by id order by id;

【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景,hive,hive,hadoop,数据仓库

  • 方法三:sort_array (只支持升序)
select
id,concat_ws(',',collect_list(cur_day)),regexp_replace(concat_ws(',',sort_array(collect_list(concat_ws('|' ,lpad(cast(rn as string),2,'0') ,rule)))),'\\d+\\|','') 
from(
select t.* 
from(
select
id,cur_day,rule,
row_number()over(partition by id order by cur_day asc) as rn
from test_1
)t order by rn
)t group by id order by id;

【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景,hive,hive,hadoop,数据仓库

上面代码用到相关函数解析:

  • lpad(str,len,pad) 函数:这个是对排序值(也就是rule)来补位的,当要排序的值过大时,因为sort_array是按顺序对字符进行排序(即11会在2的前面),所以可以使用此函数补位(即将1,2,3,4变成01,02,03,04),这样就能正常排序了。

    • 第一个参数:你要补齐的字段值
    • 第二个参数:补齐之后总共的位数
    • 第三个参数:你要在左边填充的字符
  • regexp_replace(strA,strB,strC) 函数:将字符串A中的符合JAVA正则表达式B的部分替换为C,即排序之前将序号使用,跟需要的字段拼接,而排序之后,需要将序号和:去掉

  • sort_array(expr[, ascendingOrder])默认是升序排序,但其中可以带参数,默认为True,即按升序,如果输入False,就会按降序排序。

    • expr:一个可排序元素的 ARRAY 表达式。
    • ascendingOrder:可选的 BOOLEAN 表达式,默认值为 True,即按升序。
select id
,concat_ws(',',sort_array(collect_list(concat_ws('|' ,lpad(cast(rn as string),2,'0') ,rule)))) as middle_value --中间值
,regexp_replace(concat_ws(',',sort_array(collect_list(concat_ws('|' ,lpad(cast(rn as string),2,'0') ,rule)))),'\\d+\\|','')  as result_values --最终结果
from(
select t.* 
from(
select
id,cur_day,rule,
row_number()over(partition by id order by cur_day asc) as rn
from test_1
)t order by rn
)t group by id order by id;

【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景,hive,hive,hadoop,数据仓库文章来源地址https://www.toymoban.com/news/detail-708261.html

到了这里,关于【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Spark SQL自定义collect_list分组排序

    想要在spark sql中对group by + concat_ws()的字段进行排序,可以参考如下方法。 原始数据如下: 目标数据如下: spark-shell: 1.使用开窗函数 因为使用开窗函数本身会使用比较多的资源, 这种方式在大数据量下性能会比较慢,所以尝试下面的操作。 2.使用struct和sort_array(array,asc?tru

    2024年02月01日
    浏览(39)
  • 第12章_集合框架(Collection接口,Iterator接口,List,Set,Map,Collections工具类)

    1.1 生活中的容器 1.2 数组的特点与弊端 一方面,面向对象语言对事物的体现都是以对象的形式,为了方便对多个对象的操作,就要对对象进行存储。 另一方面,使用数组存储对象方面具有 一些弊端 ,而Java 集合就像一种容器,可以 动态地 把多个对象的引用放入容器中。 数

    2024年01月25日
    浏览(45)
  • Java基础六 - Collection集合List、Set、Queue,Map

    1. List - ArrayList、LinkedList、Vector ArrayList         2. LinkedList         3. Vector         4. 常见使用方法 2. Set - HashSet、LinkedHashSet、TreeSet 1. HashSet 2. LinkedHashSet 3. TreeSet 4. 常用方法 3. Map - HashMap、TreeMap、LinkedHashMap、Hashtable 1. HashMap 2. LinkedHashMap 3. TreeMap 4. Hashtable 5.

    2024年02月14日
    浏览(49)
  • Java02-迭代器,数据结构,List,Set ,Map,Collections工具类

    目录 什么是遍历? 一、Collection集合的遍历方式 1.迭代器遍历 方法 流程 案例 2. foreach(增强for循环)遍历 案例 3.Lamdba表达式遍历 案例 二、数据结构 数据结构介绍 常见数据结构 栈(Stack) 队列(Queue) 链表(Link) 散列表(Hash Table) 树(Tree) List接口 ArraysList集合 Linked

    2024年02月14日
    浏览(52)
  • Java02-迭代器,数据结构,List,Set ,TreeSet集合,Collections工具类

    目录 什么是遍历? 一、Collection集合的遍历方式 1.迭代器遍历 方法 流程 案例 2. foreach(增强for循环)遍历 案例 3.Lamdba表达式遍历 案例 二、数据结构 数据结构介绍 常见数据结构 栈(Stack) 队列(Queue) 链表(Link) 散列表(Hash Table) 树(Tree) List接口 ArraysList集合 Linked

    2024年02月14日
    浏览(47)
  • 【Java 集合框架API接口】Collection,List,Set,Map,Queue,Deque

    博主: _LJaXi Or 東方幻想郷 专栏: Java | 从跨行业到跨平台 开发工具: IntelliJ IDEA 2021.1.3 Java集合API提供了一组功能强大的数据结构和算法, 具有以下作用( 简述 ) 存储和组织数据 提供高效的数据访问和操作 实现算法和数据处理 提供线程安全性 支持泛型编程 java.util.Collection

    2024年02月12日
    浏览(48)
  • MySQL函数:列转行CONCAT、CONCAT_WS、GROUP_CONCAT的使用(精要)

    很久没有接触Mysql了。 今天心血来潮,突然想了解一下Mysql列转行,看了一些文章,重点不清晰,遂有下文! Mysql官网、 社区版下载( Windows版_mysql.8.0.31下载  ) Mysql内部提供了列转行的三个函数,分别是: CONCAT( str1,str2,... ) CONCAT_WS( separator,str1,str2,... ) GROUP_CONCAT( expr ) 数

    2024年02月05日
    浏览(41)
  • 【Java基础教程】(四十八)集合体系篇 · 上:全面解析 Collection、List、Set常用子接口及集合元素迭代遍历方式~【文末送书】

    掌握 Java 设置类集的主要目的以及核心接口的使用; 掌握 Collection 接口的作用及主要操作方法; 掌握 Collection 子接口 List、Set 的区别及常用子类的使用; 掌握 Map 接口的定义及使用; 掌握集合的4种输出操作语法结构; 掌握 Properties类的使用 ; 了解类集工具类 Collections 的作

    2024年02月15日
    浏览(57)
  • Hive中的常用concat函数——concat函数、concat_ws函数和group_concat函数

    连接参数的函数,返回结果为连接参数的字符串。如果有一个参数为 NULL ,则返回的结果为 NULL 。 concat() 的一个特殊形式,表示 concat with separator ,两个参数之间加上特定的分隔符。返回的是用指定分隔符连接参数的字符串。如果分割符为 null ,则返回 null ,参数为 null ,则

    2024年02月02日
    浏览(44)
  • Hive的行列转换(行转多列、多列转行、行转单列、单列转行)

    在实际使用Hive的过程中,常常会涉及到行列转换,细分的话,有下面4种类型的行列转换,分别是: 行转多列 多列转行 行转单列 单列转行 下面我们通过样例介绍每种行列转换的实现方法。 样例表 班级成绩表: 姓名(name) 学科(subject) 成绩(score) 行列转换思路分析及实现 行转

    2024年02月15日
    浏览(79)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包