SQL窗口函数的使用场景与实例.docxVIP

SQL窗口函数的使用场景与实例

一、SQL窗口函数的基础认知

（一）窗口函数的定义与核心特性

SQL窗口函数（WindowFunction）是一种保留行级细节的聚合分析工具，它能够在不合并结果集行的前提下，对一组相关行（称为“窗口”）进行计算。与普通聚合函数（如SUM、AVG）不同，窗口函数不会将多行数据压缩为一行，而是为每一行返回一个基于窗口的计算结果——这意味着它既保留了原始数据的明细，又能实现分组或排序后的聚合分析。

从语法结构看，窗口函数的核心是OVER()子句，它定义了“窗口”的范围（即函数要操作的行集合）。一个完整的窗口函数语法通常包含三部分：窗口函数名、OVER()子句，以及可选的PARTITIONBY（分组）和ORDERBY（排序）子句。例如，计算每个部门内员工的工资排名，语法如下：RANK()OVER(PARTITIONBY部门IDORDERBY月薪DESC)。

窗口函数主要分为三类：第一类是排名函数（如RANK、DENSE_RANK、ROW_NUMBER），用于对数据进行排序并生成序号；第二类是聚合窗口函数（如SUM、AVG、MAX、MIN作为窗口函数），用于计算窗口内的累积或分组聚合值；第三类是分析函数（如LAG、LEAD、FIRST_VALUE、LAST_VALUE），用于获取窗口内的前后行或首尾行数据。这些分类覆盖了大多数常见的分析需求，是窗口函数的核心能力来源。

（二）窗口函数与普通聚合函数的关键区别

要理解窗口函数的价值，必须先明确它与普通聚合函数的差异——这是掌握窗口函数的关键。

普通聚合函数（如SUM(月薪)）的核心是“合并行”：它会将符合条件的所有行压缩为一行，返回一个单一的聚合结果（比如全表的工资总和）。而窗口函数的核心是“保留行”：它会为每一行返回一个基于窗口的计算结果，原始数据的行结构不会被破坏。例如，计算员工工资的部门内占比：

用普通聚合函数需要先计算部门总工资（子查询），再与员工个人工资关联，语法复杂；

用窗口函数只需一行代码：月薪/SUM(月薪)OVER(PARTITIONBY部门ID)，直接返回每个员工工资占部门总工资的比例，且保留所有员工的明细信息。

再举一个具体例子：假设有一张“员工表”，包含员工ID、部门ID、月薪三个字段。若要计算每个部门的总工资，普通聚合函数的写法是SELECT部门ID,SUM(月薪)FROM员工表GROUPBY部门ID，结果会合并为每个部门一行；而窗口函数的写法是SELECT员工ID,部门ID,月薪,SUM(月薪)OVER(PARTITIONBY部门ID)AS部门总工资FROM员工表，结果会保留所有员工的行，且每一行都显示所在部门的总工资。

简言之，普通聚合函数解决“what（整体是多少）”的问题，而窗口函数解决“how（每个个体在整体中的位置是怎样的）”的问题——这正是窗口函数的核心价值所在。

二、SQL窗口函数的典型使用场景与实例

在实际业务中，窗口函数的应用场景非常广泛，尤其擅长解决“分组内的明细分析”“排序与排名”“时间序列的累积/对比”等普通SQL难以高效处理的问题。以下是五大典型场景及具体实例：

（一）场景1：分组内的排名分析

排名是窗口函数最常见的使用场景之一。业务中经常需要对“每个分组内的记录排序”（比如每个班级的学生成绩排名、每个部门的员工工资排名、每个产品类别的销量排名），这时排名函数（RANK、DENSE_RANK、ROW_NUMBER）能快速解决问题。

三个排名函数的区别

RANK()：相同值返回相同排名，但会跳过后续序号（有间隙）。例如，若两名学生都是90分（第1名），下一名学生将是第3名。

DENSE_RANK()：相同值返回相同排名，不跳过后续序号（无间隙）。例如，两名90分学生是第1名，下一名是第2名。

ROW_NUMBER()：无论值是否相同，返回唯一的连续序号。例如，两名90分学生将分别得到1和2的序号。

实例：学生成绩的班级内排名

假设有一张“学生成绩表”，包含班级ID、学生姓名、语文成绩三个字段。现在需要计算每个班级内学生的语文成绩排名（相同分数并列，且无间隙），应使用DENSE_RANK()函数：

SELECT班级ID,学生姓名,语文成绩,DENSE_RANK()OVER(PARTITIONBY班级IDORDERBY语文成绩DESC)AS班级内排名FROM学生成绩表。

若业务要求“每个班级取成绩前3的学生”，则只需在上述查询外包裹一层，筛选班级内排名=3即可：

SELECT*FROM(SELECT班级ID,学生姓名,语文成绩,DENSE_RANK()OVER(PARTITIONBY班级ID