发布于 由admin

MySQL新版本特性之：`MySQL 8.0`的`Window Functions`：`OLAP`分析和复杂查询的利器。

好的，下面是关于 MySQL 8.0 Window Functions 的技术讲座文章：

MySQL 8.0 Window Functions：OLAP 分析和复杂查询的利器

大家好，今天我们来深入探讨 MySQL 8.0 中引入的 Window Functions，这是一种强大的 SQL 特性，极大地增强了 MySQL 在 OLAP（Online Analytical Processing）分析和复杂查询方面的能力。

1. 什么是 Window Functions？

传统 SQL 中的聚合函数，如 SUM(), AVG(), COUNT(), MAX(), MIN() 等，会将多行数据聚合为一行结果。而 Window Functions（窗口函数）则允许我们在查询结果的每一行上，执行与当前行相关的聚合计算，同时保留原始行的详细数据。换句话说，窗口函数不会像聚合函数那样将多行数据折叠成一行，而是为每一行都计算出一个基于“窗口”的聚合值。

这个“窗口”可以理解为与当前行相关的一组行，窗口函数的计算就是基于这个窗口内的数据进行的。窗口的定义可以基于 PARTITION BY 子句（将数据分成多个分区）和 ORDER BY 子句（在分区内对数据排序），以及 ROWS 或 RANGE 子句（定义窗口的大小）。

2. Window Functions 的语法结构

Window Functions 的基本语法如下：

window_function(arguments) OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...]
    [ROWS | RANGE frame_extent]
)

  • window_function(arguments): 这是具体的窗口函数，例如 SUM(), AVG(), ROW_NUMBER(), RANK() 等。arguments 是传递给窗口函数的参数，有些窗口函数不需要参数。

  • OVER(...): 这是 OVER 子句，用于定义窗口的规范。

    • PARTITION BY column1, column2, ...: 可选子句，用于将结果集划分为多个分区。窗口函数将分别应用于每个分区。如果没有 PARTITION BY 子句，则整个结果集被视为一个分区。

    • ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...: 可选子句，用于在每个分区内对行进行排序。ORDER BY 子句对于某些窗口函数（如 ROW_NUMBER(), RANK(), LAG(), LEAD()）至关重要，因为它们的计算结果依赖于行的顺序。

    • ROWS | RANGE frame_extent: 可选子句，用于定义窗口的大小（也称为窗口框架）。frame_extent 定义了相对于当前行的窗口边界。如果没有指定 ROWS 或 RANGE，则默认窗口是整个分区。

      • ROWS: 基于行数的窗口定义。可以使用 BETWEEN 关键字指定窗口的开始和结束位置。例如：

        • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 从分区的第一行到当前行。
        • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 从当前行的前一行到当前行的后一行。
        • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 从当前行到分区的最后一行。

      • RANGE: 基于值的窗口定义。只能与 ORDER BY 子句一起使用，并且 ORDER BY 的列必须是数值类型或日期类型。例如：

        • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 所有小于等于当前行 ORDER BY 列值的行。
        • RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING: ORDER BY 列的值在当前行值的前后 10 个单位内的所有行。
        • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 所有大于等于当前行 ORDER BY 列值的行。

3. 常见的 Window Functions

MySQL 8.0 提供了多种内置的 Window Functions，可以分为以下几类：

  • 聚合函数: SUM(), AVG(), COUNT(), MAX(), MIN(), STDDEV(), VAR_POP(), VAR_SAMP() 等。这些函数在窗口内执行聚合计算。

  • 排序函数: ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK(), CUME_DIST(), NTILE()。这些函数用于对窗口内的行进行排序和排名。

  • 值函数: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()。这些函数用于访问窗口内其他行的值。

3.1 聚合函数作为 Window Functions

我们可以将标准的聚合函数用作 Window Functions，以计算窗口内的聚合值。

示例：计算每个部门的工资总额和平均工资，并显示每个员工的工资和部门总工资、平均工资。

假设我们有以下 employees 表：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    department VARCHAR(255),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 50000.00),
(2, 'Bob', 'Sales', 60000.00),
(3, 'Charlie', 'Marketing', 55000.00),
(4, 'David', 'Marketing', 70000.00),
(5, 'Eve', 'IT', 80000.00),
(6, 'Frank', 'IT', 90000.00);

使用 Window Functions 的查询：

SELECT
    employee_name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS department_total_salary,
    AVG(salary) OVER (PARTITION BY department) AS department_average_salary
FROM
    employees;

结果：

