Sql server 开窗函数over()的语法

转载最新推荐文章于 2026-03-30 02:33:06 发布 4.9k 阅读 AI写同款

收录于

SqlServer数据库

.NET跨平台

码龄15年墨衍会员 · AI 创作全网分发

6066粉丝738篇原创

关注

SQL中的开窗函数一个例子，一个业务表，有字段客户名、操作部门和业务量，一个客户会有多个操作部门，每个操作部门不同的业务量，同一个客户的有些部门的业务量可能相同，现在想取出每个客户唯一的操作部门，按照操作部门业务量最多的选取，业务量相同的话取排在最前面的部门。以上代码将对每个分区内的数据进行排序，并计算每个分区内的后面所有行的累计和。OVER()子句中的ROWS参数ROWS参数是OVER子句中用于定义窗口范围的一个重要参数，它决定了开窗函数计算时所考虑的行的范围，熟练使用可以达成意想不到的效果。阅读详情

AI摘要

本文介绍了SQL Server中的开窗函数，包括其基本概念、使用方法及应用场景。通过实例演示了如何利用开窗函数进行数据排序、分组统计以及复杂的聚合计算。

用法一：与ROW_NUMBER()函数结合用，给结果进行排序编号，如图：
在这里插入图片描述

代码如下：

SELECT ROW_NUMBER() over(order by RequiredDate) num ,* from  [Northwind].[dbo].[Orders]

用法二：跟聚合函数一起使用，利用over子句的分组效率比group by子句的效率更高。

在Northwind数据库的订单表Orders中查询"订单id",“客户id”,“运费”,“所有订单的总数”，“每一个客户的总运费”，“所有客户的总运费”，“每一个客户的平均运费”，“所有客户的平均运费”,“每一个客户所有订单中最大的运费”,“所有客户中最大运费”,“每一个客户所有订单中最小的运费”,“所有客户中最小运费”,如下图:
在这里插入图片描述
代码如下：

  SELECT  [OrderID] --订单id
      ,[CustomerID] --客户id
      ,[Freight] --运费
      ,COUNT(OrderID) over() as totalNum --一所有订单的总数
      ,SUM(Freight) over(partition by customerid) as cusTotalFreight --每一个客户的总运费
      ,SUM(Freight) over() as totalFreight --所有客户的总运费
      ,AVG(Freight) over(partition by customerid) as cusAvgFreight --每一个客户的平均运费
      ,AVG(Freight) over() as avgFreight --所有客户的平均运费
      ,MAX(Freight) over(partition by customerid) as cusMaxFreight --每一个客户所有订单中最大的运费
      ,MAX(Freight) over() as maxFreight --所有客户中最大运费
      ,MIN(Freight) over(partition by customerid) as cusMinFreight --每一个客户所有订单中最小的运费
      ,MIN(Freight) over() as minFreight --所有客户中最小运费
  FROM [Northwind].[dbo].[Orders]

总结：运用开窗函数over()再结合聚合函数，可以使表格向右扩张，并进行一些数据的统计

sql `over`开窗函数

1.使用over子句与rows_number()以及聚合函数进行使用，可以进行编号以及各种操作。而且利用over子句的分组效率比group by子句的效率更高。

2.在订单表（order）中统计中，生成这么每一条记录都包含如下信息：“所有订单的总和”、“每一位客户的所有订单的总和”、”每一单的金额“

关键点：使用了sum() over() 这个开窗函数

如图：
在这里插入图片描述
代码如下：

select 2  customerID,3  SUM(totalPrice) over() as AllTotalPrice,4  SUM(totalPrice) over(partition by customerID) as cusTotalPrice,5  totalPrice6  from OP_Order

3.在订单表（order）中统计中，生成这么每一条记录都包含如下信息：“所有订单的总和（AllTotalPrice）”、“每一位客户的所有订单的总(cusTotalPrice)”、”每一单的金额(totalPrice)“，”每一个客户订单的平均金额(avgCusprice)“，”所有客户的所有订单的平均金额(avgTotalPrice)“，“客户所购的总额在所有的订单中总额的比例(CusAllPercent)”,“每一订单的金额在每一位客户总额中所占的比例(cusToPercent)”。
在这里插入图片描述
代码如下

with tabs as
 (
  select 
 customerID,
 SUM(totalPrice) over() as AllTotalPrice,
 SUM(totalPrice) over(partition by customerID) as cusTotalPrice,
 AVG(totalPrice) over(partition by customerID) as avgCusprice,
 AVG(totalPrice) over() as avgTotalPrice,
 totalPrice
 from OP_Order
 )
 select 
 customerID,
 AllTotalPrice,
 cusTotalPrice,
 totalPrice,
