【Mysql自学笔记】基础篇（六）窗口函数及其应用

一、综述

1、窗口函数的定义

窗口函数是 MySQL 8.0 版本引入的强大功能，它允许在与一组相关行（称为窗口）相关的上下文中执行计算，而无需使用传统的 GROUP BY 子句对结果进行分组。窗口函数可以在查询结果集的每一行上执行特定的计算，同时能够访问该行所在窗口内的其他行数据，从而实现诸如排名、累计计算、移动平均等复杂的分析操作。

2、窗口函数的语法

----------窗口函数----------

function_name(expression) OVER ( 
	[PARTITION BY partition_expression] 
	[ORDER BY order_expression [ASC|DESC]] 
	[window_frame_clause] )

• function_name：这是具体的窗口函数名称，常见的有聚合类窗口函数（如 SUM、AVG、MAX、MIN 等）、排序类窗口函数（如 RANK、DENSE_RANK、ROW_NUMBER 等）、分析类窗口函数（如 LAG、LEAD、FIRST_VALUE、LAST_VALUE 等）。
• expression：根据不同的窗口函数和任务需求，选择输入相应的参数。
• OVER：是窗口函数语法中的关键字部分，它用于指定窗口的定义方式。
• PARTITION BY：用于对结果集进行分区操作，，窗口函数会在每个分区内独立进行计算。在一个数据表中也可以指定多个分区表达式，用逗号分隔。例如：PARTITION BY region, department，表示先按照地区分区，在每个地区内再按照部门进一步分区。
• ORDER BY：指定在每个分区内数据的排序方式，可以指定多个排序表达式，并用逗号分隔，同时可以选择升序（ASC）或降序（DESC）排列。例如：ORDER BY sales_amount DESC 表示按照销售金额降序排列。
• window_frame_clause：这是一个可选部分，用于进一步精确指定窗口的范围。常见的定义方式有：
  （1）ROWS BETWEEN：通过指定相对于当前行的行数范围来定义窗口。
  （2）RANGE BETWEEN：通过指定相对于当前行的值的范围来定义窗口。

3、常见的窗口函数分类

以下是一些常见窗口函数的具体语法示例，主要包括聚合类窗口函数、排序类窗口函数、以及取值类窗口函数等：

窗口函数分类	功能概述	常见函数示例
聚合类窗口函数	在特定的窗口范围内执行诸如求和、求平均值、求最大值、求最小值等常见的聚合操作。针对每个窗口单独展开计算，从而为每行数据赋予基于该窗口聚合计算所得到的结果。	- SUM()： 对窗口范围内指定列的数据进行求和运算。 - AVG()： 计算窗口范围内指定列数据的平均值。 - MAX()： 返回窗口范围内指定列数据的最大值。 - MIN()： 获取窗口范围内指定列数据的最小值。
排序类窗口函数	在窗口范围内明确每行数据的排名、序号等相关信息，依据不同的排名规则可获取到各异类型的排序结果。	- RANK()： 依照指定的排序条件对窗口内的数据行进行排名操作。 倘若存在两行或多行数据满足相同的排序条件（即数据值相等），那么这些行将共享同一排名，且下一行的排名会跳过相应的数量。 - DENSE_RANK()：同样是用于排名的函数，当有两行或多行数据满足相同排序条件时，这些行将共享同一排名，不过下一行的排名不会跳过，而是呈现连续的状态。 - ROW_NUMBER()：为窗口内的每行数据分配一个独一无二的序号，该序号依照指定的排序条件从1开始依次递增。无论数据值是否相等，每行都会被赋予不同的序号。
取值类窗口函数	主要用于获取与当前行相关的其他行的信息，比如获取前一行或后一行的值、计算行与行之间的差值等。	- LAG()：用于获取当前行之前某一行的指定列的值。 - LEAD()：与LAG()的功能相反，用于获取当前形之后某一行的指定列的值 - FIRST_VALUE()：用于获取窗口范围内指定列的第一个值（通常是依照指定的排序条件来确定）。 - LAST_VALUE()：用于获取窗口范围内指定列的最后一个值（通常是依照指定的排序条件来确定）。

4、本文使用示例数据集说明

本文创建一个名为 sales_data 的数据集，它记录了一家公司不同地区、不同销售人员在不同时间段的销售业绩相关信息，可以输入如下代码将数据写入数据表中：

-- 创建示例数据集
CREATE TABLE sales_data (
    sales_id INT AUTO_INCREMENT PRIMARY KEY,
    region VARCHAR(50),
    salesperson VARCHAR(50),
    sale_date DATE,
    unit_price DECIMAL(10, 2),
    quantity_sold INT,
    sales_amount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO sales_data (region, salesperson, sale_date, unit_price, quantity_sold, sales_amount) VALUES
('North', 'Alice', '2024-01-01', 10.00, 5, 50.00),
('North', 'Alice', '2024-01-05', 12.00, 3, 36.00),
('North', 'Bob', '2024-01-03', 8.00, 4, 32.00),
('North', 'Bob', '2024-01-08', 9.00, 6, 54.00),
('North', 'Charlie', '2024-01-06', 12.00, 3, 36.00),
('South'