hive学习(三):练习题——collect_set及array_contain(学生选课情况

hive及数仓 SenLin 4年前 (2020-03-25) 1193次浏览 已收录 0个评论

前言:hive,sql collect_set及array_contain,大数据

sql为基础,利用题目进行hive的语句练习,逐步体会sql与hive的不同之处

题目用到hive的集合函数,使用了collect_set、array_contain函数,额外讲解concat_ws的使用,文末有具体解释。

本次练习题来源:https://www.cnblogs.com/qingyunzong/p/8747656.html

题目:

(1)数据说明

存在一份数据

id course 
1,a 
1,b
1,c 
1,e 
2,a 
2,c 
2,d 
2,f 
3,a 
3,b 
3,c 
3,e

该数据含义为:表示有id为1,2,3的学生选修了课程a,b,c,d,e,f中其中几门。

(2)需求

编写Hive的HQL语句来实现以下结果:表中的1表示选修,表中的0表示未选修

id    a    b    c    d    e    f
1     1    1    1    0    1    0
2     1    0    1    1    0    1
3     1    1    1    0    1    0

解答步骤:

(1)创建表t_course
create table t_course(id int,course string)
row format delimited fields terminated by ",";
(2)导入数据
load data local inpath "/home/{文件所在位置}/course.txt" into table t_course;

注:以linux系统为例,可以通过 rz -be 上传文件course.txt

(3)解题过程
第一步:找出选课集合
select collect_set(course) as courses from t_course;
hive学习(三):练习题——collect_set及array_contain(学生选课情况
image.png
第二步:找出每名学生的选课集合
hive> select id as id,collect_set(course) as course from t_course group by id;
hive学习(三):练习题——collect_set及array_contain(学生选课情况
image.png
第三步:将前面两个步骤连接
create table id_courses as select t1.id as id,t1.course as id_courses,t2.course courses 
from 
( select id as id,collect_set(course) as course from t_course group by id ) t1 
join 
(select collect_set(course) as course from t_course) t2;

查看id_courses

select * from id_courses;
hive学习(三):练习题——collect_set及array_contain(学生选课情况
image.png
第四步:得出结果

拿出course字段中的每一个元素在id_courses中进行判断,看是否存在。

select id,
case when array_contains(id_courses, courses[0]) then 1 else 0 end as a,
case when array_contains(id_courses, courses[1]) then 1 else 0 end as b,
case when array_contains(id_courses, courses[2]) then 1 else 0 end as c,
case when array_contains(id_courses, courses[3]) then 1 else 0 end as d,
case when array_contains(id_courses, courses[4]) then 1 else 0 end as e,
case when array_contains(id_courses, courses[5]) then 1 else 0 end as f 
from id_courses;
hive学习(三):练习题——collect_set及array_contain(学生选课情况
image.png

函数解释:

一、collect_set(列名):将分组后的去重参数用[x1,x2,x3……]合并

例:
(1)存在一份数据test

c1     c2      c3 (列名)
a       b       1 
a       b       2 
a       b       3 
c       d       4 
c       d       5 
c       d       6

(2)使用collect_set函数(会去重,collect_list不会去重)

select c1,c2,collect_set(c3) as c3 from test group by c1,c2;

(3)结果如下:

c1 c2 c3
a  b  ["1","2","3"]
c  d  ["4","5","6"]
二、concat_ws 函数通常会搭配collect_set使用

concat_ws的第一个参数是指定符号进行分隔(例如 1,2,3),
concat则没有分隔符(例如 123)

(1) 在上题步骤2结合concat_ws使用

select c1,c2,concat_ws(',',collect_set(c3))  as c3
from test
group by c1,c2;

(2)结果如下:

c1     c2      c3
a       b       1,2,3
c       d       4,5,6
三、array_contain属于集合函数,返回的是True OR False

语法:
array_contains(Array<T>, value)
解释:
Returns TRUE if the array contains value.
如该数组Array<T>包含value返回true,否则返回false


top8488大数据 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:hive学习(三):练习题——collect_set及array_contain(学生选课情况
喜欢 (0)
[]
分享 (0)

您必须 登录 才能发表评论!