avgCusprice,
avgTotalPrice,
 cusTotalPrice/AllTotalPrice as CusAllPercent,
 totalPrice/cusTotalPrice as cusToPercent 
 from tabs

4.在订单表（order）中统计中，生成这么每一条记录都包含如下信息：“所有订单的总和（AllTotalPrice）”、“每一位客户的所有订单的总(cusTotalPrice)”、”每一单的金额(totalPrice)“，”每一个客户订单的平均金额(avgCusprice)“，”所有客户的所有订单的平均金额(avgTotalPrice)“,“订单金额最小值（MinTotalPrice）”,“客户订单金额最小值(MinCusPrice)”,“订单金额最大值(MaxTotalPrice)”,“客户订单金额最大值（MaxCusPrice）”，“客户所购的总额在所有的订单中总额的比例(CusAllPercent)”,“每一订单的金额在每一位客户总额中所占的比例(cusToPercent)”。

关键：利用over子句进行操作。

如图：
在这里插入图片描述
具体代码如下：

with tabs as
(
 select 
customerID,
SUM(totalPrice) over() as AllTotalPrice,
SUM(totalPrice) over(partition by customerID) as cusTotalPrice,
AVG(totalPrice) over(partition by customerID) as avgCusprice,
AVG(totalPrice) over() as avgTotalPrice,
MIN(totalPrice) over() as MinTotalPrice,
MIN(totalPrice) over(partition by customerID) as MinCusPrice,
MAX(totalPrice) over() as MaxTotalPrice,
MAX(totalPrice) over(partition by customerID) as MaxCusPrice,
totalPrice
from OP_Order
)
select 
customerID,
AllTotalPrice,
cusTotalPrice,
totalPrice,
avgCusprice,
avgTotalPrice,
MinTotalPrice,
MinCusPrice,
MaxTotalPrice,
MaxCusPrice,
cusTotalPrice/AllTotalPrice as CusAllPercent,
totalPrice/cusTotalPrice as cusToPercent 
from tabs

总结：领用聚合函数再结合over子句，可以使表格向右扩张。并进行一些数据的统计。

SQL SERVER开窗函数

作为一名开发人员来讲，我感觉在职场白混了好多年，可能是自己真的没有进取的精神吧，看了《程序员的SQL金典》这本电子书，真的让我学到了不少知识，真心喜欢这本电子书，书中讲解的内容比较好懂，也比较实用。谢谢作者的辛勤汗水:)。

今天将要介绍SQL Sever的开窗函数，何谓开窗函数，不懂吧。反正对于我来说，我是摸不着头脑了，第一次听说过。那么，什么是开窗函数，其实可以理解为是聚合函数的一个加强版。因为使用聚合函数的话（不包括子查询的情况），整个查询都只能是聚合列返回值，而不能有基础行的返回值。那么对于需要基础行的返回值的话，就需要使用复杂的子查询或者是存储过程等才可以解决。但是使用开窗函数就可以轻松解决，它能够在同一行中同时返回基础行的列和聚合列。在ISO SQL规定了这样的函数为开窗函数，在Oracle中则被称为分析函数，而在DB2中则被称为OLAP函数。

开窗函数与聚合函数一样，都是对行的集合组进行聚合计算。它用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。反正我理解这个函数已经使用好子查询或者是其它方式求得聚合列的值给我合并。

以书中的例子一步一步来介绍，假设要计算所有人员的总数，我们可以执行下面的SQL语句：

SELECT COUNT(FName) FROM T_Person

这种方式比较直接，只返回一个聚合列的值，没有任何基础行的列的值。但是有时需要从不在聚合函数中的行的列中访问这些聚合计算的值（即基础行的列）。比如我们想查询每个工资小于5000元的员工信息（城市以及年龄），并且在每行中都显示所有工资小于5000 元的员工个数，尝试编写下面的SQL语句：

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName)
FROM T_Person
WHERE FSALARY<5000

执行上面的SQL以后我们会得到下面的错误信息：
选择列表中的列’T_Person.FCity’ 无效，因为该列没有包含在聚合函数或GROUP BY 子句中。
这是因为所有不包含在聚合函数中的列必须声明在GROUP BY 子句中，使用子查询的方式是可以解决：

SELECT FName, FCITY, FAGE, FSalary,
(
SELECT COUNT(FName) FROM T_Person
WHERE FSALARY<5000
)
FROM T_Person
WHERE FSALARY<5000

