发布于 由admin

MySQL编程进阶之：窗口函数（Window Functions）的实战：`ROW_NUMBER()`、`RANK()`和`LEAD()`的用法。

各位观众老爷，大家好！今天咱们来聊聊MySQL窗口函数，这玩意儿听起来高大上，其实就是让你的SQL语句变得更灵活、更强大。咱们今天重点讲讲ROW_NUMBER()、RANK() 和 LEAD() 这三个小可爱，看看它们是怎么在实际应用中大显身手的。

一、窗口函数是啥？为啥要用它？

咱们先来捋捋什么是窗口函数。简单来说，窗口函数就是在某些行的集合上执行的函数，但它不会像GROUP BY那样把这些行聚合在一起，而是会为每一行都返回一个值。你可以把它想象成一个“滑动窗口”，在你的数据上滑动，然后对窗口内的数据进行计算。

那为啥要用窗口函数呢？因为它能帮你解决一些用传统SQL很难解决的问题，比如：

  • 计算每个部门工资排名前几的员工
  • 计算每个用户最近一次购买商品的时间
  • 计算每个产品销售额的同比增长率

如果你用GROUP BY，那就只能得到每个部门的最大工资，或者每个用户最后一次购买的时间，但你没法同时得到用户的其他信息，比如用户名、购买的商品等等。窗口函数就能做到这一点，它既可以进行聚合计算，又能保留原始行的信息。

二、ROW_NUMBER()：行号小能手

ROW_NUMBER() 函数顾名思义，就是给结果集中的每一行分配一个唯一的行号。这个行号是按照你指定的顺序生成的。

语法：

ROW_NUMBER() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3 [ASC | DESC], ... )
  • PARTITION BY: 可选，将结果集分成多个分区，ROW_NUMBER() 会在每个分区内独立编号。
  • ORDER BY: 必选，指定排序的列，ROW_NUMBER() 会按照这个顺序生成行号。

举个栗子：

假设我们有一个 employees 表，包含员工姓名、部门和工资：

employee_name department salary
Alice Sales 50000
Bob Sales 60000
Charlie Sales 55000
David Marketing 45000
Eve Marketing 50000
Frank IT 70000
Grace IT 65000

现在，我们要给每个部门的员工按照工资从高到低排序，并分配一个行号：

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM
    employees;

结果：

employee_name department salary row_num
Bob Sales 60000 1
Charlie Sales 55000 2
Alice Sales 50000 3
Eve Marketing 50000 1
David Marketing 45000 2
Frank IT 70000 1
Grace IT 65000 2

可以看到，每个部门的员工都按照工资降序排列，并且分配了唯一的行号。

实际应用：

  • 分页查询： 可以用ROW_NUMBER() 来实现分页查询，比如获取第10到20条记录。
  • 获取每个分组的前N条记录： 比如获取每个部门工资最高的前3名员工。

三、RANK()：排名专家

RANK() 函数也是用来排名的，但它和ROW_NUMBER() 的区别在于，如果存在相同的值，RANK() 会给它们分配相同的排名，并且会跳过后续的排名。

语法：

RANK() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3 [ASC | DESC], ... )

语法和 ROW_NUMBER() 几乎一样。

举个栗子：

还是用上面的 employees 表，这次我们用 RANK() 来计算每个部门的员工的工资排名：

SELECT
    employee_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM
    employees;

结果：

employee_name department salary salary_rank
Bob Sales 60000 1
Charlie Sales 55000 2
Alice Sales 50000 3
Eve Marketing 50000 1
David Marketing 45000 2
Frank IT 70000 1
Grace IT 65000 2

注意看 Marketing 部门，Eve 的工资是 50000， David 的工资是 45000，所以 Eve 排名第 1， David 排名第 2。 如果两个人的工资都是 50000， 那么排名都会是 1， 然后下一个人的排名将会是 3，跳过了 2。

DENSE_RANK()：更紧凑的排名

DENSE_RANK() 函数和 RANK() 函数类似，也是用来排名的，但它不会跳过排名。 也就是说，即使存在相同的值，它也会紧密地分配排名。

举个栗子：

如果我们在上面的例子中使用 DENSE_RANK()：

SELECT
    employee_name,
    department,
    salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM
    employees;

结果：

employee_name department salary salary_rank
Bob Sales 60000 1
Charlie Sales 55000 2
Alice Sales 50000 3
Eve Marketing 50000 1
David Marketing 45000 2
Frank IT 70000 1
Grace IT 65000 2

如果 Marketing 部门有两个人的工资都是 50000， 那么排名都会是 1， 然后下一个人的排名将会是 2， 而不是像 RANK() 一样跳过 2。

实际应用：

  • 计算排名： 比如计算每个学生的考试成绩排名。
  • 筛选Top N： 比如获取每个类别销量排名前10的产品。

RANK() vs DENSE_RANK() vs ROW_NUMBER()

