526互联

mysql 窗口函数（Window Functions）

发布时间 2023-04-06 16:22:07作者: vincent2023

MySQL 窗口函数（Window Functions）是一种高级的 SQL 查询技巧，它允许在结果集的一组相关行上执行计算。窗口函数可以用于处理分组、排序、累计等复杂的聚合任务，使得查询更加简洁和高效。在 MySQL 8.0 及更高版本中，支持窗口函数。

以下是一些常用的窗口函数：

  1. ROW_NUMBER()：为结果集中的每一行分配一个唯一的整数序号。
  2. RANK()：为结果集中的每一行分配一个唯一的整数序号，但在具有相同值的行中使用相同的序号。在下一个序号时，会跳过重复的序号。
  3. DENSE_RANK()：与 RANK() 类似，但不会跳过重复的序号。
  4. NTILE(N)：将结果集分成 N 个组，并为每一行分配一个组号。
  5. CUME_DIST()：计算当前行在结果集中的累计分布。
  6. PERCENT_RANK()：计算当前行在结果集中的百分比排名。
  7. LEAD()：获取当前行后面的第 N 行的值。
  8. LAG()：获取当前行前面的第 N 行的值。
  9. FIRST_VALUE()：获取窗口中的第一行的值。
  10. LAST_VALUE()：获取窗口中的最后一行的值。
  11. NTH_VALUE()：获取窗口中的第 N 行的值。

使用窗口函数时，需要定义一个窗口（OVER 子句），它描述了如何为每一行定义相关的行集。窗口定义通常包括以下部分：

  1. PARTITION BY：按给定的列或表达式对结果集进行分区。每个分区将被视为一个独立的窗口，窗口函数会在每个分区上分别计算。
  2. ORDER BY：定义窗口内行的排序顺序。
  3. ROWS/RANGE：定义窗口的大小和形状。ROWS 基于行数，RANGE 基于值范围。

PARTITION BY

PARTITION BY 在窗口函数中的作用类似于分组。它用于将结果集划分为多个分区，以便在每个分区内单独进行窗口函数的计算。通过使用 PARTITION BY 子句，您可以在每个分区内独立地应用窗口函数，而不是在整个结果集中应用。

在很多情况下，PARTITION BY 子句非常有用。例如，当您需要对每个部门的员工进行排名时，可以使用 PARTITION BY 根据部门对员工进行分组，然后在每个部门内应用窗口函数（如 RANK() 或 ROW_NUMBER()）。

下面是一个使用 PARTITION BY 的示例：

准备数据

-- 创建 employees 表
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
salary DECIMAL(10, 2) NOT NULL,
department_id INT NOT NULL
);

-- 插入示例数据
INSERT INTO employees (first_name, last_name, salary, department_id) VALUES
('John', 'Doe', 5000.00, 1),
('Jane', 'Smith', 5500.00, 1),
('Mike', 'Brown', 6000.00, 2),
('Emily', 'Johnson', 6500.00, 2),
('Tom', 'Davis', 7000.00, 3),
('Nina', 'Taylor', 5500.00, 3),
('Sophia', 'Lee', 6000.00, 4),
('Daniel', 'Miller', 5800.00, 4);

-- 查询刚刚插入的数据
SELECT * FROM employees;

示例

SELECT 
    department_id, 
    first_name, 
    last_name, 
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_within_department
FROM employees;

在这个示例中，我们使用 RANK()函数为每个部门的员工分配工资排名。通过在窗口定义中包含 PARTITION BY department_id子句，我们确保了在每个部门内部进行排名计算。