MySQL中的窗口函数是一类用来在某一部分查询结果上进行计算的函数，这些函数的用法与普通的聚合函数如 SUM、AVG、COUNT类似，但是与聚合函数不同的是，窗口函数不会讲多行数据合并成一行结果，而是可以保留每一行的数据，并且同时在一组数据也就是一个窗口上进行计算。

在使用过程中通常需要通过OVER 子句来定义数据分组和排序规则，如下所示，是窗口函数的一些特点

• 保留行：根据上面的介绍我们知道在窗口函数对行进行计算的时候，可以将所有的计算结果中的每一行数据都会保留在结果集中。
• 定义窗口：定义窗口函数的时候，需要通过OVER 子句来进行定义，也就是指定分区和排序规则。
• 支持多种函数：窗口函数包括聚合函数、排名函数、分布函数和统计函数。

常见的窗口函数

• 聚合窗口函数：如 SUM(), AVG(), COUNT(), MAX(), MIN() 等。
• 排名函数：
• ROW_NUMBER()：返回分区中的行号，按排序规则排序。
• RANK()：返回分区中的排名，排名有重复时会跳过排名。
• DENSE_RANK()：返回分区中的排名，排名有重复时不会跳过排名。
• NTILE(N)：将分区中的行按排序规则分成N份，返回每行所属的组号。
• 偏移函数：
• LAG(expression, offset, default)：返回当前行前面第 offset 行的 expression 值。
• LEAD(expression, offset, default)：返回当前行后面第 offset 行的 expression 值。
• 累计函数：
• FIRST_VALUE(expression)：返回当前窗口的第一个值。
• LAST_VALUE(expression)：返回当前窗口的最后一个值。
• NTH_VALUE(expression, N)：返回当前窗口的第 N 个值。

使用MySQL窗口函数进行查询的时候，可以在窗口函数中进行各种复杂的计算，而这种复杂的计算不会合并成一行，也就是不会丢失行数据，下面我们就通过几个例子来看看如何使用不同的窗口函数来进行操作。如下所示。

ROW_NUMBER() 排名函数

假设有一个包含学生成绩的students_scores分数表，如果我们想要根据成绩排名，我们可以通过如下的方式来进行操作。

CREATE TABLE students_scores (
    student_id INT,
    student_name VARCHAR(50),
    score INT
);

INSERT INTO students_scores (student_id, student_name, score) VALUES
(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 85),
(4, 'David', 91);

SELECT
    student_id,
    student_name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM
    students_scores;

上面的结果就可以按照成绩进行排名

RANK() 排名函数

当然，除了使用上面的这种方式，在students_scores表中，我们还可以使用RANK()函数来对学生的成绩进行排名，这种情况下分数相同的时候也会进行排名

SELECT
    student_id,
    student_name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM
    students_scores;

SUM() 聚合窗口函数

假设我们有一张销售记录表sales，其中包含了销售人员的各项销售信息，如果我们想要计算每个销售人员的累计销售额，我们可以通过如下的方式来进行操作。

CREATE TABLE sales (
    sale_id INT,
    salesperson_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_id, salesperson_id, sale_date, amount) VALUES
(1, 1, '2024-01-01', 100.00),
(2, 1, '2024-01-05', 200.00),
(3, 2, '2024-01-02', 150.00),
(4, 1, '2024-01-10', 50.00),
(5, 2, '2024-01-07', 300.00);

SELECT
    salesperson_id,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY salesperson_id ORDER BY sale_date) AS cumulative_sales
FROM
    sales;

LAG() 偏移函数

还是在上面的销售信息表中，我们可以通过LAG()函数获取每个销售记录的前一个销售记录的金额，如下所示。

SELECT
    salesperson_id,
    sale_date,
    amount,
    LAG(amount, 1, 0) OVER (PARTITION BY salesperson_id ORDER BY sale_date) AS previous_amount
FROM
    sales;

NTILE() 分布函数

这个操作我们可以在学生成绩表中进行演示，如下所示，使用 NTILE() 函数将学生按成绩分成四组。

SELECT
    student_id,
    student_name,
    score,
    NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM
    students_scores;

FIRST_VALUE() 累计函数

在销售信息表中，我们可以通过FIRST_VALUE()函数获取每个销售人员的第一笔销售记录的金额，如下所示。

SELECT
    salesperson_id,
    sale_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY salesperson_id ORDER BY sale_date) AS first_sale_amount
FROM
    sales;

总结

上面的这些例子展示了如何使用MySQL的窗口函数来执行各种数据分析任务。然后可以通过OVER子句，定义计算窗口的分区和排序规则，从而在查询结果集中进行复杂的计算而不丢失行数据。窗口函数大大增强了SQL的表达能力，数据分析和报告生成中非常有用，因为它们能够在不丢失行的情况下对数据进行复杂的计算和分析。MySQL从8.0版本开始支持窗口函数，大大增强了其数据处理能力。

Tags：sum() over