SQL-窗口函数大全（窗口函数应用详解）

一、什么是窗口函数

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

语法大致如下

<窗口函数>over(partition by 分组字段 order by 排序字段)--分组和排序字段不是必须项，视问题情况而定

主要分为3类，分别是聚合窗口函数、排序窗口函数、偏移窗口函数

    聚合窗口函数是avg、sum、count、max、min等；

    排序窗口函数是rank、dense_rank、row_number；

    偏移窗口函数是lag、lead

聚合和排序窗口函数应用比较多，需要着重掌握，偏移窗口函数虽然应用场景不多，但建议还是至少明白是做什么的，什么场景下能用。

二、窗口函数有什么用

最主要的作用是在不减少原表行数的情况下进行分组排序等计算，主要应用场景有

组内比较问题

组内topN问题

累计求和问题

连续登录、活跃N天问题

连续出现N次问题

三、窗口函数怎么用

窗口函数的语法介绍

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

我们经常使用的 avg()、sum()、max()、min()是分析函数， 而 over()才是窗口函数，看一下over()窗口函数的语法结构:

<窗口函数> over (partition by <用于分组的列名>
               order by <用于排序的列名>                
               rows between 开始位置 and 结束位置)

over()函数中包括三个函数：包括

分区 partition by 列名

排序 order by 列名   

指定窗口范围 rows between 开始位置 and 结束位置

我们在使用 over()窗口函数时，over()函数中的这三个函数可组合使用也可以不使用。

over()函数中如果不使用这三个函数，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据。

2.over()函数中的三个函数讲解

order by ：

order by 是排序的意思，是该窗口中的排序

partition by：

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

rows between 开始位置 and 结束位置。

    是指定窗口范围，比如第一行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and 结束位置)搭配分析函数时，分析函数按照这个范围进行计算的。

3. 窗口范围使用说明：

不指定窗口范围时，是指窗口内所有的行。指定窗口范围，常用于累计求和，移动平均/滚动求和等。

窗口范围常用：

preceding ：往前     
    n preceding 表示自身记录往前n行     
    unbounded preceding 表示该窗口最前面的行（起点）
following：往后
current row：当前行
unbounded：意思无限的 （一般结合 preceding，following 使用）    
    unbounded preceding 表示该窗口最前面的行（起点）    
    unbounded following：表示该窗口最后面的行（终点）

1).我们常使用的窗口范围是rows between unbounded preceding and current row（表示从起点到当前行），常用该窗口来计算累加/累计求均值。

##第二大部分会用实例演示如何使用。##
sum(字段1) over(partition by 字段2 order by 字段3 rows between unbounded preceding and current row) as 新字段名
avg(字段1) over(partition by 字段2 order by 字段3 rows between unbounded preceding and current row) as 新字段名

2).常用窗口函数范围rows n preceding(表示自身记录及前n行)，求移动平均值或滚动求和

avg(字段1) over （order by 字段2 rows n preceding）
sum(字段1) over （order by 字段2 rows n preceding）
-------
###其中 rows和preceding这两个关键字表示的意思为“之前...行”，
rows n preceding表示为自身记录及前n行
第二大部分会用实例演示如何使用###

窗口函数常用场景及应用实现

1.常与 over()一起使用的分析函数，使用场景及实践

专用窗口函数，包括rank,dense_rank,row_number等，实现窗口范围内排序。

聚合函数，如sum,avg,count,max,min,first_value,last_value等。实现窗口范围内总和/平均值/计数/最大值/最小值/第一个值/最后一个值的查询。

窗口函数实现累加求功能,累计求平均值功能

窗口函数的平均移动.求移动平均值，滚动求和

窗口函数偏移函数。组内每行，取前边第n行的或后边第N行的值。lag() over() 与 lead() over() 函数是跟偏移量相关的两个分析函数.

为了方便查看计算后的结果，以如下班级表中的模拟数据做场景演示。如有雷同纯属巧合。

select *
from 班级表 order by 学号 asc

2.应用实现及效果演示

2.1 专用窗口函数，包括rank,dense_rank,row_number等，实现窗口范围内排序

rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4

2.1.1 在不改变原表行数的情况下，对全部数据实现排序

#对全部数据，按照成绩进行排序#
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.1.2在不改变原表行数的情况下，对各分区内数据实现组内排序

#按照班级分区，并对组内数据，按照成绩进行排序##
select *,
   rank() over (partition by 班级 order by 成绩 desc) as ranking,
   dense_rank() over (partition by 班级 order by 成绩 desc) as dese_rank,
   row_number() over (partition by 班级 order by 成绩 desc) as row_num
from 班级表

得到结果：

2.2 聚合函数，如

sum,avg,count,max,min,first_value(),last_value() 等

sum(字段1) over () 求和avg(字段1) over () 求均值count(字段1) over () 计数max() over () 求最大值min() over () 求最小值

first_value()  over():求分组第一条.其中用row_number() over()取编号第一条的也可以实现first_value() over()的效果

last_value()  over():求分组最后一条

