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

MySQL 窗口函数提供了一种灵活的方式来处理 SQL 查询中的数据，它们允许你在不需要对数据进行分组的情况下对行集进行分析。窗口函数最常用于分析性操作，比如计算排名、累计和、移动平均值等。MySQL 从版本 8.0 开始支持窗口函数。以下是窗口函数的几个关键概念和常见用法：

窗口函数的基本语法

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

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 查询的表达能力和执行效率。