玖叶教程网

我们在日常工作中是否经常会遇到需要排名的情况，比如：每个部门按业绩来排名，每人按绩效排名，对部门销售业绩前N名的进行奖励等。面对这类需求，我们就需要使用sql的高级功能——窗口函数。

一、什么是窗口函数？

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

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

<窗口函数> over （partition by <用于分组的列名>
                    order by <用于排序的列名>）
-- over关键字用于指定函数的窗口范围，
-- partition by 用于对表分组，
-- order by子句用于对分组后的结果进行排序。

<窗口函数>的位置，可以放以下两种函数：

1. 专用窗口函数，包括rank, dense_rank, row_number等。
2. 聚合函数，如sum. avg, count, max, min等支持窗口函数的查询元素

需要注意的是并不是所有的查询子句都支持窗口函数，先看运行顺序，SELECT和ORDER BY 子句直接支持窗口函数。做这个限制的原因是为了避免二义性，因此把查询的最终结果当作窗口的起点。如果窗口函数可以早于SELECT阶段出现，那么通过一些查询表单会无法得到正确的结果,所以窗口函数原则上只能写在select子句中。

二、如何使用窗口函数

group by和partiton by的区别：

group by分组汇总后改变了表的行数。

partiition by分组汇总后不会改变原表中的行数。例如

窗口函数的功能：

1）同时具有分组和排序的功能

2）不减少原表的行数

注意事项：partition by子句可以省略，省略就是不指定分组

三、专用窗口函数

排序语句rank、desnse_rank、row_number的区别

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 班级表

rank函数：如果有并列名次，会占用下一名次的位置，如正常排名是1，2，3，4，5但是现在第3名和第4名是并列的名次，结果是1，2，3，3，5。

dense_rank函数：如果有并列名次的行，不会占用下一名次的位置，如正常排名是1，2，3，4，5，但是现在第3名和第4名是并列的名次，结果是1，2，3，3，4。

row_number函数：不考虑并列名次的情况，正常排名1，2，3，4，5。

注意事项：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持( )空着就可以。

例如：现有一张销售订单表，想知道各渠道订单数前2个客户信息

步骤一：按渠道分组(partiotion by 渠道)，并按订单数降序排列(order by 订单数 desc)，套入窗口函数的语法，就是下面的sql语句：

select *,
     row_number() over(partition by 渠道
                       order by 订单数 desc) as '排名'
from 订单表;

运行结果如下：

步骤二：筛选出订单数的前2名，所以我们在上一步基础上加入一个where字句来筛选出符合条件的数据。（where 排名 <=2）

SELECT *
from (
  SELECT*,row_number() over(partition by 渠道 order by 订单数 desc)as '排名'
from 订单表) as a
WHERE 排名<=2;

经典TopN问题sql模板：

 select * from (
 select *,     
       row_number() over 
       (partition by 要分组的列名 
       order by 要排序的列名 desc) as ranking
       from 表名) as a
 where ranking ?= N;

四、聚合函数作为窗口函数

sum、avg、count、max、min

聚合窗口函数和专用窗口函数用法完全相同，只需把聚合函数写在窗口函数的位置即可，但函数后面括号里面不能为空，需要指定聚合的列名。

select *,
        sum(成绩) over (order by 学号) as current_sum,
        avg(成绩) over (order by 学号) as current_avg,
        count(成绩) over (order by 学号) as current_count,
        max(成绩) over (order by 学号) as current_max,
        min(成绩) over (order by 学号) as current_min
from 班级表;

得到结果：

求和、平均、计数、最大最小值，都是针对累计到当前列的所有数据进行计算。

• 这样使用聚合函数的作用

聚合函数作为窗口函数，可以在每一行的数据里直观地看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

例如：还是上面的订单表，现在要统计累计一共有多少订单

SELECT 客户编号,订单数,sum(订单数)
over(order by 客户编号) as 累计订单数
from 订单表

“累计求和”问题的万能模板是：

select 列1,列2,
sum(列名) over (
order by ?用于排序的列名?) as 累计值的别名
from 表名;