2.2.1 对全部数据进行聚合计算，及对全部数据排序后进行聚合计算

select *,
   rank() over (order by 学号) as ranking,
   #####对全部行数据，进行聚合计算####
   sum(成绩) over () as current_sum1,
   avg(成绩) over () as current_avg1,
   #####对全部数据，按照学号排序，并进行聚合计算####
   sum(成绩) over (order by 学号) as current_sum2,
   avg(成绩) over (order by 学号) as current_avg1,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min,
     first_value(成绩) over (order by 学号) as current_first_value,
     last_value(成绩) over (order by 学号) as current_last_value
from 班级表

得到结果：

2.2.2 对数据进行分区，组内不排序对组内数据进行聚合计算。

select *,
###分组，并对组内进行聚合计算##
   sum(成绩) over (partition by 班级) as current_sum,
   avg(成绩) over (partition by 班级 ) as current_avg,
   count(成绩) over (partition by 班级 ) as current_count,
   max(成绩) over (partition by 班级 ) as current_max,
   min(成绩) over (partition by 班级 ) as current_min,
     first_value(成绩) over (partition by 班级 ) as current_first_value,
     last_value(成绩) over (partition by 班级 ) as current_last_value,
###分组，并对组内排序后进行聚合计算##
    sum(成绩) over (partition by 班级 order by 成绩) as current_sum2,
   avg(成绩) over (partition by 班级 order by 成绩) as current_avg2
from 班级表

得到结果：

2.2.2 对数据进行分区，组内排序后对组内数据进行聚合计算。

###分组，对组内排序后进行聚合计算##
#按照班级分组，并且组内按照成绩从小到大排列。组内当前求和，组内当前求均值，组内当前计数，组内求当前最大值，组内求当前最小值，组内求当前第一个值，组内求当前最后一个值#
---------------
select *,
   sum(成绩) over (partition by 班级 order by 成绩) as current_sum,
   avg(成绩) over (partition by 班级 order by 成绩) as current_avg,
   count(成绩) over (partition by 班级 order by 成绩) as current_count,
   max(成绩) over (partition by 班级 order by 成绩) as current_max,
   min(成绩) over (partition by 班级 order by 成绩) as current_min,
     first_value(成绩) over (partition by 班级 order by 成绩) as current_first_value,
     last_value(成绩) over (partition by 班级 order by 成绩) as current_last_value
from 班级表

得到结果：

2.3 窗口函数实现累加求和功能,累计求平均值功能

常使用的窗口范围是rows between unbounded preceding and current row（表示从起点到当前行），常用该窗口来计算累加/累计求均值。

sum(字段1) over(partition by 字段2 order by 字段3 rows between unbounded preceding and current row) as 新字段名
avg(字段1) over(partition by 字段2 order by 字段3 rows between unbounded preceding and current row) as 新字段名
---------------解释：------
sum(字段1) 的求和是针对后面over()窗口的求和，
over中partition by 字段2 order by 字段3， 是针对字段2分组按照字段3排序，
unbounded意思无限的 preceding在之前的，current row当前行。
限定了行是按照在当前行不限定的往前处理，通俗就是处理当前以及之前的所有行求sum/avg

2.3.1对数据进行分组，并进行排序，限定行数截止到当前行

#按照班级分组，并且组内按照成绩从小到大排列。##
###组内累计求和，求均值，计数，组内求当前最大值，##
###组内求当前最小值，组内求第一个值，组内求当前最后一个值#
select *,
   sum(成绩) over (partition by 班级 order by 成绩 rows between unbounded preceding and current row) as current_sum,
   avg(成绩) over (partition by 班级 order by 成绩 rows between unbounded preceding and current row) as current_avg,
   count(成绩) over (partition by 班级 order by 成绩 rows between unbounded preceding and current row) as current_count,
   max(成绩) over (partition by 班级 order by 成绩 rows between unbounded preceding and current row) as current_max,
   min(成绩) over (partition by 班级 order by 成绩 rows between unbounded preceding and current row) as current_min,
   first_value(成绩) over (partition by 班级 order by 成绩 rows between unbounded preceding and current row) as current_first_value,
   last_value(成绩) over (partition by 班级 order by 成绩 rows between unbounded preceding and current row) as current_last_value
from 班级表

得到结果：

2.3.2 对数据不分组，对全部数据排序，限定行数截止到当前行

##备注：此种用法不常用，仅用于加深语法理解。##
##此时,sum(成绩) over ( orderby 成绩 rows betweenunboundedprecedingandcurrentrow)##
##等同于sum(成绩) over ( orderby 成绩 )###
##对全部数据按成绩从小到大排列。
##累计求和，求均值，计数，求当前最大值，##
###求当前最小值，求第一个值，求当前最后一个值#
select *,
   sum(成绩) over ( order by 成绩 rows between unbounded preceding and current row) as current_sum,
   avg(成绩) over ( order by 成绩 rows between unbounded preceding and current row) as current_avg,
   count(成绩) over (order by 成绩 rows between unbounded preceding and current row) as current_count,
   max(成绩) over ( order by 成绩 rows between unbounded preceding and current row) as current_max,
   min(成绩) over (order by 成绩 rows between unbounded preceding and current row) as current_min,
   first_value(成绩) over ( order by 成绩 rows between unbounded preceding and current row) as current_first_value,
   last_value(成绩) over (order by 成绩 rows between unbounded preceding and current row) as current_last_value
