sql中窗口函数的用法窗口函数语法详解及排名应用-SQL-PHP中文网

sql中窗口函数的用法窗口函数语法详解及排名应用

冰火之心

发布： 2025-07-05 11:55:01

原创

591人浏览过

窗口函数是在sql中用于在查询结果中执行计算的函数，其基本语法为function_name() over (partition by column1, column2 order by column3)，其中function_name如rank(), row_number(), sum(), avg()等，over()定义窗口范围，partition by用于分区，order by用于排序。1. 窗口函数与group by的区别在于group by聚合数据减少行数，而窗口函数保留原始行并添加计算值；2. 排名函数包括rank()（跳过后续排名），dense_rank()（不跳过排名），row_number()（唯一编号）和ntile(n)（分组编号）；3. 窗口函数处理null值时通常忽略它们，但排序中的null值位置可通过nulls first/last控制；4. 性能优化技巧包括索引优化、避免不必要的排序、限制窗口大小、物化中间结果及使用数据库特定优化；5. 实际业务应用涵盖移动平均、累计总和、top n查找以及百分比排名等场景。

sql中窗口函数的用法窗口函数语法详解及排名应用

窗口函数，简单来说，就是在SQL查询中，让你能针对特定的“窗口”进行计算，而这个“窗口”通常是与当前行相关的行集合。它能让你在同一行中看到聚合结果和原始数据，这在很多报表和数据分析场景下非常有用。

窗口函数的基本语法是：function_name() OVER (PARTITION BY column1, column2 ORDER BY column3)

function_name()：你要使用的窗口函数，例如 RANK(), ROW_NUMBER(), SUM(), AVG() 等。
OVER()：关键词，表示这是一个窗口函数。
PARTITION BY：定义窗口的分区，类似于 GROUP BY，但不会将结果聚合为一行。
ORDER BY：定义窗口内数据的排序方式。

窗口函数与GROUP BY的区别是什么？

很多人容易把窗口函数和 GROUP BY 混淆。GROUP BY 会将数据聚合为更少的行，而窗口函数不会减少行数，它只是为每一行计算一个值。想象一下，你想知道每个部门的平均工资，同时还想看到每个员工的工资。GROUP BY 只能告诉你每个部门的平均工资，而窗口函数可以同时显示每个员工的工资和他们所在部门的平均工资。

举个例子，假设我们有一个 employees 表，包含 employee_id, department_id, 和 salary 字段。如果我们想知道每个员工的工资和他们所在部门的平均工资，可以使用如下SQL：

SELECT
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS department_avg_salary
FROM
    employees;

登录后复制

这个查询会返回每个员工的ID、部门ID、工资，以及他们所在部门的平均工资。

如何使用窗口函数进行排名？

窗口函数在排名方面非常强大。SQL提供了几个用于排名的窗口函数：

RANK()：为每个分区中的行分配一个排名，如果存在并列，则会跳过后续排名。
DENSE_RANK()：类似于 RANK()，但不会跳过排名。如果存在并列，则后续排名会紧随其后。
ROW_NUMBER()：为每个分区中的行分配一个唯一的序列号，从1开始。
NTILE(n)：将每个分区中的行分成 n 组，并为每行分配一个组号。

例如，如果我们想对 employees 表中的员工按工资进行排名，可以使用如下SQL：

SpeakingPass-打造你的专属雅思口语语料

使用chatGPT帮你快速备考雅思口语，提升分数

查看详情

SELECT
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_dense_rank,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_row_number
FROM
    employees;

登录后复制

这个查询会返回每个员工的ID、工资，以及他们的工资排名（使用 RANK(), DENSE_RANK(), 和 ROW_NUMBER() 三种方式）。

窗口函数如何处理NULL值？

窗口函数在处理 NULL 值时，通常遵循SQL的标准规则。例如，在计算 SUM() 或 AVG() 时，NULL 值会被忽略。在排序时，NULL 值的处理方式取决于具体的数据库系统。有些数据库会将 NULL 值排在最前面，有些则会排在最后面。你可以使用 NULLS FIRST 或 NULLS LAST 来显式指定 NULL 值的排序方式，但这并非所有数据库都支持。

例如，如果我们想对 employees 表中的员工按工资进行排名，并将 NULL 值排在最后面，可以使用如下SQL（假设你的数据库支持 NULLS LAST）：

SELECT
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC NULLS LAST) AS salary_rank
FROM
    employees;

登录后复制

窗口函数的性能优化有哪些技巧？

窗口函数在处理大数据集时可能会比较慢。以下是一些性能优化技巧：

索引优化：确保 PARTITION BY 和 ORDER BY 中使用的列都有索引。
避免不必要的排序：如果你的窗口函数不需要排序，就不要使用 ORDER BY 子句。
限制窗口大小：使用 ROWS BETWEEN 子句来限制窗口的大小。例如，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 表示只考虑当前行之前和之后的各一行。
物化中间结果：如果你需要在多个窗口函数中使用相同的结果，可以考虑将中间结果物化为一个临时表。
使用数据库特定的优化：不同的数据库系统可能有针对窗口函数的特定优化。查阅你使用的数据库的文档，了解如何优化窗口函数的性能。

如何在实际业务场景中使用窗口函数？

窗口函数在很多实际业务场景中都有应用，例如：

计算移动平均：可以使用 AVG() 窗口函数来计算一段时间内的移动平均值。例如，计算过去7天的平均销售额。
计算累计总和：可以使用 SUM() 窗口函数来计算累计总和。例如，计算每个月的累计销售额。
查找Top N：可以使用排名窗口函数来查找每个类别中的Top N。例如，查找每个部门中工资最高的3名员工。
计算百分比排名：可以使用 PERCENT_RANK() 或 CUME_DIST() 窗口函数来计算百分比排名。例如，计算每个学生的成绩在班级中的百分比排名。