Hive笔记之collect_list/collect_set/concat_ws/concat 列转行函数详解

Hive笔记之collect_list/collect_set/concat_ws/concat函数（列转行）

create table t_visit_video (
    username string,
    video_name string
) partitioned by (day string)
row format delimited fields terminated by ',';

vi  /home/hadoop/t_visit_video.txt 

张三,大唐双龙传
李四,天下无贼
张三,神探狄仁杰
李四,霸王别姬
李四,霸王别姬
王五,机器人总动员
王五,放牛班的春天
王五,盗梦空间

load data local inpath '/home/hadoop/t_visit_video.txt' into table t_visit_video partition (day='20200508');

hive (felix)> select * from t_visit_video;
OK
张三    大唐双龙传      20200508
李四    天下无贼        20200508
张三    神探狄仁杰      20200508
李四    霸王别姬        20200508
李四    霸王别姬        20200508
王五    机器人总动员    20200508
王五    放牛班的春天    20200508
王五    盗梦空间        20200508
Time taken: 1.445 seconds, Fetched: 8 row(s)

按用户分组，取出每个用户每天看过的所有视频的名字：
hive (felix)> select username, collect_list(video_name) from t_visit_video group by username;
张三    ["大唐双龙传","神探狄仁杰"]
李四    ["天下无贼","霸王别姬","霸王别姬"]
王五    ["机器人总动员","放牛班的春天","盗梦空间"]
Time taken: 26.414 seconds, Fetched: 3 row(s)

但是上面的查询结果有点问题，因为霸王别姬实在太好看了，所以李四这家伙看了两遍，这直接就导致得到的观看过视频列表有重复的，所以应该增加去重，使用collect_set，其与collect_list的区别就是会去重：
hive (felix)> select username, collect_set(video_name) from t_visit_video group by username;
OK
张三    ["神探狄仁杰","大唐双龙传"]
李四    ["霸王别姬","天下无贼"]
王五    ["盗梦空间","放牛班的春天","机器人总动员"]
Time taken: 24.167 seconds, Fetched: 3 row(s)

李四的观看记录中霸王别姬只出现了一次，实现了去重效果。

--实现了linux的rownum功能

hive (felix)> select username,video_name,day,row_number() over ()  rn from t_visit_video; 
王五    盗梦空间        20200508        1
王五    放牛班的春天    20200508        2
王五    机器人总动员    20200508        3
李四    霸王别姬        20200508        4
李四    霸王别姬        20200508        5
张三    神探狄仁杰      20200508        6
李四    天下无贼        20200508        7
张三    大唐双龙传      20200508        8


突破group by限制
还可以利用collect来突破group by的限制，Hive中在group by查询的时候要求出现在select后面的列都必须是出现在group by后面的，即select列必须是作为分组依据的列，但是有的时候我们想根据A进行分组然后随便取出每个分组中的一个B，代入到这个实验中就是按照用户进行分组，然后随便拿出一个他看过的视频名称即可：

hive (felix)> select username, collect_list(video_name)[0] from t_visit_video group by username;
张三    神探狄仁杰
李四    霸王别姬
王五    盗梦空间
Time taken: 23.027 seconds, Fetched: 3 row(s)

video_name不是分组列，依然能够取出这列中的数据。


concat、concat_ws 函数
1）concat只是将各个字符进行拼接
2）concat_ws是将各个字符串 通过一个指定的拼接符进行拼接，相当于Oracle的wm_concat和listagg 

从数据库里取N个字段，然后组合到一起用“，”分割显示，起初想到用CONCAT()来处理，好是麻烦，没想到在手册里居然有提到CONCAT_WS()，非常好用。
它是一个特殊形式的 CONCAT()，第一个参数剩余参数间的分隔符，分隔符可以是与剩余参数一样的字符串，如果分隔符是 NULL，返回值也将为 NULL，这个函数会跳过分隔符参数后的任何 NULL 和空字符串，分隔符将被加到被连接的字符串之间。
hive (felix)> SELECT CONCAT_WS(",","First name","Second name","Last Name");
OK
First name,Second name,Last Name
Time taken: 0.055 seconds, Fetched: 1 row(s)

hive (felix)> SELECT CONCAT(",","First name","Second name","Last Name");
OK
,First nameSecond nameLast Name
Time taken: 0.044 seconds, Fetched: 1 row(s)

hive (felix)> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
OK
First name,Last Name
Time taken: 0.051 seconds, Fetched: 1 row(s)