从数据库里取N个字段,然后组合到一起用“,”分割显示,起初想到用CONCAT()来处理,好是麻烦,没想到在手册里居然有提到CONCAT_WS(),非常好用。
CONCAT_WS(separator, str1, str2,...)
它是一个特殊形式的 CONCAT(),第一个参数剩余参数间的分隔符,分隔符可以是与剩余参数一样的字符串,如果分隔符是 NULL,返回值也将为 NULL,这个函数会跳过分隔符参数后的任何 NULL 和空字符串,分隔符将被加到被连接的字符串之间。
简单例子如下:
mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name"); -> 'First name,Second name,Last Name' mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name"); -> 'First name,Last Name'
Hive中collect相关的函数有collect_list和collect_set。
它们都是将分组中的某列转为一个数组返回,不同的是collect_list不去重而collect_set去重。
做简单的实验加深理解,创建一张实验用表,存放用户每天点播视频的记录:
create table t_visit_video ( username string, video_name string ) partitioned by (day string) row format delimited fields terminated by ',';
在本地文件系统创建测试数据文件:
张三,大唐双龙传 李四,天下无贼 张三,神探狄仁杰 李四,霸王别姬 李四,霸王别姬 王五,机器人总动员 王五,放牛班的春天 王五,盗梦空间
将数据加载到Hive表:
load data local inpath '/root/hive/visit.data' into table t_visit_video partition (day='20180516');
按用户分组,取出每个用户每天看过的所有视频的名字:
select username, collect_list(video_name) from t_visit_video group by username ;
张三 ["大唐双龙传","神探狄仁杰"] 李四 ["天下无贼","霸王别姬","霸王别姬"] …………
但是上面的查询结果有点问题,因为霸王别姬实在太好看了,所以李四这家伙看了两遍,这直接就导致得到的观看过视频列表有重复的,所以应该增加去重,使用collect_set,其与collect_list的区别就是会去重:
select username, collect_set(video_name) from t_visit_video group by username;
张三 ["大唐双龙传","神探狄仁杰"] 李四 ["天下无贼","霸王别姬"] …………
李四的观看记录中霸王别姬只出现了一次,实现了去重效果。
突破group by限制
还可以利用collect来突破group by的限制,Hive中在group by查询的时候要求出现在select后面的列都必须是出现在group by后面的,即select列必须是作为分组依据的列,但是有的时候我们想根据A进行分组然后随便取出每个分组中的一个B,代入到这个实验中就是按照用户进行分组,然后随便拿出一个他看过的视频名称即可:
select username, collect_list(video_name)[0] from t_visit_video group by username;
张三 大唐双龙传 李四 天下无贼 …………
video_name不是分组列,依然能够取出这列中的数据。
摘录汇总于:
https://www.cnblogs.com/locoy/archive/2006/10/28/542751.html
https://www.cnblogs.com/cc11001100/p/9043946.html