SQL 中的窗口函数使用总结

最新推荐文章于 2025-02-28 13:01:35 发布

hmyqwe

最新推荐文章于 2025-02-28 13:01:35 发布

阅读量558

点赞数

分类专栏： SQL 文章标签：数据库

本文链接：https://blog.youkuaiyun.com/hmyqwe/article/details/109045074

版权

SQL 专栏收录该内容

88 篇文章

订阅专栏

本文详细介绍了窗口函数在数据库分析中的作用，包括rank、dense_rank和row_number的区别，以及如何在SQL查询中使用它们进行分组排名。通过实例演示了如何利用window函数进行业绩排名和topN筛选，强调了窗口函数在保持原表行数的同时实现复杂数据分析。

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

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

语法

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
<窗口函数>的位置，可以放以下两种函数：

专用窗口函数，比如rank, dense_rank, row_number等
聚合函数，如sum. avg, count, max, min等

使用

专用窗口函数rank
举例:
下图为班级表中的内容:

我们想在每个班级内按成绩排名，得到下面的结果:

得到上面结果的sql语句代码如下：

select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from 班级表

解释下这个sql语句里的select子句，rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分：

1）每个班级内：按班级分组

partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）
2）按成绩排名

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。
通过下图，我们就可以理解partiition by（分组）和order by（在组内排序）的作用：
在这里插入图片描述
group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。
例如下面统计每个班级的人数：

partition子句可以省略，省略就是不指定分组，只是按成绩由高到低进行了排序，结果如下：
在这里插入图片描述
这样就失去了窗口函数的功能，一般不推荐这样使用。

其他专业窗口函数
专用窗口函数rank, dense_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 班级表

以上SQL得到结果：
在这里插入图片描述
从上面的结果可以看出：

rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4；
dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2；
row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4；
这三个函数的区别如下：

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

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

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

窗口函数是聚合函数时，会得出以下结果：
在这里插入图片描述
单独用sum举个例子：

如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推；
不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算；

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