employee_name department salary department_total_salary department_average_salary
Alice Sales 50000.00 110000.00 55000.00
Bob Sales 60000.00 110000.00 55000.00
Charlie Marketing 55000.00 125000.00 62500.00
David Marketing 70000.00 125000.00 62500.00
Eve IT 80000.00 170000.00 85000.00
Frank IT 90000.00 170000.00 85000.00

在这个例子中，SUM(salary) OVER (PARTITION BY department) 计算了每个部门的工资总额，AVG(salary) OVER (PARTITION BY department) 计算了每个部门的平均工资。PARTITION BY department 子句将数据按部门划分，因此每个部门都有自己的窗口。

3.2 排序函数

排序函数用于对窗口内的行进行排序和排名。

  • ROW_NUMBER(): 为窗口中的每一行分配一个唯一的序列号，从 1 开始。

  • RANK(): 为窗口中的每一行分配一个排名。如果有多行具有相同的排序值，则它们将获得相同的排名，并且下一个排名将被跳过。

  • DENSE_RANK(): 类似于 RANK()，但它不会跳过排名。如果有多行具有相同的排序值，则它们将获得相同的排名，并且下一个排名将是连续的。

  • PERCENT_RANK(): 计算每一行的百分比排名。结果范围是 0 到 1。

  • CUME_DIST(): 计算每一行的累积分布。结果范围是 0 到 1。

  • NTILE(n): 将窗口中的行分成 n 组，并为每一行分配一个组号。

示例：计算每个部门员工的工资排名。

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

结果：

employee_name department salary row_num rank_num dense_rank_num
Bob Sales 60000.00 1 1 1
Alice Sales 50000.00 2 2 2
David Marketing 70000.00 1 1 1
Charlie Marketing 55000.00 2 2 2
Frank IT 90000.00 1 1 1
Eve IT 80000.00 2 2 2

在这个例子中，ROW_NUMBER(), RANK(), 和 DENSE_RANK() 都用于计算每个部门员工的工资排名。PARTITION BY department 子句将数据按部门划分，ORDER BY salary DESC 子句指定按工资降序排序。

3.3 值函数

值函数用于访问窗口内其他行的值。

  • LAG(expression, offset, default): 访问窗口中当前行之前 offset 行的 expression 的值。如果 offset 超出窗口范围，则返回 default 值。

  • LEAD(expression, offset, default): 访问窗口中当前行之后 offset 行的 expression 的值。如果 offset 超出窗口范围，则返回 default 值。

  • FIRST_VALUE(expression): 返回窗口中第一行的 expression 的值。

  • LAST_VALUE(expression): 返回窗口中最后一行的 expression 的值。

  • NTH_VALUE(expression, n): 返回窗口中第 n 行的 expression 的值。

示例：计算每个员工的工资与前一个员工的工资差。

SELECT
    employee_name,
    department,
    salary,
    LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS previous_salary,
    salary - LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS salary_difference
FROM
    employees;

结果：

employee_name department salary previous_salary salary_difference
Alice Sales 50000.00 0.00 50000.00
Bob Sales 60000.00 50000.00 10000.00
Charlie Marketing 55000.00 0.00 55000.00
David Marketing 70000.00 55000.00 15000.00
Eve IT 80000.00 0.00 80000.00
Frank IT 90000.00 80000.00 10000.00

在这个例子中，LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) 访问了每个部门中前一个员工的工资。PARTITION BY department 子句将数据按部门划分，ORDER BY salary 子句指定按工资升序排序。LAG 函数的第三个参数 0 是默认值，用于处理分区中的第一行，因为第一行没有前一行。

4. ROWS 和 RANGE 的使用

ROWS 和 RANGE 子句用于定义窗口的大小，也就是窗口框架。它们允许我们更精确地控制窗口内包含的行。

示例：计算每个员工的工资及其与前后两名员工的工资的平均值。

SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS average_salary_around
FROM
    employees;

结果：

employee_name department salary average_salary_around
Alice Sales 50000.00 55000.00
Bob Sales 60000.00 55000.00
Charlie Marketing 55000.00 62500.00
David Marketing 70000.00 62500.00
Eve IT 80000.00 85000.00
Frank IT 90000.00 85000.00

在这个例子中，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 指定窗口包含当前行、前一行和后一行。

示例：如果 employees 表中存在相同薪资的员工，使用 RANGE 子句计算每个员工的工资及其与薪资相差 5000 以内的员工的平均工资。

假设 employees 表新增一条数据：

INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(7, 'Grace', 'IT', 82000.00);

查询语句如下：

SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department ORDER BY salary RANGE BETWEEN 5000 PRECEDING AND 5000 FOLLOWING) AS average_salary_range
FROM
    employees;

结果：

employee_name department salary average_salary_range
Alice Sales 50000.00 55000.00
Bob Sales 60000.00 55000.00
Charlie Marketing 55000.00 62500.00
David Marketing 70000.00 62500.00
Eve IT 80000.00 81000.00
Grace IT 82000.00 81000.00
Frank IT 90000.00 90000.00

在这个例子中，RANGE BETWEEN 5000 PRECEDING AND 5000 FOLLOWING 指定窗口包含工资在当前员工工资前后 5000 范围内的所有员工。由于 Eve 的工资是 80000，Grace 的工资是 82000，所以计算 Eve 和 Grace 平均薪资时，都考虑了彼此的薪资。

总结 ROWS 和 RANGE 的区别：

特性 ROWS RANGE
基础 基于行数 基于值
ORDER BY 可选，但排序影响窗口内容 必须，且 ORDER BY 的列必须是数值或日期类型
窗口定义 窗口大小是固定的行数，与值无关 窗口大小是基于值的范围，行数可能变化
适用场景 需要基于行号进行计算的场景，例如计算相邻行的差值 需要基于值的范围进行计算的场景，例如计算某个值附近的值的平均值

5. Window Functions 的实际应用场景

Window Functions 在 OLAP 分析和复杂查询中有着广泛的应用，以下是一些常见的场景：

  • 计算移动平均值: 可以使用 AVG() 和 ROWS 子句计算一段时间内的移动平均值。例如，计算过去 7 天的每日销售额的移动平均值。

  • 计算累计总和: 可以使用 SUM() 和 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 子句计算累计总和。例如，计算每个月的累计销售额。

  • 计算百分比: 可以使用 SUM() 和 OVER() 子句计算每个类别占总体的百分比。例如，计算每个产品的销售额占总销售额的百分比。

  • 查找最大值/最小值: 可以使用 FIRST_VALUE() 和 LAST_VALUE() 函数查找窗口中的最大值/最小值。例如，查找每个部门工资最高的员工。

  • 排名和排序: 可以使用 RANK(), DENSE_RANK(), 和 ROW_NUMBER() 函数对数据进行排名和排序。例如，计算每个学生的成绩排名。

  • 预测和趋势分析: 可以使用 LAG() 和 LEAD() 函数进行预测和趋势分析。例如，预测未来的销售额。

6. Window Functions 的性能考虑

虽然 Window Functions 功能强大，但在使用时也需要注意性能问题。

  • ORDER BY 子句: ORDER BY 子句会增加查询的计算复杂度，因为需要对数据进行排序。尽量避免在不需要排序的场景下使用 ORDER BY 子句。

  • 窗口大小: 窗口越大，计算复杂度越高。尽量缩小窗口大小，只包含必要的行。

  • 索引: 合适的索引可以提高查询的性能。例如，如果经常使用 PARTITION BY 子句，可以在分区列上创建索引。

  • 查询优化器: MySQL 的查询优化器会对包含 Window Functions 的查询进行优化。可以使用 EXPLAIN 命令查看查询的执行计划，并根据需要进行调整。

7. Window Functions 与子查询的比较

在 MySQL 8.0 之前，很多复杂的查询需要使用子查询才能实现。Window Functions 提供了一种更简洁、更高效的替代方案。

示例：查找每个部门工资高于部门平均工资的员工。

使用子查询：

SELECT
    employee_name,
    department,
    salary
FROM
    employees e
WHERE
    salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

使用 Window Functions：

SELECT
    employee_name,
    department,
    salary
FROM
    (SELECT
        employee_name,
        department,
        salary,
        AVG(salary) OVER (PARTITION BY department) AS department_average_salary
    FROM
        employees) AS subquery
WHERE
    salary > department_average_salary;

在这个例子中，使用 Window Functions 的查询更加简洁，并且通常性能更高，因为它避免了对 employees 表的多次扫描。

8. 总结

Window Functions 是 MySQL 8.0 中引入的一项重要特性，它极大地增强了 MySQL 在 OLAP 分析和复杂查询方面的能力。通过灵活定义窗口，我们可以方便地执行各种聚合、排序和值访问操作，而无需使用复杂的子查询。掌握 Window Functions 的使用，可以帮助我们编写更简洁、更高效的 SQL 查询，从而更好地分析和利用数据。

Window Functions 是数据分析的强大工具，灵活应用可以简化复杂查询，在 MySQL 8.0 中发挥更大的价值。

发表回复

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