一、分组排序取TopN
我们的需求是这样,有一份学生的考试分数信息,语文、数学、英语这三门,需要计算出班级中单科排名 前三名学生的姓名
1、建表并导入数据
create external table student_score(
id int,
name string,
sub string,
score int
)row format delimited
fields terminated by '\t'
location '/data/student_score';
hdfs dfs -put /data/soft/hivedata/student_score.data /data/student_score
2、组内分组并按照分数排序
select *,row_number() over(partition by sub order by score desc) num
from student_score
3、每组取前三名
select * from(
select *,row_number() over(partition by sub order by score desc) num from student_score
) s where s.num<=3
4、备注
- row_number over() 是正常排序
- rank() over()是跳跃排序,有两个第一名时候,接下来就是第三名(在各个分组内)
- dense_rank() over() 是连续排序,有两个第一名的时候,依旧跟着第二名(在各个分组内)
二、行转列
1、行转列就是把多行的数据转成一列数据
- CONCAT_WS() :可以实现根据指定的分隔符拼接多个字段的值,最终转换成带有分隔符的字符串,它可以接受多个参数,第一个是分隔符,后面的参数可以是字符串或是字符串数组,最终就是使用分隔符吧后面的字符串拼到一块
- COLLECT_SET() :这个函数可以返回一个set集合
- COLLECT_LIST():这个函数可以返回一个list集合
2、建表并导入数据
create external table student_favors(
name string,
favor string
)row format delimited
fields terminated by '\t'
location '/data/student_favors';
hdfs dfs -put /data/soft/hivedata/student_favors.data /data/student_favors
3、语句:
3.1、 先把多列数据转成一行数据
select name,concat_ws(',',collect_list(favor)) as favor_list
from student_favors group by name;
3.2、把list字段转成换string类型
select name,concat_ws(',',collect_list(favor)) as favor_list
from student_favors group by name;
三、列转行
1、列转行可以把一列数据转成多行
- split() :接收一个字符串和切割规则,使用切割规则对数据的行进行切割,最终返回一个array数组
- explode(array) :表示把数组的每个元素转成一行
- explode(map) :表示把map中每个key-value转成一行,key为一列,value为一行
- lateral view :对数据产生一个支持别名的虚拟表
2、建表并导入数据
create external table student_favors_2(
name string,
favorlist string
)row format delimited
fields terminated by '\t'
location '/data/student_favors_2';
hdfs dfs -put /data/soft/hivedata/student_favors_2.data /data/student_favors_2
3、语句
3.1 先使用split对favorlist字段进行切割
select split(favorlist,',') from student_favors_2;
3.2 再使用explode对数据进行操作
select explode(split(favorlist,',')) from student_favors_2;
3.3 laterview相相当于把explode返回的数据作为一个虚拟表来使用了,起名字为table1,然后给这个表里面的 那一列数据起一个名字叫favor_new,如果有多个字段,可以再后面指定多个。这样在select后面就可以 使用这个名字了,有点类似join操作了
select name,favor_new from student_favors_2 lateral view explode(split(favorlist,','))table1 as favor_new;