Hive的行列转换(行转多列、多列转行、行转单列、单列转行)

这篇具有很好参考价值的文章主要介绍了Hive的行列转换(行转多列、多列转行、行转单列、单列转行)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

在实际使用Hive的过程中,常常会涉及到行列转换,细分的话,有下面4种类型的行列转换,分别是:

  1. 行转多列
  2. 多列转行
  3. 行转单列
  4. 单列转行

下面我们通过样例介绍每种行列转换的实现方法。

样例表
班级成绩表:

姓名(name) 学科(subject) 成绩(score)

A	语文	70
A	数学	90
A	英语	80
B	数学	95
B	英语	85
B	语文	75

行列转换思路分析及实现
行转多列
如果需要将上面的样例表转换为
“姓名 | 语文成绩 | 数学成绩 | 英语成绩”
这样的格式,那么这就需要用到行转多列。

思路:
涉及到行转成列,肯定是会按照某一列或者某几列的值进行分组来压缩行数,所以会用到group by。
分组之后需要用到聚合函数,由于多列中的每列只关心自己对应的数据,所以要使用case语句进行选择,至于聚合函数,只要数据能保证唯一性,max、min、avg(数值类型)等都可以

样例SQL:

select name,
max(case when subject = '数学' then score else null end) math,
max(case when subject='英语' then score else null end) english,
max(case when subject='语文' then score else null end) chinese
from t1
group by name;

多列转行
将上面行转多列的结果再转回成原始表结构的过程,就是多列转行

思路
列转行,会涉及到行数的增加,所以会用到UDTF,而UDTF只是针对某一列的,要把这列扩展后生成的多行数据和源表中的各列拼接在一起,需要用到lateral view语法;
需要将多列里各列的列名(业务含义),在新数据中当做一个标识列,而与lateral view联合使用的explode函数是支持Map类型的,所以要先将原表里的多列变换成Map类型的一列,然后再用lateral view拆开。

样例SQL:

select name,subject,score from
(
	select name,map('数学',math,'英语',english,'语文',chinese) scores
	from t2
) tt1 
lateral view explode(scores) tt2 as subject,score;

行转单列
将原始表转换为
“姓名 | 所有科目成绩集合 ”
则涉及到行转单列

思路
和行转多列一样,行数会减少,所以需要用到group by,然后转成的是单列,所以需要用到collect_list或者collect_set聚合函数,如果字段类型想要是有分隔符隔开的字符串,再套上一层concat_ws;
上面的方案得出的array或者字符串是乱序的,如果想要进行排序,可以使用sort_array函数。但sort_array只能按字段类型的升序排列(数值字段自然序,字符串字段字典序);
如果想要自己指定排序规则,或者排序的不是单列里的这个字段(比如班级考试成绩表,所有分数字段里需要根据学科进行排序,而不是分数高低),则需要使用collect_list加上over子句来实现。

样例SQL:
1、成绩不排序

select name,concat_ws(',',collect_list(score))
from t1
group by name;

2、按分数高低排序

select name,concat_ws(',',sort_array(collect_list(score)))
from t1
group by name;

3、按学科进行排序

select name,concat_ws(',',max(a)) from 
(
	select name,
		collect_list(score) over(partition by name order by subject 
			rows between unbounded preceding and unbounded following) a 
	from t1
) tt1 group by name;

4、按指定的学科顺序排序

select name,concat_ws(',',max(a)) scores from 
(
	select name,
		collect_list(score) over(partition by name 
			order by case when subject='语文' then 1 
						  when subject='数学' then 2 
						  when subject='英语' then 3 end 
			rows between unbounded preceding and unbounded following) a 
	from t1
) tt1 group by name;

单列转行
将上面的结果转成原始表结构,就是单列转行

思路
和多列转行一样,使用lateral view加explode来转换。但这种方式转换出来会丢失掉科目字段信息;
如果需要加上科目信息,则需要按照单列里面的顺序的业务含义,先将单列转成Map类型,将科目加到数据里,然后再使用lateral view转换。

样例SQL:
1、无科目字段

select name,score from
(
	select name,split(scores,',') s from t3
) tt1 
lateral view explode(s) tt2 as score;

2、有科目字段文章来源地址https://www.toymoban.com/news/detail-558309.html

select name,subject,score from
(
	select name,map('语文',s[0],'数学',s[1],'英语',s[2]) ss from 
	(
		select name,split(scores,',') s from t3
	) tt1
) ttt1
lateral view explode(ss) ttt2 as subject,score;

到了这里,关于Hive的行列转换(行转多列、多列转行、行转单列、单列转行)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

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

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