from 班级表

2.4 窗口函数的平均移动.求移动平均值，滚动求和

取自身记录及前N行求移动平均，或滚动求和。

可以用rows n preceding，等同于rows between n preceding and current row

avg(字段1) over （order by 字段2 rows n preceding）
sum(字段1) over （order by 字段2 rows n preceding）
其中 rows和preceding这两个关键字表示的意思为“之前...行”，
rows n preceding表示为自身记录及前n行

avg(字段1) over (order by 字段2 rows between n preceding and current row）取自身记录及前N行求平均值。
sum(字段1) over （order by 字段2 rows between n preceding and current row）
其中，rows between n preceding and current row表示从前边一行到当前行

2.4.1  不排序不分组，对全部数据按原有数据求移动平均值或滚动求和

Select *,
avg(成绩) over (rows 1 preceding) as '移动求平均值(前1行与当前行)',
avg(成绩) over ( rows 2 preceding) as '移动求平均值(前2行与当前行)',
sum(成绩) over (rows 1 preceding) as '移动求和(前1行与当前行)',
sum(成绩)  over (rows 2 preceding) as '移动求和(前2行与当前行)'
from 班级表

2.4.1 对全部数据排序，并求移动平均值或滚动求和

#按照全部数据排序，求移动平均值或滚动求和值#
Select *,
avg(成绩) over (order by 学号 rows 1 preceding) as '移动求平均值(前1行与当前行)',
avg(成绩) over (order by 学号 rows 2 preceding) as '移动求平均值(前2行与当前行)',
sum(成绩) over (order by 学号 rows 1 preceding) as '移动求和(前1行与当前行)',
sum(成绩)  over (order by 学号 rows 2 preceding) as '移动求和(前2行与当前行)'
from 班级表

###表示连续n+1个移动平均/移动求和。rows和preceding这两个关键字表示的意思为“之前...行”，上面句子中rows 2 preceding表示为自身记录及前2行的平均或求和。##

得到结果：

2.4.2 对数据分区，组内排序后对组内求移动平均值或滚动求和

#组内排序，并且组内求移动平均值或滚动求和值#
Select *,
avg(成绩) over (partition by 班级 order by 学号 rows 1 preceding) as '移动求平均值(前1行与当前行)',
avg(成绩) over (partition by 班级 order by 学号 rows 2 preceding) as '移动求平均值(前2行与当前行)',
sum(成绩) over (partition by 班级 order by 学号 rows 1 preceding) as '移动求和(前1行与当前行)',
sum(成绩)  over (partition by 班级 order by 学号 rows 2 preceding) as '移动求和(前2行与当前行)'
from 班级表

2.5  偏移函数。取每个数据上一个或下一次的数据。

一般用于查找上一次的购买时间，后一次的购买时间。如获取成绩排名后一位的成绩，学号等信息。

lag() over() 与 lead() over() 函数是跟偏移量相关的两个分析函数.

通过这2个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列，从而更方便的进行数据过滤。这种操作可以代替表的自联接，并且LAG和lead有更高的效率。

lag(字段1，N)表示对over后的窗口取前边第n行的字段1的值。

lead(字段1，N)表示对over后的窗口取后边第n行的字段1的值。

over() 表示 lag() 与 lead() 操作的数据都在 over() 的范围内，他里面可以使用 partition by 语句（用于分组） order by 语句（用于排序）。

partition by a order by b 表示以 a 字段进行分组，再 以 b 字段进行排序，对数据进行查询。

lead(field, num,defaultvalue) ,field需要查找的字段，num标识往后查找的num行数据，defaultvalue没有符合条件的默认值。

2.5.1. 对数据分组，并对组内数据排序，取组内每行数据的前一个或后一个数据

##按照班级分组，并对成绩降序排列，取每名同学排名前边及后边一位的学生成绩##
select *,
lag(成绩,1) over( partition by 班级 order by 成绩  desc) as '成绩排名前一位同学的成绩',
lead(成绩,1) over( partition by 班级 order by 成绩  desc) as '成绩排名后一位同学的成绩',
lag(学号,1) over( partition by 班级 order by 成绩 desc) as '成绩排名前一位同学的学号',
lead(学号,1) over( partition by 班级 order by 成绩  asc) as '成绩排名后一位同学的学号'
from 班级表

2.6  其他类

ntile(n) 把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，ntile 返回此行所属的组的编号

select *,
ntile(3) over( partition by 班级 order by 成绩 asc) as 'ntile'
from 班级表

##ntile(n)表示每个分区中平均分发指定的数字.当n为5时表示，每个分区中平均分发5个编号。对于每一行，ntile 返回此行所属的组的编号##

文章来源：公众号-填充空白 作者：王肖灵Joy