函数 作用 是否跳过排名 是否允许并列排名
ROW_NUMBER() 为结果集中的每一行分配一个唯一的行号，即使值相同，行号也是唯一的。 是 否
RANK() 根据指定的排序规则对结果集进行排名，如果存在相同的值，则分配相同的排名，并跳过后续的排名。 是 是
DENSE_RANK() 根据指定的排序规则对结果集进行排名，如果存在相同的值，则分配相同的排名，但不会跳过后续的排名。 否 是

四、LEAD()：展望未来

LEAD() 函数可以让你访问结果集中当前行之后的行的值。 它可以让你在同一行中访问到下一行的数据，这在计算环比、同比等指标时非常有用。

语法：

LEAD(expression, offset, default_value) OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3 [ASC | DESC], ... )
  • expression: 要访问的列。
  • offset: 偏移量，表示要访问当前行之后的第几行。默认为 1，表示访问下一行。
  • default_value: 可选，如果偏移量超出了结果集的范围，则返回该默认值。如果未指定，则返回 NULL。

举个栗子：

假设我们有一个 sales 表，包含日期和销售额：

sale_date sales_amount
2023-01-01 100
2023-01-02 120
2023-01-03 110
2023-01-04 130
2023-01-05 140

现在，我们要计算每天的销售额与下一天的销售额的差值：

SELECT
    sale_date,
    sales_amount,
    LEAD(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS next_day_sales,
    LEAD(sales_amount, 1, 0) OVER (ORDER BY sale_date) - sales_amount AS sales_difference
FROM
    sales;

结果：

sale_date sales_amount next_day_sales sales_difference
2023-01-01 100 120 20
2023-01-02 120 110 -10
2023-01-03 110 130 20
2023-01-04 130 140 10
2023-01-05 140 0 -140

可以看到，我们成功地计算出了每天的销售额与下一天的销售额的差值。 注意最后一行， 因为 2023-01-05 是最后一天，没有下一天了， 所以 LEAD() 函数返回了默认值 0。

LAG()：回顾过去

LAG() 函数和 LEAD() 函数类似，但它是访问结果集中当前行之前的行的值。 也就是说， LAG() 函数可以让你在同一行中访问到上一行的数据。

语法：

LAG(expression, offset, default_value) OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3 [ASC | DESC], ... )

语法和 LEAD() 函数几乎一样。

举个栗子：

还是用上面的 sales 表，这次我们用 LAG() 来计算每天的销售额与前一天的销售额的差值：

SELECT
    sale_date,
    sales_amount,
    LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS previous_day_sales,
    sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS sales_difference
FROM
    sales;

结果：

sale_date sales_amount previous_day_sales sales_difference
2023-01-01 100 0 100
2023-01-02 120 100 20
2023-01-03 110 120 -10
2023-01-04 130 110 20
2023-01-05 140 130 10

可以看到，我们成功地计算出了每天的销售额与前一天的销售额的差值。 注意第一行， 因为 2023-01-01 是第一天，没有前一天了， 所以 LAG() 函数返回了默认值 0。

实际应用：

  • 计算环比/同比： 比如计算每个月的销售额环比增长率。
  • 计算移动平均： 比如计算过去7天的平均销售额。
  • 查找连续事件： 比如查找连续3天下雨的日期。

五、实战演练：获取每个部门工资最高的前3名员工

咱们来一个稍微复杂一点的例子，用窗口函数来实现一个经典的需求：获取每个部门工资最高的前3名员工。

SELECT
    employee_name,
    department,
    salary
FROM (
    SELECT
        employee_name,
        department,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
    FROM
        employees
) AS ranked_employees
WHERE
    salary_rank <= 3;

解释：

  1. 子查询： 首先，我们用一个子查询来计算每个部门的员工的工资排名。这里我们用了 DENSE_RANK()，因为我们希望即使有并列排名，也要紧密地分配排名。
  2. 外层查询： 然后，在外层查询中，我们筛选出排名小于等于 3 的员工，也就是每个部门工资最高的前3名员工。

六、总结

今天咱们学习了MySQL窗口函数中的 ROW_NUMBER()、RANK() 和 LEAD() 这三个常用的函数。 它们的功能分别是：

  • ROW_NUMBER()：给结果集中的每一行分配一个唯一的行号。
  • RANK()：对结果集进行排名，相同的值分配相同的排名，并跳过后续的排名。
  • DENSE_RANK()：对结果集进行排名，相同的值分配相同的排名，但不跳过后续的排名。
  • LEAD()：访问结果集中当前行之后的行的值。
  • LAG()：访问结果集中当前行之前的行的值。

窗口函数是SQL高级查询中一个非常重要的工具，掌握它们可以让你编写出更简洁、更高效的SQL语句。 希望今天的讲解能帮助你更好地理解和使用窗口函数。

下次再见，祝大家写码愉快！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注