MySQL 窗口函数详解：分析性查询的强大工具

MySQL 窗口函数详解：分析性查询的强大工具

MySQL窗口函数是数据分析中的一项强大工具，它允许用户在不进行数据分组的情况下执行复杂的分析操作。从版本8.0开始，MySQL引入了窗口函数功能，使得开发者能够更灵活地处理数据，实现诸如排名计算、累计求和和移动平均等高级分析需求。本文将详细介绍窗口函数的基本语法、常见函数类型、窗口框架以及实际应用场景。

窗口函数的基本语法

窗口函数的基本语法结构如下：

function_name([arguments]) OVER (
    [PARTITION BY columns]
    [ORDER BY columns]
    [frame_clause]
)

• function_name: 窗口函数名，如 ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()、AVG() 等。
• PARTITION BY: 可选，用于定义窗口的分区，类似于 GROUP BY。
• ORDER BY: 用于定义窗口内的行顺序。
• frame_clause: 可选，定义窗口框架，可以是 ROWS 或 RANGE。

常见窗口函数

1. ROW_NUMBER()

为每个分区的每一行分配唯一的行号。

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

2. RANK()

为每个分区的每一行分配排名，排名有重复，且后面排名会有跳跃。

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

3. DENSE_RANK()

类似于 RANK()，但排名不跳跃。

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

4. NTILE(n)

将分区中的行均匀分布到 n 个桶中，并为每一行分配桶号。

SELECT name, department,
       NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

5. SUM(), AVG(), MIN(), MAX()

在窗口内计算聚合值。

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees;

窗口框架

窗口框架用来定义在计算聚合值时哪些行应该被包含在窗口内。框架可以用 ROWS 或 RANGE 来定义。

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

示例：

SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_sum
FROM employees;

应用场景

窗口函数在以下场景中非常有用：

• 计算每个部门内员工的工资排名。
• 求某段时间内的累计销售额。
• 计算移动平均值或累计和。
• 分析和报告需要复杂数据操作的情况。

通过使用窗口函数，开发者可以在查询中轻松实现复杂的分析和报告功能，而不需要进行额外的数据处理。窗口函数提供了一种简洁、优雅的解决方案，可以提高 SQL 查询的表达能力和执行效率。