SQL开窗函数

概述

需求背景：

有时一组数据只返回一组值不能满足需求，如经常想知道各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。

2003 年 ISO SQL 标准加入开窗函数，目前在 MS SQLServer、Oracle、DB2等主流数据库中都提供对开窗函数的支持，不过MySQL暂时还未对开窗函数给予支持。

简介：与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。在 ISO SQL 规定这样的函数为开窗函数，在 Oracle 中则被称为分析函数。

开窗函数有两类：一类是聚合开窗函数，一类是排序开窗函数

窗函数和聚合函数的区别：

1. SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。
2. 聚合函数每组只返回一个值，开窗函数每组可返回多个值。

开窗函数在聚合函数后增加一个 OVER 关键字。开窗函数格式：​

​函数名(列) OVER(选项)​

​​ OVER关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。

开窗函数​

​COUNT(*) OVER()​

​对于查询结果的每一行都返回所有符合条件的行的条数。OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

PARTITION BY 子句：

开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区独立于结果集，创建的分区只是供进行聚合计算的，且不同的开窗函数所创建的分区也不互相影响。

显示每一个人员的信息以及所属城市的人员数：

​​

​select name, city, age, salary, count(*) over(partition by city) from person​

​ 在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干扰

ORDER BY子句：

开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。

ORDER BY子句的语法为：

​

​ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2​

​ RANGE表示按照值的范围进行范围的定义，ROWS表示按照行的范围进行范围的定义；

边界规则的可取值：

​

​RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2​

​部分用来定位聚合计算范围，这个子句又被称为定位框架。

​

​RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW​

​是开窗函数中最常使用的定位框架，为了简化使用，如果使用的是这种定位框架，则可以省略定位框架声明部分，

高级

在开窗函数中可使用COUNT()、SUM()、MIN()、MAX()、AVG()等聚合函数，还可以在开窗函数中使用一些高级的函数，有些函数同时被DB2和Oracle同时支持，比如RANK()、DENSE_RANK()、ROW_NUMBER()，而有些函数只被Oracle支持，比如RATIO_TO_REPORT()、NTILE()、LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()。

RANK()和DENSE_RANK()函数都可以用于计算一行的排名，不过对于并列排名的处理方式不同；ROW_NUMBER()函数计算一行在结果集中的行号，同样可以将其当成排名函数。这三个

函数的功能存在一定的差异。

SELECT name, salary, age, 
RANK() OVER(ORDER BY salary desc) RANK,
DENSE_RANK() OVER(ORDER BY salary desc) DENSE_RANK,
ROW_NUMBER() OVER(ORDER BY salary desc) ROW_NUMBER
FROM person;      

参考