相关文章

  • Hive多行转多列,多列转多行

    原始数据表 目标结果表 分析:目标表中的a和b是用分组形成,所以groupby字段选用原始表中col1,c、d、e是原始表中的行值,在目标表中成了列名,目标表中可以采用as做列名自定义,当原始表中的某一字段值等于某一特定值时,人为定义为结果表中的列名。 SQL实现 总结:多行

    2024年02月12日
    浏览(48)
  • Hive数据仓库行转列

    查了很多资料发现网上很多文章都是转发和抄袭,有些问题。这里分享一个自己项目中使用的行转列例子,供大家参考。代码如下: 如果是在SparkSQL或Presto平台,或者阿里云的MaxCompute平台,还可使用如下方式:

    2024年02月21日
    浏览(58)
  • 【Hive-SQL】Hive Select 选择语句排除一列或多列

    查看 除了sample_date 以外的所有字段信息 查看 除了sample_date 和 msgtype 以外的所有字段信息 上面的 set hive.support.quoted.identifiers=none; 可以替换操作: 在 \\\'hive-site.xml\\\' 中添加以下配置: hive.support.quoted.identifiers=none

    2024年02月10日
    浏览(42)
  • 【hive】行转列—explode()/posexplode()/lateral view 函数使用场景

    功能: 用于和UDTF函数(explode,split)结合使用,把某一行数据拆分成多行数据,再将多行结果组合成一个支持别名的虚拟表。 主要解决select使用UDTF做查询的过程中查询只能包含单个UDTF,不能包含其它字段以及多个UDTF的情况。 语法:LATERAL VIEW udtf(expression) tableAlias AS columnAlia

    2024年04月09日
    浏览(41)
  • HIVE SQL通过Lateral View + explode实现列转行

    原表: a b Andy 碟中谍,谍影重重,007 MOMO 小鞋子,朋友啊你的家在哪里 David ‘’ Lily NULL 实现效果 a b Andy 碟中谍 Andy 谍影重重 Andy 007 MOMO 小鞋子 MOMO 朋友啊你的家在哪里 David ‘’ 实现代码: 注: explode函数:处理map结构的字段,将数组转换成多行,所以此处使用了split函数将

    2024年02月12日
    浏览(37)
  • 【SQL相关】Hive行列字符串的合并与拆分

    目录 一、行方向 1. 行方向的合并 1.1 concat 函数 1.2 concat_ws 函数 2. 行方向的拆分 二、列方向 1. 列方向的合并 1.1 group_concat 函数 1.2 collect_list 函数 1.3 collect_set 函数 2. 列方向的拆分 2.1 explode 函数 2.2 lateral view 1. 行方向的合并 将同一行某几列的数据以分隔符分隔,合并到同一列

    2024年04月14日
    浏览(50)
  • Hive字符串数组json类型取某字段再列转行

    acct content 1232313 [{\\\"name\\\":\\\"张三\\\",\\\"code\\\":\\\"上海浦东新区89492jfkdajrn福建的卡\\\"...},{\\\"name\\\":\\\"狂徒\\\",\\\"code\\\":\\\"select * from table where aa=1rn and a=12\\\"...},{...}] ... ...  上述数据表名code_content,把json中code内容全都取出来拼接成一行数据,最终效果: acct new_content 1232313 上海浦东新区89492jfkdajrn福建的

    2024年02月11日
    浏览(49)
  • Hive SQL 中ARRAY或MAP类型数据处理:lateral view explode()/posexplode()——行转列函数

    前言:在对表数据进行批量处理过程中,常常碰上某个字段是一个array或者map形式的字段,一列数据的该字段信息同时存在多个值,当我们需要取出该数组中的每一个值实现一一对应关系的时候,可以考虑使用lateral view explode()/posexplode() 进行处理。 一、提要:explode()本身是

    2024年02月04日
    浏览(48)
  • 把多列的迭代次数问题化简为单列问题

    前已有实验表明,当训练集只有一列的时候,收敛迭代次数与训练集分布的标准差成反比。分布越均匀迭代次数越大。如果可以把多列问题化简为单列问题,比较迭代次数的大小顺序就会变得很简单。 ( A, B )---3*30*2---( 1, 0 )( 0, 1 ) 做一个网络来分类A和B,网络输入只有3个节点

    2023年04月11日
    浏览(29)
  • Oracle——行转列与列转行

    把某一个字段的值作为唯一值,然后另外一个字段的 行值转换成它的列值 。 转换过程大致如下所示: 通常情况下,采取 group by 唯一字段 进行分组,然后依据不同的列进行判断输出就能转换。 建表语句和增加测试数据sql如下所示: 执行完毕后,数据库中当前的数据结构如

    2024年02月15日
    浏览(63)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包