Hive09：Hive的函数操作：基本函数使用、高阶函数应用_hive取()里的值

一、函数的基本操作

和mysql一样的，hive也是一个主要做统计的工具，所以为了满足各种各样的统计需要，他也内置了相当多的函数，我们可以通过show functions;来查看hive中的内置函数。

show functions;
1

查看指定函数的描述信息我们可以使用：desc function functionName;

desc function year;
1

显示函数的扩展内容

desc function extended year;
1

二、Hive高级函数应用

普通的就不说了，mysql中支持的函数这里面大部分都支持，并且hive支持的函数比mysql还要多，在这里我们主要挑几个典型的说一下

1、分组排序取TopN

一个典型的应用场景，分组排序取TopN操作

主要需要使用到ROW_NUMBER() 和 OVER()函数

row_number和over函数通常搭配在一起使用

row_number会对数据编号，编号从1开始

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

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

基础数据是这样的：

1       zs1     chinese 80
2       zs1     match   90
3       zs1     english 89
4       zs2     chinese 60
5       zs2     match   75
6       zs2     english 80
7       zs3     chinese 79
8       zs3     match   83
9       zs3     english 72
10      zs4     chinese 90
11      zs4     match   76
12      zs4     english 80
13      zs5     chinese 98
14      zs5     math    80
15      zs5     english 70
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

建表

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 -put或者load都行。

hdfs dfs -put /data/soft/hivedata/student_score.data /data/student_score
1

我们先使用row_number对数据编号，看一下是什么样子，row_number不能单独使用，在这里需要加上over

select * from student_score;
select *,row_number() over() from student_score;
1
2

结果如下：在这里相当于给表里面的所有数据编了一个号，从1开始

但是我们是希望对这些数据，先分组，再对组内数据进行排序，再编号

所以就需要在over函数内部添加partiton by进行分组，添加order by 进行排序，最终给生成的编号起了换一个别名num
执行sql

select *, row_number() over (partition by sub order by score desc) as num from student_score;
1

结果如下：

接着就可以获取前三名了，
执行sql

select * from (
select *, row_number() over (partition by sub order by score desc) as num 
from student_score 
) s where s.num<=3;
1
2
3
4

结果如下：

前面SQL中的row_number()可以替换为rank()或者dense_rank()
其中：rank()表示上下两条记录的score相等时，记录的行号是一样的，但下一个score值的行号递增N（N是重复的次数），比如：有两条并列第一，下一个是第三，没有第二
执行sql

select *, rank() over (partition by sub order by score desc) as num from student_score;
1

结果如下：

dense_rank()表示上下两条记录的score相等时，下一个score值的行号递增1，比如：有两条并列第一，下一个是第二
执行sql

select *, dense_rank() over (partition by sub order by score desc) as num from student_score;
1

结果如下：

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

2、行转列

行转列就是把多行数据转为一列数据

针对行转列这种需求主要需要使用到CONCAT_WS()、COLLECT_SET() 、COLLECT_LIST()函数

先看一下这几个函数的描述信息，注意，有的函数在帮助文档里面没有描述信息

（1）CONCAT_WS()

desc function CONCAT_WS;
1

CONCAT_WS()函数可以实现根据指定的分隔符拼接多个字段的值，最终转化为一个带有分隔符的字符串

它可以接收多个参数，第一个参数是分隔符，后面的参数可以是字符串或者字符串数组，最终就是使用分隔符把后面的所有字符串拼接到一块

（2）COLLECT_LIST()

desc function COLLECT_LIST;
1

这个函数可以返回一个list集合，集合中的元素会重复，一般和group by 结合在一起使用，一会再演示。

（3）COLLECT_SET()

desc function COLLECT_SET;
1

这个函数可以返回一个set集合，集合汇中的元素不重复，一般和group by 结合在一起使用，一会再演示。

根据前面的分析，使用这几个函数我们就可以实现行转列这个需求了

某位学生有很多爱好，数据如下

zs      swing
zs      football
zs      sing
zs      coding
zs      swing
1
2
3
4
5

期望的结果是这样的

zs	swing,footbal,sing,codeing,swing
1

分析一下：
在这其实就是对数据进行了分组，分组之后可以把相同人的爱好保存到一个数组中，再把数组中的数据转成使用逗号分割的字符串

开始对原始数据建表

上传数据：

hdfs dfs -put /data/soft/hivedata/student_favors.data  /data/stud                                                                          	ent_favors
1

查看数据

select * from student_favors;
1

我们想要把数据转换为这种格式：zs swing,footbal,sing,codeing,swing

先对name字段进行分组，把favor转成一个数组

select name,collect_list(favor) as favor_list from student_favors group by name;
1

然后再使用concat_ws把数组中的元素按照指定分隔符转成字符串
这样就实现了多行数据转为一列数据了
执行行转列操作

