当前位置：首页 > article >正文

「sql之窗口函数」

article 2025/1/12 2:19:03

窗口函数

最近学习sql的时候，发现了一个比较难理解的语法—窗口函数

先说定义：「看完定义你可能觉得一头雾水，哈哈别急，等我后续一步步拆解」窗口函数是 SQL 中一种用于在查询结果集的特定 “窗口”（子集）上进行计算的函数。它能够在不改变原始数据行的基础上，为每行数据计算出一个聚合值或排名值等相关结果。与普通聚合函数（如 SUM、AVG 等）不同，聚合函数会将多行数据聚合成一行输出，而窗口函数可以在每一行数据上都返回一个计算结果。

在这里插入图片描述

表达式

function(args) OVER([PARTITION BY expression] [ORDER BY expression[ASC|DESC]][frame])

表达式拆解：

首先，OVER，PARTITION BY，ORDER BY 都是关键字，记住就好啦，之后越用越习惯，我们主要来拆解一下他们的作用

1）`function(args)`

这个函数的作用就是在指定窗口中对数据进行计算或处理，以得出所需的结果。「至于这里说的窗口是什么，请往下看」

function(args)可以是聚合函数，也可以是排名函数，具体使用什么函数就和不同的业务相关了，我在这里列出几个常用的函数：

聚合函数：

SUM(args)：计算分区内指定列的总和。「这里的分区和窗口是一个概念，以下同理」
AVG(args)：计算分区内指定列的平均值。
MIN(args)：找出分区内指定列的最小值。
MAX(args)：找出分区内指定列的最大值。

排名函数：

RANK()：为分区内的行分配排名，相同的值会获得相同的排名，并且会跳过相同排名的数量。
DENSE_RANK()：为分区内的行分配排名，相同的值会获得相同的排名，但不会跳过相同排名的数量。
ROW_NUMBER()：为分区内的行分配一个唯一的连续整数，从 1 开始，无论行内的值是否相同。（关于这三个排名函数的区别，下面会详细介绍➕做题练习）

2）`[PARTITION BY expression]`

[PARTITION BY expression]其中，PARTITION BY 是关键字，记住即可，而后面的 expression，就是我们要指定一个分组，这个字段通俗理解就是，我们要按照什么进行分组，当然他是可选的

3）`[ORDER BY expression[ASC|DESC]]`

ORDER BY 就好理解了，就是要指定我们要按照什么进行排序，默认是升序，如果要降序排序，需要我们进行显示的指定

4）`[frame]`

最后一部分就是这个[frame]了，他其实是指定一个窗口大小，也是可选的，其中他又分为两种模式，分别是ROWS BETWEEN和RANGE BETWEEN，下面我们来简单说一下这两种模式的区别：

ROWS BETWEEN：按物理行来进行划分窗口，明确指定了窗口包含的行数，从当前行开始，向前或向后延伸一定的行数。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：窗口从分区的第一行开始，到当前行结束。
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：窗口包含当前行的前一行、当前行和后一行，总共三行。

RANGE BETWEEN：按一定的数值逻辑来划分窗口范围，该范围是根据 ORDER BY 子句中列的值的范围来确定的，而不是物理行。通常与 ORDER BY 子句中的排序字段的逻辑值范围相关。

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：窗口包含从分区的第一行开始，到当前行**（基于** ORDER BY **列的值）**的所有行。
RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING：窗口包含 ORDER BY 列的值在当前行的值的前 5 个值到后 5 个值之间的所有行，而不是具体的物理行。

关键区别总结：

ROWS BETWEEN 基于物理行，根据实际的行位置来确定窗口范围，对于需要精确控制前后行数量的计算（如移动平均、相邻行对比等）很有用。
RANGE BETWEEN 基于逻辑范围，根据 ORDER BY 列的值的范围来确定窗口范围，更适合处理需要根据排序字段的值的范围进行计算的情况，例如基于数据值范围的聚合计算，对数据的逻辑分组和分析更有帮助。

大家注意体会上述的物理行与逻辑值的关系~~~

针对[frame]，我再列出一些他的常用表达式和对应的含义，大家可以对照着看看，一定可以理解~

ROWS BETWEEN 常用表达式：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：分区范围为从起始行到当前行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：分区范围为从当前行到最后一行
ROWS BETWEEN n PRECEDING AND m FOLLOWING：从当前行的前 n 行开始，到当前行的后 m 行结束

RANGE BETWEEN 常用表达式：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：此窗口范围从该分区中排序字段最小的行开始，到当前行结束。

RANGE BETWEEN 的表达式不好理解，我们举一个具体的例子来看：

SELECT 
    product_id, 
    sales_date, 
    sales_amount, 
    SUM(sales_amount) OVER (
        PARTITION BY product_id 
        ORDER BY sales_date 
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sales_amount
FROM 
    sales;

解释：对于每个 product_id 分区，按 sales_date 排序。此窗口范围从该分区中 sales_date 最小的行开始，到当前行（根据 sales_date 的值）结束。cumulative_sales_amount 计算的是从该分区中 sales_date 最小的行到当前行的 sales_amount 的累计和。它会考虑 sales_date 值小于或等于当前行 sales_date 的所有行，而不是具体的行数。

RANGE BETWEEN n PRECEDING AND m FOLLOWING：此窗口范围包括 sales_date 值在当前行 sales_date 减去 n（这里 n = 10）到当前行 sales_date 加上 m（这里 m = 10）范围内的所有行。

举例：

SELECT 
    product_id, 
    sales_date, 
    sales_amount, 
    AVG(sales_amount) OVER (
        PARTITION BY product_id 
        ORDER BY sales_date 
        RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
    ) AS range_average

解释：对于每个 product_id 分区，按 sales_date 排序。此窗口范围包括 sales_date 值在当前行 sales_date 减去 n（这里 n = 10）到当前行 sales_date 加上 m（这里 m = 10）范围内的所有行。range_average 计算的是这些行的 sales_amount 的平均值。请注意，这里的范围是基于 sales_date 的值，而不是物理行，因此如果有多个行的 sales_date 在这个范围内，它们都会被考虑进去。

排序函数的区别

在这里插入图片描述

函数记忆关键点：

row_number()：序号不重复且连续
rank()：序号可以重复，但序号不连续（比如：两个同学成绩都是 100 并列第一，第三个同学排名就是第三）
dense_rank()：序号可以重复，序号连续

练习

LeetCode 178. 分数排名

SELECT
  Scores.score,
  -- 使用 DENSE_RANK() 窗口函数对成绩进行排名
  -- 该函数会为结果集中的每一行分配一个排名，根据分数降序排列
  -- 相同分数的行将获得相同的排名，并且排名不会跳过，即下一个不同分数的排名会紧挨着上一个不同分数的排名
  dense_rank() over (
    -- 按照分数降序排列
    ORDER BY
      score DESC
  ) AS `rank`
FROM
  Scores;

LeetCode 184. 部门工资最高的员工

select
  Department,
  Employee,
  Salary
from
  (
    select
      d.name as Department,
      e.name as Employee,
      e.salary as Salary,
      -- 窗口函数，根据部门ID分组后再根据员工薪水降序排序的顺序进行排名
      rank() over (
        partition by
          e.departmentId
        order by
          e.salary desc
      ) as salary_rank
    from
      Employee as e
      inner join Department as d on e.departmentId = d.id
  ) as t
where
  -- 取排名为1的员工就是部门最高薪水的员工
  salary_rank = 1;