MySQL窗口函数详解

MySQL从8.0版本开始引入了窗口函数，这是一个强大的特性，可以大大简化复杂的数据分析任务。本文将详细介绍MySQL窗口函数的概念、语法和常见用法，并结合实际应用场景进行说明。

什么是窗口函数?

窗口函数是一种能够对结果集中的一组行进行操作的函数。它们类似于聚合函数，但不会将结果集缩减为单个行 - 相反，它们为每一行返回一个结果。

窗口函数的语法

基本语法如下:

<code>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()，但不会产生间隔。

基本用法

<code>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() 不会。

3. LAG() 和 LEAD()

LAG() 和 LEAD() 允许我们访问当前行之前或之后的行。

基本用法

<code>SELECT

date,

sales,

LAG(sales) OVER (ORDER BY date) as previous_day_sales,

LEAD(sales) OVER (ORDER BY date) as next_day_sales

FROM daily_sales;

实际应用场景：计算同比增长率

假设我们要计算每月销售额的同比增长率：

CREATE TABLE monthly_sales (

year INT,

month INT,

sales DECIMAL(10, 2)

);

INSERT INTO monthly_sales (year, month, sales) VALUES

(2022, 1, 10000), (2022, 2, 12000), (2022, 3, 15000),

(2023, 1, 11000), (2023, 2, 13000), (2023, 3, 16000);

SELECT

year,

month,

sales,

LAG(sales) OVER (PARTITION BY month ORDER BY year) as prev_year_sales,

(sales - LAG(sales) OVER (PARTITION BY month ORDER BY year)) /

LAG(sales) OVER (PARTITION BY month ORDER BY year) * 100 as growth_rate

FROM monthly_sales

ORDER BY month, year;

这个查询计算了每个月的销售额相比去年同期的增长率。

4. 聚合窗口函数 (如 SUM(), AVG())

聚合函数如 SUM() 和 AVG() 也可以作为窗口函数使用，可以计算累计总和或移动平均值。

基本用法

<code>SELECT

date,

sales,

SUM(sales) OVER (ORDER BY date) as cumulative_sales,

AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg

FROM daily_sales;

实际应用场景1：计算累计总和

假设我们要计算每个部门的累计销售额：

CREATE TABLE sales (

id INT,

department VARCHAR(50),

sale_date DATE,

amount DECIMAL(10, 2)

);

INSERT INTO sales (id, department, sale_date, amount) VALUES

(1, 'Electronics', '2023-01-01', 1000),

(2, 'Clothing', '2023-01-01', 500),

(3, 'Electronics', '2023-01-02', 1500),

(4, 'Clothing', '2023-01-02', 750),

(5, 'Electronics', '2023-01-03', 1200),

(6, 'Clothing', '2023-01-03', 600);

SELECT

department,

sale_date,

amount,

SUM(amount) OVER (

PARTITION BY department

ORDER BY sale_date

) as cumulative_sales

FROM sales

ORDER BY department, sale_date;

这个查询计算了每个部门的累计销售额，按日期排序。

实际应用场景2：计算移动平均值

假设我们有一个股票价格表，我们想计算7天移动平均价格：

<code>CREATE TABLE stock_prices (

date DATE,

price DECIMAL(10, 2)

);

INSERT INTO stock_prices (date, price) VALUES

('2023-01-01', 100.00),

('2023-01-02', 101.00),

('2023-01-03', 102.00),

('2023-01-04', 101.50),

('2023-01-05', 103.00),

('2023-01-06', 104.00),

('2023-01-07', 103.50),

('2023-01-08', 105.00),

('2023-01-09', 106.00),

('2023-01-10', 107.00);

SELECT

date,

price,

AVG(price) OVER (

ORDER BY date

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

) AS moving_avg

FROM stock_prices

ORDER BY date;

这个查询将计算包括当前日期在内的前7天的移动平均价格。

结论

窗口函数是MySQL 8.0中的一个强大新特性，可以大大简化复杂的数据分析任务。通过上述实际应用场景的例子，我们可以看到窗口函数在处理排名、时间序列数据、累计计算等方面的强大能力。这些函数使得我们能够更高效地处理诸如员工排名、同比增长、累计总和、移动平均等常见的数据分析问题。

随着对窗口函数的深入理解和熟练应用，你将能够编写更简洁、更高效的SQL查询，大大提高数据分析的效率。窗口函数不仅可以简化查询，还可以提高查询性能，因为它们通常比使用子查询或自连接的等效查询更有效率。

继续探索和实践这些窗口函数，你会发现它们在日常数据分析工作中的无穷潜力。