目录
一、排名函数 -大名鼎鼎
1、row_number()
2、rank()
2、rank()
3、dense_rank()
二、练习
三、自定义函数
1、将字母变大写案例
2、输入 出生年月日求年龄 函数
一、排名函数 -大名鼎鼎
row_number() rank() dense_rank()
1、row_number()
row_number从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
效果如下：
98 1
97 2
97 3
96 4
95 5
95 6没有并列名次情况，顺序递增2、rank()
生成数据项在分组中的排名，排名相等会在名次中留下空位
2、rank()
生成数据项在分组中的排名，排名相等会在名次中留下空位
效果如下：
98 1
97 2
97 2
96 4
95 5
95 5
94 7
有并列名次情况，顺序跳跃递增3、dense_rank()
生成数据项在分组中的排名，排名相等会在名次中不会留下空位
效果如下：
98 1
97 2
97 2
96 3
95 4
95 4
94 5
有并列名次情况，顺序递增4、案例演示
1 gp1808 80
2 gp1808 92
3 gp1808 84
4 gp1808 86
5 gp1808 88
6 gp1808 70
7 gp1808 98
8 gp1808 84
9 gp1808 86
10 gp1807 90
11 gp1807 92
12 gp1807 84
13 gp1807 86
14 gp1807 88
15 gp1807 80
16 gp1807 92
17 gp1807 84
18 gp1807 86
19 gp1805 80
20 gp1805 92
21 gp1805 94
22 gp1805 86
23 gp1805 88
24 gp1805 80
25 gp1805 92
26 gp1805 94
27 gp1805 86建表，加载数据：
create table if not exists stu_score(
userid int,
classno string,
score int
)
row format delimited
fields terminated by ' ';load data local inpath '/home/hivedata/stu_score.txt' overwrite into table stu_score;需求一：对每个班级的每次考试按照考试成绩倒序
select *,dense_rank() over(partition by classno order by score desc) from stu_score;select *,dense_rank() over(order by score desc) `全年级排名` from stu_score;需求二：获取每次考试的排名情况
select *,
-- 没有并列，相同名次依顺序排
row_number() over(distribute by classno sort by score desc) rn1,
-- rank()：有并列，相同名次空位
rank() over(distribute by classno sort by score desc) rn2,
-- dense_rank()：有并列，相同名次不空位
dense_rank() over(distribute by classno sort by score desc) rn3
from stu_score;
运行结果：26 gp1805 94 1 1 1
21 gp1805 94 2 1 1
25 gp1805 92 3 3 2
20 gp1805 92 4 3 2
23 gp1805 88 5 5 3
27 gp1805 86 6 6 4
22 gp1805 86 7 6 4
24 gp1805 80 8 8 5
19 gp1805 80 9 8 5
11 gp1807 92 1 1 1
16 gp1807 92 2 1 1
10 gp1807 90 3 3 2
14 gp1807 88 4 4 3
13 gp1807 86 5 5 4
18 gp1807 86 6 5 4
12 gp1807 84 7 7 5
17 gp1807 84 8 7 5
15 gp1807 80 9 9 6
7 gp1808 98 1 1 1
2 gp1808 92 2 2 2
5 gp1808 88 3 3 3
9 gp1808 86 4 4 4
4 gp1808 86 5 4 4
8 gp1808 84 6 6 5
3 gp1808 84 7 6 5
1 gp1808 80 8 8 6
6 gp1808 70 9 9 7需求三：求每个班级的前三名
select * from (
select * ,dense_rank() over(partition by classno order by score desc) as paiming from stu_score) t where paiming <=3;二、练习
孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
大海 语文 94
大海 数学 56
大海 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78create table score(
name string,
subject string,
score int)
row format delimited fields terminated by "\t";load data local inpath '/home/hivedata/test_e.txt' into table score;1、计算每门学科成绩排名
select *,dense_rank() over (partition by subject order by score desc) from score;2、求出每门学科前三名的学生
select * from (select *,dense_rank() over (partition by subject order by score desc) paiming from score) t where paiming <=3;上午的那个题目：到当前为止，每一个顾客最近三次的消费金额？
t_order.name t_order.orderdate t_order.cost
先获取每一个人最近三次的消费记录select * from (
select *,row_number() over(partition by name order by orderdate desc) paixu from t_order ) twhere t.paixu <=3;t.name t.orderdate t.cost t.paixu
mart 2018-04-13 94 1
mart 2018-04-11 75 2
mart 2018-04-09 68 3
neil 2018-06-12 80 1
neil 2018-05-10 12 2
saml 2018-04-06 42 1
saml 2018-02-03 23 2
saml 2018-01-08 55 3
tony 2018-01-07 50 1
tony 2018-01-04 29 2
tony 2018-01-02 15 3
将以上结果按照名字分组，并且sum值select name,sum(cost) from (
select *,row_number() over(partition by name order by orderdate desc) paixu from t_order ) t where t.paixu <=3 group by name ;
三、自定义函数
hive的内置函数满足不了所有的业务需求。hive提供很多的模块可以自定义功能，比如：自定义函数、serde、输入输出格式等。而自定义函数可以分为以下三类：
1）UDF: user defined function：用户自定义函数，一对一的输入输出 （最常用的）。比如abs()
2）UDAF: user defined aggregation function：用户自定义聚合函数，多对一的输入输出,比如：count sum max avg。
3) UDTF: user defined table-generate function ：用户自定义表生产函数 一对多的输入输出，比如：lateral view explode1、将字母变大写案例
创建Maven项目：MyFunction
在pom.xml,加入以下maven的依赖包
<dependency><groupId>org.apache.hive</groupId><artifactId>hive-exec</artifactId><version>3.1.2</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common --><dependency><groupId>org.apache.hadoop</groupId><artifactId>hadoop-common</artifactId><version>3.3.1</version></dependency>需要继承一个类：继承org.apache.hadoop.hive.ql.udf.generic.GenericUDF，并重写抽象方法。
需求：编写一个自定义函数，让其字母大写变小写
package com.bigdata;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;public class LowerString extends GenericUDF {// 初始化操作// 假如传递的参数个数不是1个，就抛异常@Overridepublic ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {if (objectInspectors.length != 1) {// 说明参数的数量不对throw new UDFArgumentException("参数数量错误");}// 返回值类型检查return PrimitiveObjectInspectorFactory.javaStringObjectInspector;}// 编写具体代码的地方@Overridepublic Object evaluate(DeferredObject[] deferredObjects) throws HiveException {// 获取到传入进来的参数String inputString = deferredObjects[0].get().toString();// 逻辑处理if (inputString == null || inputString.length() == 0) {return "";}// abcreturn inputString.toUpperCase();}// 返回自定义函数的描述@Overridepublic String getDisplayString(String[] strings) {return "该函数可以将大写的字母变为小写";}
}
编写好之后，打包 package,变为一个jar包。
将该jar包放入 hive的lib文件夹下。
函数的加载方式：
第一种：命令加载 (只针对当前session有效)
1、将MyFunction-1.0-SNAPSHOT.jar 放入/opt/installs/hive/lib/目录下：
2. 将编写好的UDF打包并上传到服务器，将jar包添加到hive的classpath中hive> add jar /opt/installs/hive/lib/MyFunction-1.0-SNAPSHOT.jar;
3. 创建一个自定义的临时函数名hive> create temporary function myUpper as 'com.bigdata.LowerString';
4. 查看我们创建的自定义函数，hive> show functions;
5.在hive中使用函数进行功能测试
select myUpper('yunhe');
6. 如何删除自定义函数？在删除一个自定义函数的时候一定要确定该函数没有调用hive> drop temporary function if exists myupper;第二种方式：
1. 将编写好的自定函数上传到服务器2. 写一个配置文件，将添加函数的语句写入配置文件中，hive在启动的时候加载这个配置文件
[root@yunhe01 ~]# vi $HIVE_HOME/conf/hive-init
文件中的内容如下
add jar /opt/installs/hive/lib/MyFunction-1.0-SNAPSHOT.jar;
create temporary function myUpper as 'com.bigdata.LowerString';3. 启动hive时
[root@yunhe01 ~]# hive -i $HIVE_HOME/conf/hive-init第三种方式：
在.hiverc 文件中，添加
add jar /opt/installs/hive/lib/MyFunction-1.0-SNAPSHOT.jar;
create temporary function myUpper as 'com.bigdata.LowerString';每次hive启动即可使用。2、输入 出生年月日求年龄 函数
1. age=当前年-生日的年份 2001-2-1 2024-2001 = 23
2. 判断月份，当前月份小于生日月份，age-1 2001-3-10 22
3. 月份相等，判断当前的日期，如果日期小于生日日期，age-1 2001-2-23 22代码演示：
package com.bigdata.udf;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector;import java.time.Duration;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;/***/
public class BirthdayToAgeUDF extends GenericUDF {@Overridepublic ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {// 1、检查参数的数量是否正确if (objectInspectors.length != 1) {throw new UDFArgumentException("参数的数量错误");}// 2、检查参数的类型是否正确ObjectInspector inspector = objectInspectors[0];if (!(inspector instanceof StringObjectInspector)) {throw new UDFArgumentException("参数的类型错误");}return PrimitiveObjectInspectorFactory.javaIntObjectInspector;}@Overridepublic Object evaluate(DeferredObject[] deferredObjects) throws HiveException {// 1、获取参数String birthdayStr = deferredObjects[0].get().toString();// 2、判断参数的有效性DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");try {LocalDate birthday = LocalDate.parse(birthdayStr, formatter);int birthYear = birthday.getYear();int birthMonth = birthday.getMonthValue();int birthDay = birthday.getDayOfMonth();int nowYear = LocalDate.now().getYear();int nowMonth = LocalDate.now().getMonthValue();int nowDay = LocalDate.now().getDayOfMonth();// 年龄计算int age = nowYear - birthYear;if (nowMonth < birthMonth) {--age;} else if (nowMonth == birthMonth && nowDay < birthDay) {--age;}return age;} catch (Exception ignore) {return -1;}}@Overridepublic String getDisplayString(String[] strings) {return null;}
}
hive> add jar /usr/local/hive-3.1.2/lib/MyFunction-1.0-SNAPSHOT.jar;
Added [/usr/local/hive-3.1.2/lib/MyFunction-1.0-SNAPSHOT.jar] to class path
Added resources: [/usr/local/hive-3.1.2/lib/MyFunction-1.0-SNAPSHOT.jar]
hive> create temporary function myAge as 'com.bigdata.udf.BirthdayToAgeUDF';
OK
Time taken: 0.048 seconds
hive> select myAge('1987-02-06');
OK
35
Time taken: 0.187 seconds, Fetched: 1 row(s)另外一种写法：
package com.bigdata;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;public class BirthdayToAgeUDF extends GenericUDF {@Overridepublic ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {if (objectInspectors.length != 1) {// 说明参数的数量不对throw new UDFArgumentException("参数数量错误");}// 返回值类型检查return PrimitiveObjectInspectorFactory.javaStringObjectInspector;}@Overridepublic Object evaluate(DeferredObject[] deferredObjects) throws HiveException {// 获取参数String birthday = deferredObjects[0].get().toString();// 获取用户输入的年月日SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");int age = -1;try {Date date = format.parse(birthday);// 想要年月日Calendar c = Calendar.getInstance();c.setTime(date);int year = c.get(Calendar.YEAR);int month = c.get(Calendar.MONTH);int day = c.get(Calendar.DAY_OF_MONTH);// 获取当前时间的年月日Calendar c1 = Calendar.getInstance();int year2 = c1.get(Calendar.YEAR);int month2 = c1.get(Calendar.MONTH);int day2 = c1.get(Calendar.DAY_OF_MONTH);// 根据规则比较age = year2 - year;if(month2 < month){age--;}else if(month2 == month && day2 < day){age--;}} catch (ParseException e) {throw new RuntimeException(e);}return age;}@Overridepublic String getDisplayString(String[] strings) {return "将出生年月变为年龄";}
}
