【02单表查询】——03：OVER子句

最新推荐文章于 2025-05-28 00:29:08 发布

原创最新推荐文章于 2025-05-28 00:29:08 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#OVER #sql排名函数 #ROW-NUMBER #RANK #NTILE

SQL Server 专栏收录该内容

6 篇文章

订阅专栏

本文深入解析SQL OVER子句及其开窗函数的功能、应用与区别，包括GROUPBY与OVER的区别、实例演示及排名函数ROW_NUMBER、RANK、DENSE_RANK与NTILE的使用方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

——OVER子句介绍
OVER子句用于为行定义一个窗口，一遍进行特定的计算，可以把行的窗口简单的理解成是运算符操作的一个行的集合，例如，聚合函数和排名函数都可以支持OVER子句的运算类型。由于OVER子句为这些函数提供了的一个行的窗口，所以这些函数也叫做“开窗函数”；

——GROUP BY和OVER的区别
集合开窗函数很group by有一点类似的就是 —— 都可以对行进行分组，但是group by对数据进行分组之后，查询会为每个组只返回一行，因此也限制了所有的表达式为每组只能返回一个值；聚合开窗函数使用over子句提供窗口作为上下文，对窗口中的一组值进行操作，而不是使用group by子句提供的上下文，这样就不必对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列

——OVER子句实例

SELECT orderid , custid , val,
SUM(val) OVER() AS totalvalue,
SUM(val) OVER(PARTITON BY custid) AS custtotalvalue
FROM Sales.OrderValues

我的实例数据库查询结果如下
这里写图片描述
上面的结果集我已经画出了蓝色的线条，右下角那个区域多画了一个进去，忽略那条多画的数据，会发现每个蓝色线块都是一个组；

其实呀，带空圆括号的OVER子句会对所有的行进行计算，这里的所有的行指的是经过WHERE、GROUP BY、HAVING处理阶段后剩下的那些行，注意：只有在SELECT和ORDER BY阶段才可以使用OVER子句，这里重点介绍在SELECT子句中的应用；

最后一列额两个蓝色块是按找custid进行分组处理的

SUM(val) OVER(PARTITON BY custid) AS custtotalvalue

在OVER中分区/分组使用PARTITON BY子句

OVER子句的一个优点就是能够在返回基本列的同时，在同一行对他们进行聚合；也可以在表达式中混合使用基本列和聚合列

SELECT orderid,custid,val,
100.* val / SUM(val) OVER() AS pctall,
100.* val / SUM(val) OVER(PARTITON BY custid) AS pctcust
FROM Sales.OrderValues

查询结果集为
这里写图片描述
注意，在表达和式中使用的是十进制实数100.(100后面加个点)，而不是直接使用整数100，因为这样可以隐式的将整数值val和SUM(val)转换成十进制实数值，否则，表达式中的除法将是整数除法，会截去熟知的小数部分。

——OVER（）子句的排名函数
OVER子句还支持四种排名函数：
1、ROW_NUMBER（行号）；
2、RANK（排名）；
3、DENSE_RANK（密集排名）；
4、NTILE
下面演示一下这个四个排名函数的简单用法

SELECT orderid,custid,val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum,
RANK() OVER(ORDER BY val) AS rank,
DENSE_RANK(ORDER BY val) AS dense_rank,
NTILE(10) OVER(ORDER BY val) AS ntile
FROM Sales.OrderValues
ORDER BY val

这里写图片描述

注意看蓝色画线区域各个列的区别

ROW_NUMBER()
该函数为查询的结果集中的各行分配一个递增的行号，其逻辑顺序通过OVER子句中的ORDER BY语句进行指定，在我们的查询例子中，逻辑顺序基于的是val列，因此可以看到，随着val值得增加，行号也随着增加，即使val没有增加，行号依然增加，所以说如果ORDER BY不能唯一的确定行的顺序，查询结果就是不确定的。

RANK()和DENSE_RANK()
ROW_NUMBER()函数即使排序值相同，也会为其生成唯一的行号，如果想让同样的值生成的号是一样的那么可以考虑使用RANK()和DENSE_RANK()函数，它们为具有相同逻辑排序值的所有行生成同样的排名。
RANK()和DENSE_RANK()之间的区别是：RANK表示之前有多少行居右更低的排序值，DENSE_RANK则表示之前有多少个更低的排序值

NTILE()
NTILE()函数可以将行关联到组，并为每个行分配一个所属的组号，NTILE接收一个表示组数量的参数，并要在OVER中指定逻辑顺序，我们上面的例子查询的830行被分为了10组，每组83条数据，如果组数无法整除表的行数，余数中的每一行会被平均分配到最前面的每个组，假如有102行，被分为10组，余数的2组会被平均分配到第一组和第二组，意思就是说第一组和第二组都有11条数据

和聚合开窗函数一样，排名函数也有PARTITON BY语句

SELECT orderid,custid,val,
    ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid,val;

该查询会输出：
这里写图片描述
看到行号是分组分配了吧

注意：OVER子句中指定的ORDER BY逻辑与数据展示没什么关系，并不会改变查询结果表最终的任何内容。

——SQL查询语句逻辑处理顺序总结
那么到现在为止，我对我之前到现在提及到的所有子句（【02单表查询】——01：各阶段查询顺序和各阶段解释 ;【02单表查询】——02：TOP ）进行一个逻辑处理顺序总结

1、FROM
2、GROUP BY
3、HAVING
4、SELECT
4-1OVER
4-2 DISTINCT
4-3 TOP
5、ORDER BY

期待你阅读下一篇博文【02单表查询】——04：谓词和运算符