select name,concat_ws(',',collect_list(favor)) as favor_list from student_favors group by name;
1

我们发现这里面有一些爱好是重复的，如果不希望出现重复的话可以使用COLLECT_SET()
执行sql

select name,concat_ws(',',collect_set(favor)) as favor_list from student_favors group by name;
1

3、列转行

列转行是和刚才的行转列反着来的，列转行可以把一列数据转成多行

主要使用到SPLIT()、EXPLODE()和LATERAL VIEW
看一下这几个函数

（1）SPLIT()

desc function SPLIT;
1

split函数，接受一个字串符和切割规则，就类似于java中的split函数，使用切割规则对字符串中的数据进行切割，最终返回一个array数组。

（2）EXPLODE()

desc function EXPLODE;
1

explode函数可以接受array或者map
explode(ARRAY)：表示把数组中的每个元素转成一行
explode(MAP) ：表示把map中每个key-value对，转成一行，key为一列，value为一列

（3）LATERAL VIEW

Lateral view 通常和split， explode等函数一起使用。
split可以对表中的某一列进行切割，返回一个数组类型的字段，explode可以对这个数组中的每一个元素转为一行，lateral view可以对这份数据产生一个支持别名的虚拟表

原始数据如下

zs      swing,footbal,sing
ls      coding,swing
1
2

希望的结果是这样的

zs	swing
zs	footbal
zs	sing
ls	codeing
ls	swing
1
2
3
4
5

接着建表

create external table student_favors_2(
name string,  
favorlist string
)row format delimited 
fields terminated by '\t'
location '/data/student_favors_2';
1
2
3
4
5
6

上传数据

hdfs dfs -put /data/soft/hivedata/student_favors_2.data /data/student_favors_2
1

查看数据

select * from student_favors_2;
1

先使用split对favorlist字段进行切割

select split(favorlist,',') from student_favors_2;
1

再使用explode对数据进行操作

select explode(split(favorlist,',')) from student_favors_2;
1

其实到这里已经实现了列转行了，但是还需要把name字段拼接上，这时候就需要使用lateral view了，否则直接查询name字段会报错

lateral view相当于把explode返回的数据作为一个虚拟表来使用了，起名字为table1，然后给这个表里面的那一列数据起一个名字叫favor_new，如果有多个字段，可以再后面指定多个。这样在select后面就可以使用这个名字了，有点类似join操作了

select name,favor_new from student_favors_2 lateral view explode(split(favorlist, ',')) table1 as favor_new ;
1

结果如下：

三、Hive排序相关函数

1、ORDER BY

Hive中的order by跟传统的sql语言中的order by作用是一样的，会对查询的结果做一次全局排序，使用这个语句的时候生成的reduce任务只有一个

2、SORT BY

Hive中指定了sort by，如果有多个reduce，那么在每个reducer端都会做排序，也就是说保证了局部有序（每个reducer出来的数据是有序的，但是不能保证所有的数据是全局有序的，除非只有一个reducer）

使用前面的t2_bak表，表中的数据如下：

select * from t2_bak;
1

执行排序SQL

select id from t2_bak sort by id;
1

刚才我们说sort by是局部有序，为什么最终的结果还是全局有序呢？
看里面的日志，现在只有一个reduce任务，所以最终结果还是有序的

动态设置reduce任务数量为2，然后再执行排序的SQL

set mapreduce.job.reduces = 2;
select id from t2_bak sort by id;
1
2

此时会发现数据就没有全局排序了，因为有多个reduce了。
不过针对ORDER BY来说，你动态设置再多的reduce数量都没有用，最后还是只产生1个reduce。

3、DISTRIBUTE BY

ditribute by是控制map的输出到reducer是如何划分的

ditribute by：只会根据指定的key对数据进行分区，但是不会排序。

一般情况下可以和sort by 结合使用，先对数据分区，再进行排序
两者结合使用的时候distribute by必须要写在sort by之前

先来看一下单独ditribute by的使用

set mapreduce.job.reduces = 2;
select id from t2_bak distribute by id;
1
2

可以结合sort by实现分区内的排序，默认是升序，可以通过desc来设置倒序

set mapreduce.job.reduces = 2;
select id from t2_bak distribute by id sort by id;
1
2

4、CLUSTER BY

cluster by的功能就是distribute by和sort by的简写形式
也就是 cluster by id 等于 distribute by id sort by id

set mapreduce.job.reduces = 2;
select id from t2_bak cluster by id;
1
2

四、Hive 的分组和去重函数

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

1、实现

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

2、分析

第一种：使用distinct会将所有的name都shuffle到一个reducer里面，性能较低

第二种：先对name分组，因为分组的同时其实就是去重，此时是可以并行计算的，然后再计算count即可，性能高。