HIVE函数之行转列、列转行、分组排序取TopN

一、分组排序取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;