SQL中的窗口函数应用——排名与聚合.docxVIP

SQL中的窗口函数应用——排名与聚合

一、引言：从业务痛点到窗口函数的价值

在日常数据分析工作中，我们经常会遇到这样的场景：想知道每个销售代表在部门内的业绩排名，同时保留他们的入职时间、所属团队等细节；想计算每个月的累计销售额，同时看到当月的单独业绩；想对比每个员工的工资与部门平均工资的差距，却不想丢失员工的个人信息。这些“既要整体统计，又要保留个体细节”的需求，用传统SQL的GROUPBY或子查询解决时，往往需要写多层嵌套或复杂的JOIN，不仅代码可读性差，还容易因多次扫描表导致效率低下。

窗口函数（WindowFunction）的出现，恰好解决了这个“两难”问题。它的核心逻辑是：在保留原始数据行的同时，对指定“窗口”（即数据集的一个子集）内的数据进行计算。这种特性让窗口函数成为连接“个体细节”与“群体统计”的桥梁——既可以给数据排序、排名，也可以计算总和、平均值等聚合结果，且所有计算都不会减少原始数据的行数。

本文将围绕窗口函数中最常用的排名与聚合两类应用展开，从基础概念到进阶技巧，再到复杂业务场景的组合使用，一步步拆解窗口函数的逻辑，帮助读者掌握其应用方法，并理解它如何让数据分析更高效、更贴近业务本质。

二、窗口函数的基础认知：从概念到结构

要熟练运用窗口函数，首先需要理解它的核心定义与结构。只有明确“窗口”是什么、函数如何工作，才能在实际场景中灵活调整参数，解决具体问题。

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

窗口函数是SQL中一类特殊的函数，它的本质是对数据集的一个“窗口”（即子集）进行计算，并为原始数据中的每一行返回一个结果。这里的“窗口”可以理解为：通过PARTITIONBY（分组）和ORDERBY（排序）划定的一个数据范围——比如“每个部门内按工资从高到低排序的员工集合”，就是一个典型的窗口。

窗口函数与普通函数的最大区别在于结果集的保留：普通聚合函数（如SUM、AVG）会将分组后的结果合并为一行（例如按部门计算平均工资后，每个部门只剩一行结果）；而窗口函数会为每一行数据返回一个计算值，原始数据的所有字段（如员工姓名、工资）都会被保留。例如，要计算每个部门的平均工资并保留员工信息，用窗口函数只需写AVG(salary)OVER(PARTITIONBYdept)，无需额外的JOIN或子查询。

窗口函数的基本结构由两部分组成：函数名+OVER子句。其中，OVER子句是窗口函数的核心，它定义了“窗口”的范围和计算规则，包含两个关键参数：

PARTITIONBY：将数据划分为多个“窗口”（即分组），每个窗口内的计算独立进行。例如，PARTITIONBYdept会把数据按部门分成多个窗口，每个部门的平均工资单独计算。

ORDERBY：指定窗口内数据的排序顺序，这对排名、累积计算等场景至关重要。例如，ORDERBYsalaryDESC会让每个部门内的员工按工资从高到低排序。

（二）窗口函数与传统SQL工具的对比

为了更直观地理解窗口函数的优势，我们通过一个实际案例对比传统SQL与窗口函数的差异：假设我们有一张employee表，包含name（员工姓名）、dept（部门）、salary（工资）三个字段，需要解决两个问题：

计算每个部门的平均工资，同时保留每个员工的姓名和工资；

给每个部门的员工按工资从高到低排名。

用传统SQL解决问题1：需要先通过GROUPBY计算部门平均工资，再用JOIN关联回原始表：

sql

SELECTe.name,e.dept,e.salary,d.avg_salary

FROMemployeee

JOIN(

SELECTdept,AVG(salary)ASavg_salary

FROMemployee

GROUPBYdept

)dONe.dept=d.dept;

用窗口函数解决问题1：只需一行代码，无需子查询或JOIN：

sql

SELECTname,dept,salary,AVG(salary)OVER(PARTITIONBYdept)ASavg_salary

FROMemployee;

用传统SQL解决问题2：需要用子查询计数比当前员工工资高的人数，再加1得到排名：

sql

SELECTe1.name,e1.dept,e1.salary,

(SELECTCOUNT(*)+1

FROMemployeee2

WHEREe2.dept=e1.deptANDe2.salarye1.salary)ASrank

FROMemployeee1;

用窗口函数解决问题2：直接用RANK函数，代码简洁明了：

sql

SELECTname,dept,salary,RANK()OVER