数据库窗口函数

窗口函数也称为OLAP函数。OLAP是online analytical processing的简称，意思是对数据库数据进行实时分析处理。 

窗口函数，也称为分析函数，窗口函数与分组聚合函数类似，但是每一行数据都生成一个结果

Oracle通用的版本已经支持窗口函数

 Mysql8.0支持窗口函数

 

使用方法：配合聚合窗口函数一起使用，例如SUM/AVG/COUNT/MAX/MIN等等

关键字：over     partition by   order  by

 

１）可以作为窗口函数的聚合函数。
SUM ：求和
MIN ：最小值
MAX ：最大值
AVG ：平均值
COUNT ：计数

 

2）专用窗口函数
RANK ：跳跃排序，排序：1，1，3
DENSE_RANK ：连续排序，排序：1，1，2
ROW_NUMBER：没有重复值的排序，排序：1，2，3
FIRST_VALUE ：返回组中数据窗口的第一个值
LAST_VALUE ：返回组中数据窗口的最后一个值。
LAG ：LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值。
LEAD ：LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值。

 

窗口函数：

聚合函数 over()

聚合函数 over(partition by 字段）                     --分区

聚合函数 over(order by 字段)                      --框架字句

 

2、演示

例如我们现在有这样一张数据表

原始数据表

我现在想求每个国家profit的总和，常用的做法是使用聚合函数sum

 

但是问题来了，我现在想求sum的同时，展示出其他字段的信息比如year，product等，如果是正常的sql需要进行一些额外的操作，但是有了窗口函数一切就变得不一样了，如下图，飒~

 

这里的关键字是over，partition by 是按照某个字段分区的意思，可以理解为分组 group by

partition by如果不写代表使用整个数据集分区，相当于没有group by

 

二、专用窗口函数

1、首先简单列举一些常用的 专用窗口函数

获取数据排名的：ROW_NUMBER()      RAND()     DEBSE_RANK()       PERCENT_RANK()

获取分组内的第一名或者最后一名等 FIRST_VALUE()     LAST_VALUE()         LEAD()            LAG()

累计分布：CUME_DIST()          NTH_VALUE()               NTILE()

2、演示

数据列表

现在我们想求出val的排名，如下图

row_number

这个函数有什么用处呢？现在我来说一个经典的面试场景，求所有学生中每门功课成绩的前三名，那这个时候我们就可以是使用窗口函数来解决，sql如下(脑补表结构)

select * from

(select row_number() over (partition by lesson order by score desc) 排名,* from sc) t

where 排名<=3 

在上述sql中 我们使用lesson分组，并且使用分数降序，这是每个学生会有一个排名，然后小于等于三即可。

 

窗口函数第二篇：https://www.cnblogs.com/thomasbc/p/15132711.html

 

 

 

 

 

 

原文：https://www.jianshu.com/p/b7b5f5045bf6