虽然使用子查询能够解决这个问题，但是子查询的使用非常麻烦，使用开窗函数则可以大大简化实现，下面的SQL语句展示了如果使用开窗函数来实现同样的效果：

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) OVER()
FROM T_Person
WHERE FSALARY<5000

可以看到与聚合函数不同的是，开窗函数在聚合函数后增加了一个OVER关键字。
开窗函数的调用格式为：函数名(列) OVER(选项)

我这里使用的是SQL Server 2008 R2,不知道从什么时候开始，SQL SERVER也支持开窗函数中使用ORDER BY子句（注：书本中说MSSQLServer中是不支持开窗函数中使用ORDER BY子句）。不管怎么样，这里我整合了网上的相关内容。也正因为开窗函数支持了ORDER BY子句之后，开窗函数被分为两大类。

第一大类：聚合开窗函数====》聚合函数(列) OVER (选项)，这里的选项可以是PARTITION BY子句，但不可是ORDER BY子句

第二大类：排序开窗函数====》排序函数(列) OVER(选项)，这里的选项可以是ORDER BY子句，也可以是　OVER（PARTITION BY子句　ORDER BY子句），但不可以是PARTITION BY子句

聚合开窗函数

OVER 关键字表示把聚合函数当成聚合开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做聚合开窗函数。
在上边的例子中，开窗函数COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

`PARTITION BY` 子句

开窗函数的OVER关键字后括号中的可以使用PARTITION BY 子句来定义行的分区来供进行聚合计算。与GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。下面的SQL语句用于显示每一个人员的信息以及所属城市的人员数：

SELECT FName, FCITY, FAGE, FSalary,
COUNT(FName) OVER(PARTITION BY FCITY)
FROM T_Person

OVER(PARTITION BY FCITY)表示对结果集按照FCITY进行分区，并且计算当前行所属的组的聚合计算结果。在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干扰。比如下面的SQL语句用于显示每一个人员的信息、所属城市的人员数以及同龄人的人数：

SELECT FName,FCITY, FAGE, FSalary,
COUNT(FName) OVER(PARTITION BY FCITY),
COUNT(FName) OVER(PARTITION BY FAGE)
FROM T_Person

排序开窗函数

对于排序开窗函数来讲，它支持的开窗函数分别为：ROW_NUMBER（行号）、RANK（排名）、DENSE_RANK（密集排名）和NTILE（分组排名）。

先看一段SQL语句：

select  FName, FSalary, FCity, FAge,  
row_number() over(order by FSalary) as rownum,  
rank() over(order by FSalary) as rank,  
dense_rank() over(order by FSalary) as dense_rank,  
ntile(6) over(order by FSalary)as ntile 
from  T_Person 
order by  FName

执行的结果如下（对于想自己尝试的朋友，那你得辛苦点，下载电子书或者是购买书来学习吧。因为我可是限于篇幅，省略去大部分内容哦）：
在这里插入图片描述
看到上面的结果了吧，下面来介绍下相关的内容。我们得到的最终结果是按照FName进行升序显示的。

对于row_number() over(order by FSalary) as rownum来说，这个排序开窗函数是按FSalary升序的方式来排序，并得出排序结果的序号

对于rank() over(order by FSalary) as rank来说，这个排序形容函数是按FSalary升序的方式来排序，并得出排序结果的排名号。这个函数求出来的排名结果可以排列，并列排名之后的排名将是并列的排名加上并列数（简单说每个人只有一种排名，然后出现两个并列第一名的情况，这时候排在两个第一名后面的人将是第三名，也就是没有了第二名，但是有两个第一名）

对于dense_rank() over(order by FSalary) as dense_rank来说，这个排序函数是按FSalary升序的方式来排序，并得出排序结果的排名号。这个函数与rand()函数不同在于，并列排名之后的排名只是并列排名加１（简单说每个人只有一种排名，然后出现两个并列第一名的情况，这时候排在两个第一名后面的人将是第二名，也就是两个第一名，一个第二名）

对于ntile(6) over(order by FSalary)as ntile 来说，这个排序函数是按FSalary升序的方式来排序，并得出排序结果的分组数。

排序函数和聚合开窗函数类似，也支持在OVER子句中使用PARTITION BY语句。例如：

select  FName, FSalary, FCity, FAge,  
row_number() over(partition by FName  order by FSalary) as rownum,  
rank() over(partition by FName order by FSalary) as rank,  
dense_rank() over(partition by FName order by FSalary) as dense_rank,  
ntile(6) over(partition by FName order by FSalary)as ntile 
from  T_Person 
order by  FName

