MySQL窗口函数汇总（二）

MySQL窗口函数详解
MySQL从8.0版本开始引入了窗口函数，这是一个强大的特性，可以大大简化复杂的数据分析任务。本文将详细介绍MySQL窗口函数的概念、语法和常见用法，并结合实际应用场景进行说明。
什么是窗口函数?
窗口函数是一种能够对结果集中的一组行进行操作的函数。它们类似于聚合函数，但不会将结果集缩减为单个行 - 相反，它们为每一行返回一个结果。
窗口函数的语法
基本语法如下:

function_name() OVER (
    [PARTITION BY column_list]
    [ORDER BY column_list]
    [frame_clause]
)

• function_name: 窗口函数的名称
• PARTITION BY: 可选，定义行分组的方式
• ORDER BY: 可选，定义分区内行的排序方式
• frame_clause: 可选，定义当前分区内的行子集(窗口帧)

常用的窗口函数及其应用场景
1. ROW_NUMBER()
ROW_NUMBER() 为每一行分配一个唯一的整数，用于在每个分区内对行进行排序并编号。可以帮助我们对数据进行分区后排序，获取排名信息。具体来说，ROW_NUMBER() 是一种分析函数，它可以根据 ORDER BY 子句中指定的列对行进行排序，并为每个分区内的行根据排序结果来分配唯一的连续编号。 PARTITION BY 子句类似于 GROUP BY 用于分组，该子句指定希望分区的列或表达式。行号将在每个分区内分配，然后重新开始为下一个分区分配。

SELECT 
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) as rank
FROM students;

实际应用场景：查找每个部门的前N名员工
假设我们要找出每个部门薪资最高的3名员工：

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'Sales', 60000),
(2, 'Bob', 'Sales', 50000),
(3, 'Charlie', 'Sales', 55000),
(4, 'David', 'Marketing', 65000),
(5, 'Eve', 'Marketing', 60000),
(6, 'Frank', 'Marketing', 70000),
(7, 'Grace', 'IT', 80000),
(8, 'Henry', 'IT', 75000),
(9, 'Ivy', 'IT', 78000);

SELECT *
FROM (
    SELECT 
        name,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) as salary_rank
    FROM employees
) ranked
WHERE salary_rank <= 3
ORDER BY department, salary_rank;

这个查询首先为每个部门的员工按薪资进行排名，然后筛选出排名前三的员工。

2. RANK() 和 DENSE_RANK()

• RANK() 为每一行分配排名，相同值的行获得相同排名，但会产生间隔。
• DENSE_RANK() 类似于RANK()，但不会产生间隔。

基本用法：

SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) as rank,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM students;

实际应用场景：学生成绩排名
假设我们要为学生的考试成绩进行排名，同时展示 RANK() 和 DENSE_RANK() 的区别：

CREATE TABLE student_scores (
    id INT,
    name VARCHAR(50),
    score INT
);

INSERT INTO student_scores (id, name, score) VALUES
(1, 'Alice', 95),
(2, 'Bob', 95),
(3, 'Charlie', 90),
(4, 'David', 88),
(5, 'Eve', 88),
(6, 'Frank', 85);

SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) as rank_number,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_number
FROM student_scores;

这个查询展示了学生成绩的排名，同时显示了 RANK() 和 DENSE_RANK() 的区别。RANK() 会在相同分数后产生间隔，而 DENSE_RANK() 不会。

结论
窗口函数是MySQL 8.0中的一个强大新特性，可以大大简化复杂的数据分析任务。通过上述实际应用场景的例子，我们可以看到窗口函数在处理排名、时间序列数据、累计计算等方面的强大能力。这些函数使得我们能够更高效地处理诸如员工排名、同比增长、累计总和、移动平均等常见的数据分析问题。
随着对窗口函数的深入理解和熟练应用，你将能够编写更简洁、更高效的SQL查询，大大提高数据分析的效率。窗口函数不仅可以简化查询，还可以提高查询性能，因为它们通常比使用子查询或自连接的等效查询更有效率。
继续探索和实践这些窗口函数，你会发现它们在日常数据分析工作中的无穷潜力。