Hive常用函数介绍(窗口函数)

 

1.窗口函数介绍

窗口函数，也叫也叫OLAP函数（Online Anallytical Processing，联机分析处理），可对数据库数据进行实时分析处理。

2.窗口函数入门

2.1 函数说明

窗口函数，也可以叫做开窗函数，其从本质来看是将 MySQL 中一些复杂的查询封装成了窗口的形式，进行数据统计时使用并且操 作十分方便，窗口函数为统计时使用的聚合函数指定一个聚合的范围。

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

2.2 函数使用

 源数据：

+----------------+---------------------+----------------+
| business.name  | business.orderdate  | business.cost  |
+----------------+---------------------+----------------+
| jack           | 2021-07-07          | 110            |
| tony           | 2021-07-08          | 115            |
| jack           | 2021-08-03          | 123            |
| tony           | 2021-07-04          | 129            |
| jack           | 2021-07-05          | 146            |
| jack           | 2021-10-06          | 142            |
| tony           | 2021-07-07          | 150            |
| jack           | 2021-07-08          | 155            |
| mart           | 2021-10-08          | 162            |
| mart           | 2021-10-09          | 168            |
| neil           | 2021-11-10          | 112            |
| mart           | 2021-10-11          | 175            |
| neil           | 2021-12-12          | 180            |
| mart           | 2021-10-13          | 194            |
+----------------+---------------------+----------------+

计算每个人消费总额，并且按照名字进行分组

 select name,cost,
 sum(cost) over(partition by name) 
 from business;

 

 

可以看到，这里有4个人，所以开了4个窗口，并且每个窗口都统计了单个人的总消费金额

了解了基本的用途之后，我们开始来使用几个基础的函数

• CURRENT ROW：当前行
• n PRECEDING：往前 n 行数
• n FOLLOWING：往后 n 行数据
• UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点
• UNBOUNDED FOLLOWING：表示到后面的终点

我将结合案例进行演示，以便更好的理解

案例1：求当前日期的消费总额以及截至当前日期的总消费总额。

思路讲解：利用开窗函数的机制，我们可以在求和的时候进行开窗统计，首先根据日期进行排序，并将上一行的值与本行相加，便可以得出当前日期的总消费金额，以此类推。

 select name,orderdate,cost,
 sum(cost) over(order by orderdate 
 range between  unbounded preceding and current row)   
 from business;

 

3.窗口函数使用

3.1取值函数

first_value()：返回分组类第一个值
last_value()：返回分组类最后一个值

 select name,cost,
 first_value(cost) over() `first`,
 last_value(cost) over() `last`
 from business;

  nth_value()：返回分组内第N行

 select name,cost,
 rank() over() `id`,
 nth_value(cost,2) over() `nth2`,
 nth_value(cost,4) over() `nth4`,
 nth_value(cost,6) over() `nth6` 
 from business;

lag()：从当前行开始往前去第N行，默认为NULL
lead()：从当前行开始往后去第N行，默认为NULL

需求：查看每个人上次以及下次的购买时间

 select name,cost,
 lag(orderdate,1,null) over(partition by name order by orderdate) `lag`,
 lead(orderdate,1,null) over(partition by name order by orderdate) `lead` 
 from business;

 ntile()：用于将分组数据按照顺序切分成n片

select
name,cost,orderdate,
ntile(5) over(order by orderdate) sorted
from
business;

 percent_rank()：统计当前行在整个有序窗口中占的百分比数，返回0到1之间变化

select
name,cost,orderdate,
percent_rank() over(order by orderdate)
from
business;

 

3.2 排序函数

rank()：有重复值时候间断排序，例如 1，2，2，4，4，6
dense_rank()：有重复值时候不间断排序，例如 1，2，2，3，3，4
row_number()：不间断排序，有重复值时，会根据内置算法进行排序，1，2，3，4

张三	20
李四	27
王五	29
赵六	22
田七	26
马八	24
黄九	23
罗十	28
郑三	27
吴四	20
高五	25
薛六	22
楚七	22
朱八	27

用三种方法对年纪进行排序

select
name,age,
rank() over(order by age) `rank`,
dense_rank() over(order by age) `dense_rank()`,
row_number() over(order by age) `row_number` 
from stu;

3.3 聚合函数 

sum()：求和
avg()：平均值
max()：最大值
min()：最小值
count()：计数统计

select name,age,
sum(age) over()`sum`,
avg(age) over()`avg`,
max(age) over()`max`,
min(age) over()`min`,
count() over()`count`
from stu;

 

hive中关于窗口函数的讲解就介绍这么多，希望各位看客多多支持