Hive sql 行列转换(行转列 列转行)

在Hive sql应用中会遇到“行转列”和“列转行”的场景，下面介绍其基本使用语法。

1.行转列：

关键字：collect_set() / collect_list()、concat_ws()

1）collect_set()/collect_list()：

collect_set( )函数只接受基本数据类型，作用是对参数字段进行去重汇总，返回array类型字段;

collect_list()函数和collect_set( )作用一样，只是前者不去重,后者去重。

2）concat_ws()：

concat_ws (separator,字符串A/字段名A,字符串B/字段名B…)是concat的特殊形式，第一个参数是分隔符，分隔符会放到要连接的字符串之间,分隔符可以是字符串，也可以是其他参数。如果分隔符为NULL,则结果为NULL,函数会忽略任何分隔符参数后的 NULL值。但是concat_ws( )不会忽略任何空字符串。（然而会忽略所有的 NULL)，如果参数为string类型数组则合并数组内字符串。

例:concat_ws( ‘,’, [ ‘a ‘, ‘b’])–> ‘a,b’

行转列基本语法：

select 分类字段,concat_ws(’,’,collect_set(合并字段)) as 别名 from table_name group 分类字段;

/* 原始数据name gendertimes张三 男 唐李四 男 唐王五 男 明赵六 男 明*/-- 先用collect_set将列拼接在一起，然后再通过concat_ws进行展开拼接SELECT a.gender_times,concat_ws(';',collect_set(a.name)) nameFROM(SELECT name,concat(gender, '_','times') gender_timesFROM hero_info) tGROUP BY t.gender_times;/*查询结果gender_times name 男_唐张三;李四男_明王五;赵六

2.列转行

关键字：explode()、split()和LATERAL VIEW

1) split()****:

将一个字符串按照指定字符分割，结果为一个array；

2）explode():

将一列复杂的array或者map拆分为多行,它的参数必须为map或array；

3）lateral view:

lateral view udtf(字段名)表别名/表临时名as列别名/列临时名。lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateralview再把结果组合，产生一个支持别名表的虚拟表。

列转行基本语法：

select字段,字段别名from table_name lateral view explode（split(拆分字段,分隔符)）表别名 as 字段别名;

/*原始数据province city河南 郑州市,开封市，洛阳市河北 石家庄市，保定市湖南 长沙市，岳阳市，常德市*/-- addr为表名SELECT province,city_nFROM addr LATERAL VIEW explode (split(city,',')) addr_tmp AS city_n;/*-- 查询结果河南郑州市河南开封市河南洛阳市河北石家庄市河北保定市湖南长沙市湖南岳阳市湖南常德市*/

先自我介绍一下，小编上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