关于PARTITION BY子句，请看上面的介绍，这里就不再累赘了。但是需要注意一点的是，在排序开窗函数中使用PARTITION BY子句需要放置在ORDER　BY子句之前。

开窗函数 –`over()`

一个学习性任务：每个人有不同次数的成绩，统计出每个人的最高成绩。

这个问题应该还是相对简单，其实就用聚合函数就好了。

select id,name,max(score) from Student group by id,name order by name

上边这种情况只适用id 和name是一一对应的，否则查询出来的数据是不正确的。

例如：
1 张三 100

2 张三 90

查询出来的结果

两条信息都会输出。

避免这种情况，可以使用开窗函数。

个人理解就是，开窗函数和聚合函数功能是相反的。

聚合函数，将多行数据合并成一行数据；而开窗函数则是将一行数据拆分成多行。

开窗函数可以满足上述问题，同事也可以满足其他问题。例如：求每个班最高成绩学生的信息。

分析：

每个人学号一定是不同的，名字可能有重名，最大复杂的情况是，每个班最高成绩可能不止一个。
如果继续使用开始的方式，那么是不能满足要求的。
使用开窗函数就能很好的解决这个问题。

--每个班级的成绩第一的学生
--学生表中信息如下
a 1 80
b 1 78
c 1 95
d 2 74
e 2 92
f 3 99
g 3 99
h 3 45
i 3 55
j 3 78

查询结果如下：

SQL查询语句如下：

select *
from
(
select name,class,s,rank()over(partition by class order by s desc) mm
from t2
) as t
where t.mm=1

心得：
rank（）跳跃排序，有两个第二名时后边跟着的是第四名
dense_rank() 连续排序，有两个第二名时仍然跟着第三名

over（）开窗函数：在使用聚合函数后，会将多行变成一行，
而开窗函数是将一行变成多行；
并且在使用聚合函数后，如果要显示其他的列必须将列加入到group by中，
而使用开窗函数后，可以不使用group by，直接将所有信息显示出来。

开窗函数适用于在每一行的最后一列添加聚合函数的结果。

常用开窗函数：

为每条数据显示聚合信息.(聚合函数() over())
为每条数据提供分组的聚合函数结果(聚合函数() over(partition by 字段) as 别名) --按照字段分组，分组后进行计算
与排名函数一起使用(row number() over(order by 字段) as 别名)

常用分析函数：（最常用的应该是1.2.3 的排序）
1、row_number() over(partition by ... order by ...)
2、rank() over(partition by ... order by ...)
3、dense_rank() over(partition by ... order by ...)
4、count() over(partition by ... order by ...)
5、max() over(partition by ... order by ...)
6、min() over(partition by ... order by ...)
7、sum() over(partition by ... order by ...)
8、avg() over(partition by ... order by ...)
9、first_value() over(partition by ... order by ...)
10、last_value() over(partition by ... order by ...)
11、lag() over(partition by ... order by ...)
12、lead() over(partition by ... order by ...)

lag 和lead 可以获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；
lag ，lead 分别是向前，向后；
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是超出记录窗口时的默认值）

sql server利用开窗函数`over()` 进行分组统计

这是一道常见的面试题，在实际项目中经常会用到。

需求：求出以产品类别为分组，各个分组里价格最高的产品信息。

实现过程如下：

declare @t table(
ProductID   int,
ProductName varchar(20),
ProductType varchar(20),
Price int)
 
--测试数据
insert @t
select 1,'name1','P1',3 union all
select 2,'name2','P1',5 union all
select 3,'name3','P2',4 union all
select 4,'name4','P2',4
 
--做法一：找到每个组里,价格最大的值；然后再找出每个组里价格等于这个值的
--缺点:要进行一次join
select t1.*
  from @t t1
  join (select ProductType,
               max(Price) Price
               from @t
               group by ProductType) t2 on t1.ProductType = t2.ProductType
 where t1.Price = t2.Price
 order by ProductType
 
--做法二：利用over()，将统计信息计算出来，然后直接筛选结果集。
--over() 可以让函数(包括聚合函数)与行一起输出。
;with cte as(
    select *, max(Price) over(partition by (ProductType)) MaxPrice
      from @t)
select ProductID,ProductName,ProductType,Price from cte where Price = MaxPrice
 order by ProductType
 
--over() 的语法为：over([patition by ] <order by >)。需要注意的是，over() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。
--over() 的另一常用情景是与 row_number() 一起用于分页。