Hive(十四)窗口函数

窗口函数的概念
首先，需要认识到，窗口函数并不是只有 hive 才有的，SQL 语法标准中，就有窗口函数。 并且 mysql，oracle等数据库都实现了窗口函数。 而 hive 自带的窗口函数功能，则是对原有 hive sql 语法的补充和加强。

那么什么时候，会用到窗口函数

 举两个小栗子：

排名问题：每个部门按业绩排名 topN 问题：

找出每个部门排名前 N 的员工进行奖励

面对这类需求，就需要使用窗口函数了。

窗口函数的基本语法如下：

<窗口函数>window_name  over ( [partition by 字段...]  [order by 字段...]  [窗口子句] )

窗口函数可以拆分为【窗口+函数】

• 窗口：over()，指明函数要处理的数据范围

• 函数：指明函数计算逻辑

window_name：给窗口指定一个别名。
over：用来指定函数执行的窗口范围，如果后面括号中什么都不写，即over() ，意味着窗口包含满足where 条件的所有行，窗口函数基于所有行进行计算。
符号[] 代表：可选项；  | ： 代表二选一
 partition by 子句： 窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行。分组间互相独立。
order by 子句：每个partition内部按照哪些字段进行排序，如果没有partition ，那就直接按照最大的窗口排序，且默认是按照升序（asc）排列。
窗口子句：显示声明范围（不写窗口子句的话，会有默认值）

 

 窗口函数是 SQL 中一类特别的函数。和聚合函数相似，窗口函数的输入也是多行记录。不 同的是，聚合函数的作用于由 GROUP BY 子句聚合的组，而窗口函数则作用于一个窗口， 这里，窗口是由一个 OVER 子句 定义的多行记录。聚合函数对其所作用的每一组记录输 出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果。一些聚合函 数，如 sum, max, min, avg,count 等也可以当作窗口函数使用。

<窗口函数>window_name  over ( [partition by 字段...]  [order by 字段...]  [窗口子句] )

窗口函数一般包括聚合函数、排序函数等

count(col)	统计指定列的总的个数
sum(col)	针对指定列进行求和
avg(col)	针对指定列进行求平均
max(col)	获取指定列的最大值
min(col)	获取指定列的最小值

例子1 数据准备 userorder.txt

wangsan,2024-08-08,100
tom,2024-08-02,150
wangsan,2024-02-03,230
tom,2024-08-04,290
wangsan,2024-08-05,416
wangsan,2024-04-06,412
tom,2024-08-07,510
wangsan,2024-08-08,155
lilei,2024-04-08,162
lilei,2024-04-09,168
lucy,2024-05-10,112
lilei,2024-04-11,175
lucy,2024-06-12,180
lilei,2024-04-13,194

建表语句：

create table orders(name string,orderdate string,money double)row format delimited fields terminated by ',';

加载数据：

load data local inpath '/userorder.txt' into table orders;

 

1：查询2024年8月份消费的顾客名单以及2024年8月份产生的总的消费人次

select distinct name,count(*) over() from orders where year(orderdate) = 2024 and month(orderdate) =8;

2：获取每一个客户的消费明细以及到当前日期为止的累计消费

select *,sum(money) over(partition by name order by orderdate rows between unbounded preceding and current row) from orders;

unbounded：无边界 unbounded preceding：从第一行开始 unbounded following：到最后一行结束 current row：当前行

3,获取每一个顾客的消费明细以及每一位顾客的月度消费总额

select *,sum(money) over(partition by name,month(orderdate)) from orders;

4.获取最早的20%的顾客的消费名单 - 首先需要先将数据按照消费日期来进行排序，其次需要将按照排序之后的结果将数据放到5个桶中，获取第一个桶的数据

select * from (select *, ntile(5) over(order by orderdate) as bucket_id from orders)tmp where bucket_id = 1;

5.查询客户的消费明细以及每一次消费之后上一次的消费时间

select *, lag(orderdate, 1) over(partition by name order by orderdate) from orders;

例子2

 数据：score.txt

tom 语文 80
lilei 数学 90
kesou 英语 60
lucy 语文 90
lucy 数学 50
lucy 英语 80
xiaoming 语文 30
xiaoming 数学 82
xiaoming 英语 81
zhaoling 语文 64
zhaoling 数学 82
zhaoling 英语 72

建表语句：

create table score (name string,kemu string,fenshu int) row format delimited fields terminated by '\t';

加载数据：

load data local inpath '/score.txt' into table score;

1.按学科对每一个学生的成绩进行降序排序

顺次排序

select *, row_number() over(partition by kemu order by fenshu desc) from score;

并列排序

select *, rank() over(partition by kemu order by fenshu desc) from score;

非空位并列排序

select *, dense_rank() over(partition by kemu order by fenshu desc) from score;

dense_rank()

在数据排序之后会对数据进行自增的编号，如果值相同，那么编号会重复且不产生空位

2.按学科获取每一个学科考试成绩前四名的学生

select * from (select *, rank() over(partition by kemu order by fenshu desc) as orderid from score)tmp where orderid <= 4;

rank()    在数据排序之后会对数据进行自增的编号，如果值相同，那么编号会重复且产生空位