Hive(行转列 列转行)
行转列
行专列常用的几种方式有
- collect_list
- collect_set
举例说明
原数据
sql
SELECT concat_ws("|",collect_set(name)),concat(xz,",",xx)
FROM db2.xyj
GROUP BY concat(xz,",",xx)
result
顺带说一下concat和concat_ws的区别:
concat的连接符是按位置写的比如concat(‘,’,xx,xz)那么结果就为 ,xxxz形式,而concat_ws(‘,’,xx,xz)是xx,xz形势,而且后面如果是数组两两元素之间都有间隔符
列转行
explode
常与split连用
语法格式
lateral view explode(array) tmp(临时表名) as colname(列名)
举例分析
表数据
sql
SELECT name,typename from movie
LATERAL VIEW explode(split(mtype,",")) t as typename
result