sql开窗函数的使用方法（打开天窗说亮话）

经常写SQL脚本的朋友，通常会有一种迷之自信，似乎各种问题都有自己的一套解决方案。时间长了，人的思维可能会逐渐固化。思维固化能提高工作效率，但从某些角度看是很可怕的，我们也同时会失去接受新知识的内在动力。

从一个简单问题开始，数据库SQL应用最简单的是什么？很多朋友会脱口而出：当然是查询啦，select * from 表，多简单啊。如果我再问您，SQL最难搞的是什么？您可能会陷入沉思，一幕幕曾经的彻夜查资料痛苦尝试的经历一一浮现眼前。

有丰富经验的朋友，最可能的回答应该还是：查询最难搞！没错，SQL最简单的应用是查询、最难搞的应用也是查询。为什么会这样？！道理非常简单，因为查询语句虽然起点很低、但查询的需求非常多样化、查询的语法也非常复杂！

SQL开窗函数，主流数据库都支持的一朵奇葩

知识和技术总是要一点一点消化和积累的。今天，我们就谈谈查询中非常另类的一条语法：select子句的over函数。over函数在MySQL和SQL Server中被称为开窗函数、在PostgreSQL中被称为窗口函数、在Oracle中被称为分析函数、在DB2中被称为OLAP函数。

看来over函数虽然名字不同，但在主流数据库中都是支持的。这也不奇怪，这本来就是SQL标准中定义的函数。over函数在标准SQL中定义就叫开窗函数。不同数据库中虽然叫法不同，但语法格式和功能效果基本是一致的。

那开窗函数到底是什么呢？

复杂的定义我们就不说了，简单来讲，开窗函数类似于聚合函数（group by），主要实现数据的分组统计，为统计行打开一扇另类的统计窗口。over在某些情况下可以完全替代group by，但大多情况下，over比group by更灵活、更强大。

更进一步来对比：group by是针对整个查询设置统一的分组字段，所有返回的分组统计必须以group by指定的分组条件为基准；而over则灵活多了，over 不需要基于分组字段进行统计，可以各自灵活指定分组依据。这意味着什么，相信您很容易就能想明白。

今天我们就以SQL Server为例，详细讲解一下如何具体应用开窗函数。

从最常见的count(*)引出开窗函数应用

了解一点数据库SQL的朋友应该知道，SQL中有一个count函数，这个函数是做什么用的？我不用说大家肯定都清楚，就是统计记录条数的呗！

没错，count函数确实是用来统计记录条数的，如果count函数的作用仅仅是这些的话，我就没必要提她了。简单的count函数，却引出了非常神奇的用途，怎么个神奇法呢？且听我缓缓道来。

先来点老套路，我们先看看我们都知道的count函数的语法格式：

select count(*、字段、表达式) from 表

这就是我们都知道的count的基本用法，确实非常简单，似乎没什么好说的。

其实，count除了统计记录条数之外，还有一个很神奇的功能，如果把count函数配合over开窗函数，就可以实现非常神奇的分组计数功能；我们熟知的sum函数再配合over函数，还可以实现分组汇总功能。这些统计功能在很多情况下，甚至比group by更简单、更好用。

演示数据准备

为了演示方便，我们先准备一套演示数据，创建一个表变量，存放不同区域、城市、年月的销售金额。脚本如下：

declare @sale table( FName nvarchar(50), FDistrict nvarchar(50), FYear smallint, FMonth smallint, FAmount decimal(28,10) ); insert into @sale values ('张三','北京',2019,4,20000), ('张三','深圳',2019,4,40000), ('李四','北京',2019,4,30000), ('李四','深圳',2019,4,40000), ('王二','北京',2019,4,70000), ('王二','深圳',2019,4,60000), ('马六','北京',2019,4,80000), ('马六','深圳',2019,4,70000);

演示数据运行效果如下图所示：

下面我们就以演示数据为背景讲解下over函数的具体应用。

配合count按字段分组获取记录条数

这个用法与我们所熟知的count的含义类似，也是返回记录条数的，但配合over关键字却可以根据字段进行分组，返回不同分组的统计个数，语法格式如下：

count(*) over(partition by 字段列表)

比如我们想获得按照业务员名称返回记录条数，可以这样写：

