hive学习（三）：练习题——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;

image.png

第二步：找出每名学生的选课集合

hive> select id as id,collect_set(course) as course from t_course group by id;

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;

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;

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

最后编辑于：2019.11.14 12:18:16