Hivesql 行转列,列转行详解

hive中行转列

行转列：concat_ws 和collect_set或者collect_list结合使用

collect_set函数是对统计的数据进行去重，然后拼接在一起，返回一个set集合

collect_list是不去重，对所有的值进行拼接然后返回一个list集合

select id,isbook,concat_ws(',', collect_list(cast(time as string))) as List from table group by id,isbook

 

使用 str_to_map(text,delimiter1,delimiter2) 行转列

          将字符串str按照指定分隔符转换成Map,

第一个参数是需要转换字符串,

第二个参数是键值对之间的分隔符,默认为  , ;

第三个参数是键值之间的分隔符， 默认为"="

取用map里的字段,用str_to_map('key:v1;key2:v2',';',':')[key]即可

 

行转列分解

select name,collect_list(concat_ws(':',subject,cast(sorce as string))) as info from test group by name;

将数据转换为字符串

select name ,concat_ws(',',collect_list(concat_ws(':',subject,cast(sorce as string))) ) as info from test group by name

转为map

select name,str_to_map(concat_ws(',',collect_list(concat_ws(':',subject,cast(sorce as string))))) as info from test group by name

行转列

select name

,info['语文'] as Chinese

,info['数学'] as Math

,info['英语'] as English

from

(select name,str_to_map(concat_ws(',',collect_list(concat_ws(':',subject,cast(sorce as string))))) as info from test group by name

) a

 

使用case when  行转列

select

name,

max(case when subject='语文' then score end) as Chinese,

max(case when subject='数学' then score end) as Math,

max(case when subject='英语' then score end) as English

from student_hang group by name;

 

 

 

 

 

 

列转行：

高效函数:

explode就是将hive一行中复杂的array或者map结构拆分成多行

 

LATERAL VIEW explode(split(字段,’,’))是将一个字段拆分展示为多条数据显示,

CONCAT_WS语句,将多个字段拼接起来,两者结合可以满足将一条数据多个特定字段展示为多条数据一个特定字段。

LATERAL VIEW explode(split(CONCAT_WS(’,’,string1,string2,string2)),’,’)) mytable(视图名) AS obj_view(新列名)

实例：

select DEPT_NAME,

       SUM(obj_view)/COUNT(*) as SCORE

  from XXXX表

   LATERAL VIEW explode(split(CONCAT_WS(',',RCOUNT,QCOUNT,SCORE),',')) mytable AS obj_view

   GROUP BY DEPT_NAME

 

使用case  when 列转行

select name,'chinese' subject,chinese as score from student_column

union all

select name,'Math' subject,Math as score from student_column

union all

select name,'English' subject,English as score from student_column;