文章目录

  • 函数基本操作
  • 典型高级函数
  • 分组排序取TopN
  • 分组排序取TopN示例
  • 行转列
  • 行转列示例
  • 列转行
  • 列转行示例
  • 排序函数
  • 分组与去重函数
  • 参考文献


函数基本操作

  • 查看指定函数的描述信息：desc function 函数名;
  • 显示函数扩展内容：desc function extended 函数名;

典型高级函数

分组排序取TopN

想要实现分组排序功能需要使用row_number和over函数。row_number和over函数通常搭配在一起使用。

  • row_number会对数据编号，编号从1开始
  • over可以理解为把数据划分到一个窗口内，里面可以加上partition by，表示按照字段对数据进行分组，还可以加上order by表示对每个分组内的数据按照某个字段进行排序

分组排序取TopN示例

需求：有一份学生的考试分数信息，包含语文、数学、英语这三门，现需要计算出班级中单科排名前三名学生的姓名

  1. 测试数据：student_score.data
id      name    sub     score
1       zs1     chinese 80
2       zs1     math    90
3       zs1     english 89
4       zs2     chinese 60
5       zs2     math    75
6       zs2     english 80
7       zs3     chinese 79
8       zs3     math    83
9       zs3     english 72
10      zs4     chinese 90
11      zs4     math    76
12      zs4     english 80
13      zs5     chinese 98
14      zs5     math    80
15      zs5     english 70
  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';
  1. 导入数据
load data local inpath '/data/soft/hivedata/student_score.data' into table student_score
  1. 查询数据_分组排序
select * ,row_number() over(partition by sub order by score desc)as num from student_score;

13      zs5     chinese 98      1
10      zs4     chinese 90      2
1       zs1     chinese 80      3
7       zs3     chinese 79      4
4       zs2     chinese 60      5
3       zs1     english 89      1
6       zs2     english 80      2
12      zs4     english 80      3
9       zs3     english 72      4
15      zs5     english 70      5
2       zs1     math    90      1
8       zs3     math    83      2
14      zs5     math    80      3
11      zs4     math    76      4
5       zs2     math    75      5
  • 查询数据_获取前三名
select * from (
               select *, row_number() over (partition by sub order by score desc) as num 
               from student_score 
               ) s where s.num<=3>;
               
13      zs5     chinese 98      1
10      zs4     chinese 90      2
1       zs1     chinese 80      3
3       zs1     english 89      1
6       zs2     english 80      2
12      zs4     english 80      3
2       zs1     math    90      1
8       zs3     math    83      2
14      zs5     math    80      3

【注意】

  • row_number() over()是正常排序
  • rank() over()是跳跃排序，有两个第一名时接下来就是第三名（在各个分组内）
  • dense_rank() over()是连续排序，有两个第一名时仍然跟着第二名（在各个分组内）

行转列

行转列就是把多行数据转为一列数据。针对行转列这种需求主要需要使用到CONCAT_WS()、COLLECT_SET() 、COLLECT_LIST()函数。

  • CONCAT_WS()函数可以实现根据指定的分隔符拼接多个字段的值，最终转化为一个带有分隔符的字符串。
  • COLLECT_SET() 返回一个set集合，集合汇中的元素不重复，一般和group by 结合在一起使用
  • COLLECT_LIST()返回一个list集合，集合中的元素会重复，一般和group by 结合在一起使用

行转列示例

  1. 测试数据：student_favors.data
zs      swing
zs      footbal
zs      sing
zs      codeing
zs      swing
  1. 建表
create external table student_favors(
name string,
favor string
)row format delimited
fields terminated by '\t'
location '/data/student_favors';
  1. 导入数据
load data local inpath '/data/soft/hivedata/student_favors.data' into table student_favors
  1. 列转行
select 
      name,concat_ws(',',collect_set(favor)) as favors 
from 
      student_favors
group by name;

name    favors
zs      swing,footbal,sing,codeing

【注意】如果没有添加group by命令，会报SemanticException错误。

列转行

列转行主要使用到SPLIT()、EXPLODE()和LATERAL VIEW三个函数。

  • Split函数，接受一个字串符和切割规则，使用切割规则对字符串中的数据进行切割，最终返回一个array数组
  • explode(Array)：表示把数组中的每个元素转成一行
  • explode(Map) ：表示把map中每个键值对转成一行，key为一列，value为一列
  • lateral view可以产生一个支持别名的虚拟表

列转行示例

  1. 测试数据：student_favors2.data
zs      swing,footbal,sing
ls      codeing,swing
  1. 建表
hive (default)> create external table student_favors_2(
              > name string,
              > favorList string
              > )row format delimited
              > fields terminated by '\t'
              > location '/data/student_favors_2';
  1. 导入数据
load data local inpath '/data/soft/hivedata/student_favors2.data' into table student_favors_2
  1. 行转列
# 先使用split对favorlist字段进行切割
# 再使用explode对数据进行操作
# 使用lateral view把explode返回的数据作为一个虚拟表，起名字为table1，然后给这个表里面的那一列数据起一个名字叫favor_new
hive (default)> SELECT
              >      name,favor_new
              > FROM
              >      student_favors_2 
              > lateral view
              >      explode(split(favorList,',')) table1 as favor_new;
OK
name    favor_new
zs      swing
zs      footbal
zs      sing
ls      codeing
ls      swing

排序函数

  • ORDER BY：对查询的结果做一次全局排序，使用这个语句的时候生成的reduce任务只有一个
  • SORT BY：局部排序
  • DISTRIBUTE BY：只会根据指定的key对数据进行分区，但是不会排序。一般情况下可以和sort by 结合使用，先对数据分区，再进行排序。两者结合使用的时候distribute by必须要写在sort by之前
  • CLUSTER BY：cluster by的功能就是distribute by和sort by的简写形式，也就是 cluster by id 等于 distribute by id sort by id

【注意】被cluster by指定的列只能是升序，不能指定asc和desc

分组与去重函数

  • GROUP BY ：对数据按照指定字段进行分组
  • DISTINCT：对数据中指定字段的重复值进行去重

实现

第一种：select count(distinct name) from order
第二种：select count(tmp.name) from (select name from order group by name) tmp

分析

第一种：使用distinct会将所有的name都shuffle到一个reducer里面，性能较低
第二种：先对name分组，因为分组的同时其实就是去重，此时是可以并行计算的，然后再计算count即可，性能高。

参考文献

https://www.imooc.com/wiki/BigData