文章目录
- 一、窗口函数
- 二、自定义函数
- 三、案例
一、窗口函数
如果要查询详细记录和聚合函数，必须要经过两次查询，比较麻烦，这个时候使用窗口函数，会方便很多
（1）窗口函数是针对每一行数据的
（2）如果over中没有指定参数，则默认窗口大小为全部结果集
注意：开窗函数不能写在where里面
--求01课程的平均成绩
select
*,
avg(sc.s_score) over()
from mydb2.score sc
where sc.c_id='01'
--求每门课的平均成绩
select
*,
avg(sc.s_score) over(distribute by sc.c_id) avg_score
from mydb2.score
;
--求每门功课在学生的总成绩中的占比情况
selct
*,
sc.s_score/sum(sc.s_score) over(distribute by sc.s_id) sum_score
from mgdb2.score sc- 排名函数
第一种函数：row_number从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
第二种函数：RANK()生成数据项在分组中的排名，排名相等会在名次中留下空位
第三种函数：DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位
--返回每位同学的每门功课的排名情况
select
*,
row_number() over(distribute by sc.c_id sort by sc.s_score desc) rn,
rank() over(distribute by sc.c_id sort by sc.s_score desc) r,
dense_rank() over(distribute by sc.c_id sort by sc.s_score desc) sr
from mydb2.score sc
--求每门功课的前三名
select
*
from
(
select
*,
row_number() over(distribute by sc.c_id sort by sc.s_score desc) rn
from mydb2.score sc
)t
where t.rn<4;
--求每门功课与最高分之间的差值
select
*,
max(sc.s_score) over(distribute by sc.c_id) - sc.s_score diff_score
from mydb2.score sc- distribute by子句
在over窗口中进行分组，对某一字段进行分组统计，窗口的大小就是一个组的所有记录
序列函数
- （1）fist_value、last_value
first_value：第一个版本
last_value：最后一个版本
select
*,
first_value(sc.s_score) over (patition by sc.c_id order by sc.s_score desc) max_score,
last_value(sc.s_score) over (patition by sc.c_id order by sc.s_score desc) min_score,
应当修改成下面这种
select
*,
first_value(sc.s_score) over (patition by sc.c_id order by sc.s_score desc rows between unbounded preceding and unbounded following) max_score,
last_value(sc.s_score) over (patition by sc.c_id order by sc.s_score desc rows between unbounded preceding and unbounded following) min_score,
默认窗口
- window子句
给窗口设定窗口大小
默认的窗口大小都是从第一行到当前行
窗口的带下可以设置成以下：
（1）preceding：往前
（2）following：往后
（3）current row：当前行
（4）unbounded：边界
（5）unbounded preceding：表示起点
（6）unbounded following：表示终点
窗口大小的写法：
rows between and - lag、lead
lag返回当前数据行的前第n行的数据
lead返回当前数据行的后第n行的数据
用法：
lag(columnName,offset,default_value)
lead(columnName,offset,default_value)
--求出学生每次考试与前一次考试的成绩差
select
*,
lag(sc.s_score,1,sc.s_score) over(distribute by sc.s_id sort by sc.c_id asc) as up_score --default_value:表示如果上一次考试为空，则给一个默认值，也就是sc.s_score
from mydb2.score sc
;- ntile
ntile 是Hive很强大的一个分析函数。可以看成是：它把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
--求每门功课成绩最好的前20%的学生
分析：每门功课分成5份
select
*,
ntile(5) over(distribute by sc.c_id sort by sc.s_score desc) ranks
from mydb2.score sc
;distribute by和partition by的区别
distribute by对应MapReduce中的自定义分组比较器
partition by是自定义分区
sort by和order by的区别
- cume_dist
- percentile
- percent_rank
窗口函数：所有的聚合函数都可以是窗口函数
窗口大小：默认是从第一行到最后一行，但是max
什么情况下使用窗口函数？
既有明细数据，又有返回汇总函数
二、自定义函数
自定义函数应当要遵循实现单一的功能原则
为什么要自定义函数？
hive的内置函数不能满足所有的业务需求
hive提供了很多模块都可以自定义功能：
比如自定义函数、serde（序列化反序列化函数）、输入输出格式等
UDF(User-Defined-Function) ----- 一进一出
UDAF（User- Defined Aggregation Funcation） ----- 多进一出 （聚合函数MR）
UDTF(User-Defined Table-Generating Functions) ----- 一进多出（生成多行结果）
- 编写UDF
方式1.继承UDF，重写evaluate()，允许重载
方式2.继承genericUDF，重写initlizer()、getdisplay()、evaluate()
-- 小写转大写
UDF使用方式
第一种：本session有效
1.将自定义的jar上传到服务器，并将jar包添加到hive的class path中
hive>add jar jar包名；或者配置hive.aux.jars.path
将文件放到这个路径中
2.添加一个临时函数名
create temporary function toupcase as
3.测试自定义函数的方法
（1）
（2）带from的测试
create table if not exists dual(url string);
insert into table dual values(''); --保证这个表有一条记录即可
4.用完之后删除函数
临时的session也可以不删除直接退出hive>drop function if exists toupcase;
会报错：
解决方式
hive>drop temporary function if exists toupcase;第二种方式：
1.上传jar包
2.创建一个配置文件，将上面的创建临时函数的语句写入到配置文件中
hive在启动的时候，可以指定加载这个配置文件
将下面的内容添加进配置文件hive-init文件
add jar jar包名
create temporary function toupcase as
加载方式为hive -i /root/hive-init
第三种方式：
hive的安装目录的bin目录下创建一个文件，文件名叫.hiverc
在配置文件中将上面的创建临时函数的语句写入到配置文件
直接启动hive，不需要加载配置文件
查看是否添加成功：show functions;
第四种方式：编译源码
三、案例
--生日转换成年龄
输入：1990-12-01
输入：29