select FName,FDistrict,count(*) over(partition by FName) as FCount from @sale;

运行效果如下图所示：

如上图所示，每一行返回的FCount字段的值，就是不同FName的个数统计，这里都显示2，是因为刚好每个业务员都有2条记录。

往常我们写SQL脚本做如此处理时，最常用的写法就是通过group by，但这里用count却更简单，您说不是嘛。

本节我们引出了over关键字，下面我们就引申一下，体验一回over关键字更神奇的用途：使用over关键字配合sum进行分组合计。

配合sum按字段进行分组合计

我们都知道，惯常来说分组统计要用group by，针对分组字段使用sum函数统计。其实还有一种另类的实现方式，sum函数和over函数配合使用，完全不用group by，却能实现比使用group by更加灵活的统计实现。

我们先看看实现的语法格式：

sum(合计字段) over(partition by 分组字段列表)

下面我们就实战一下各种分组汇总方式，代码如下：

select FName, FDistrict, sum(FAmount) over() as F整体, sum(FAmount) over(partition by FDistrict) as F按区域, sum(FAmount) over(partition by FName) as F按业务员, sum(FAmount) over(partition by FName,FDistrict) as F区域业务员 from @sale;

运行效果如下：

您发现了没有，我们并没有使用group by子句，前两个字段FName和FDistrict自动成为分组字段，这种分组只是决定了分组的每一行，而真正的统计标准，则是按照over关键字指定的分组依据对sum指定的字段进行汇总。

这确实比Group By这种聚合函数要好用多了，我们可以随意指定想要的分组依据，宝宝们再也不用为了实现不同分组汇总数据写一堆子查询或公用表表达式气得跳脚了。

配合排名函数让您的查询飞的更精彩

看过我上一篇文章（《善用SQL排名函数，让您的查询飞的更精彩》）的朋友，您肯定已经领教过了天窗函数的神奇应用，怎么个神奇法，具体您可以看看文中所述，这里我只做简单的讲述。

SQL有四大排名函数，分别为：ROW_NUMBER、RANK、DENSE_RANK、NTITLE。

ROW_NUMBER：主要实现最基本的序列化排名，返回的是行号，也可以用来分页查询。

其语法格式为：

row_number() over(order by field列表 asc|desc)

RANK：主要实现分组序列化排名，返回的组号。其语法格式为：

rank() over(order by field列表 asc|desc)

DENSE_RANK：与RANK类似，唯一的区别是，RANK的组号是基于行号的，相同值共用组号，碰到下一组不相同的，会跳过直接使用对应的行号做组号；DENSE_RANK则只考虑组号的连续性，不考虑行号。

其语法格式为：

dense_rank() over(order by field列表 asc|desc)

NTITLE：比较另类的功能，主要实现在排序的前提下，根据要求分多少组、再对查询结果分组。其逻辑简单说是将所有记录排序后，按照记录条数和组数要求尽量均分，返回的则是连续组号。如果能够均分则均分，如果不能均分，则优先多分前面的组，后续组再按照均分原则依次类推。

其语法格式为：

ntile(组数) over(order by field列表 asc|desc)

您明显能看到，多了一个“组数”参数，就是用来指定对查询结果分多少组用的。

over天窗函数配合排名函数的应用，我这里就不举例了，具体您可以参考《善用SQL排名函数，让您的查询飞的更精彩》。

---

今天讲的比较多，下面我们把知识点总结一下：

1、计数分组：count(*) over(partition by 分组字段列表)

2、汇总分组：sum(字段) over(partition by 分组字段列表)

3、ROW_NUMBER排名：row_number over(order by 排序字段列表 asc|desc)

4、RANK分组排名：rank over(order by 排序字段列表 asc|desc)

5、DENSE_RANK分组排名：dense_rank over(order by 排序字段列表 asc|desc)

6、NTITLE分组排名：ntitle(组数) over(order by 排序字段列表 asc|desc)

---

文中我多次提到“另类”二字，说实话开窗函数一点也不另类，另类的可能是我们的思维早已经被Group By固化了，没留意或没深入了解更神奇的over函数而已，您说呢？

怎么样？有没有觉得很神奇？！希望对您有所帮助！

,