菜鸟笔记
提升您的技术认知

窗口函数

一、窗口函数

窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。

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

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

其中<窗口函数>的位置,可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2) 聚合函数,如sum. avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中。
partition by和group by的区别:
partition by统计的每一条记录都存在,而group by将所有的记录汇总成一条记录
简单来说,窗口函数有以下功能:
1)同时具有分组和排序的功能
2)不减少原表的行数

二、hive窗口函数

avg()、sum()、max()、min()是分析函数,而over()才是窗口函数

1.over()窗口函数的语法结构

分析函数 over(partition by 列名 order by 列名 
			rows between 开始位置 and 结束位置)

over()函数中包括三个函数:包括分区partition by 列名(作用和group by类似)、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置。我们在使用over()窗口函数时,over()函数中的这三个函数可组合使用也可以不使用。
over()函数中如果不使用这三个函数,窗口大小是针对查询产生的所有数据,如果指定了分区,窗口大小是针对每个分区的数据。

1.1 over()函数中的三个函数讲解

1.order by

排序

2.partition by

partition by可理解为group by 分组。over(partition by 列名)搭配分析函数时,分析函数按照每一组每一组的数据进行计算。

3.rows between 开始位置 and 结束位置

指定窗口范围,比如第一行到当前行。而这个范围是随着数据变化的。
over(rows between 开始位置 and 结束位置)搭配分析函数时,分析函数按照这个范围进行计算的。
窗口范围说明:
我们常使用的窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(表示从起点到当前行),常用该窗口来计算累加。
如:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(表示从起点到当前行)
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING(表示往前2行到往后1行)
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW(表示往前2行到当前行)
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING(表示当前行到终点)

PRECEDING:往前
FOLLOWING:往后
CURRENT ROW:当前行
UNBOUNDED:起点(一般结合PRECEDING,FOLLOWING使用)
UNBOUNDED PRECEDING 表示该窗口最前面的行(起点)
UNBOUNDED FOLLOWING:表示该窗口最后面的行(终点)

2.常与over()一起使用的分析函数

2.1 聚合类

avg()、sum()、max()、min()

2.2 排名类

row_number()按照值排序时产生一个自增编号,不会重复(如:1、2、3、4、5、6)
rank() 按照值排序时产生一个自增编号,值相等时会重复,会产生空位(如:1、2、3、3、3、6)
dense_rank() 按照值排序时产生一个自增编号,值相等时会重复,不会产生空位(如:1、2、3、3、3、4)
如:

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

2.3 其他

lag(列名,往前的行数,[行数为null时的默认值,不指定为null])可以计算用户上次购买时间
lead(列名,往后的行数,[行数为null时的默认值,不指定为null]),可以计算用户下次购买时间。
ntile(n)把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号
ntile(3)拿出1/3的数据