Hive（四）-- 行转列、列转行、窗口函数、编号函数

行转列

使用聚合函数将行元素聚合为一个集合，再用concat、concat_ws进行拼接

• collect_list()
• collect_set()
• concat
• concat_ws

案例：

孙悟空	白羊座	A
娜娜	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A

create table tb_row_col(
name string ,
star string ,
xx string
)
row format delimited fields terminated by '\t';
load data local inpath '/home/yaoz/data/10_16/star.log' into table tb_row_col;

select * from tb_row_col;
+------------------+------------------+----------------+
| tb_row_col.name  | tb_row_col.star  | tb_row_col.xx  |
+------------------+------------------+----------------+
| 孙悟空              | 白羊座              | A              |
| 娜娜               | 射手座              | A              |
| 宋宋               | 白羊座              | B              |
| 猪八戒              | 白羊座              | A              |
| 凤姐               | 射手座              | A              |
+------------------+------------------+----------------+


射手座,A            娜娜|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋

select 
star,xx,
collect_list(name)
from 
tb_row_col
group by 
star,xx;
+-------+-----+----------------+
| star  | xx  |      _c2       |
+-------+-----+----------------+
| 射手座   | A   | ["娜娜","凤姐"]    |
| 白羊座   | A   | ["孙悟空","猪八戒"]  |
| 白羊座   | B   | ["宋宋"]         |
+-------+-----+----------------+

select 
star,xx,
concat_ws('|',collect_list(name))
from 
tb_row_col
group by 
star,xx;
+-------+-----+----------+
| star  | xx  |   _c2    |
+-------+-----+----------+
| 射手座   | A   | 娜娜|凤姐    |
| 白羊座   | A   | 孙悟空|猪八戒  |
| 白羊座   | B   | 宋宋       |
+-------+-----+----------+


select 
concat_ws(',',star,xx),
concat_ws('|',collect_list(name))
from 
tb_row_col
group by 
star,xx;
+--------+----------+
|  _c0   |   _c1    |
+--------+----------+
| 射手座,A  | 娜娜|凤姐    |
| 白羊座,A  | 孙悟空|猪八戒  |
| 白羊座,B  | 宋宋       |
+--------+----------+

 

hive本地执行设置显示标头：

set hive.cli.print.header=true;

列转行

• split(col_name,',')
• explode()
• lateral view 

案例：

movie	category
《八佰》	战争,动作,抗日,剧情
《我和我的家乡》	喜剧,剧情,动作,心理
《战狼2》	战争,动作,灾难
《夺冠》	剧情,动作,国产,爱国

create table movie(
name string ,
category array<string>
)
row format delimited fields terminated by '\t'
collection items terminated by ',';
load data local inpath '/home/yaoz/data/10_16/movie.log' into table movie;

select * from movie;
+-------------+------------------------+
| movie.name  |     movie.category     |
+-------------+------------------------+
| 《八佰》        | ["战争","动作","抗日","剧情"]  |
| 《我和我的家乡》    | ["喜剧","剧情","动作","心理"]  |
| 《战狼2》       | ["战争","动作","灾难"]       |
| 《夺冠》        | ["剧情","动作","国产","爱国"]  |
+-------------+------------------------+

select
nama,
explode(category)
from 
movie;
报错！！！

select
explode(category)
from 
movie;
+------+
| col  |
+------+
| 战争   |
| 动作   |
| 抗日   |
| 剧情   |
| 喜剧   |
| 剧情   |
| 动作   |
| 心理   |
| 战争   |
| 动作   |
| 灾难   |
| 剧情   |
| 动作   |
| 国产   |
| 爱国   |
+------+

select
name,
cate
from 
movie lateral view explode(category) tmp as cate;
+-----------+-------+
|   name    | cate  |
+-----------+-------+
| 《八佰》      | 战争    |
| 《八佰》      | 动作    |
| 《八佰》      | 抗日    |
| 《八佰》      | 剧情    |
| 《我和我的家乡》  | 喜剧    |
| 《我和我的家乡》  | 剧情    |
| 《我和我的家乡》  | 动作    |
| 《我和我的家乡》  | 心理    |
| 《战狼2》     | 战争    |
| 《战狼2》     | 动作    |
| 《战狼2》     | 灾难    |
| 《夺冠》      | 剧情    |
| 《夺冠》      |