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)