MySQL窗口函数详解

MySQL窗口函数详解

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

什么是窗口函数?

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

窗口函数的语法

基本语法如下:

function_name()OVER([PARTITIONBY column_list][ORDERBY 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(ORDERBY score DESC)as rank
FROM students;

实际应用场景：查找每个部门的前N名员工

假设我们要找出每个部门薪资最高的3名员工：

CREATETABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2));INSERTINTO 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(PARTITIONBY department
            ORDERBY salary DESC)as salary_rank
    FROM employees
) ranked
WHERE salary_rank <=3ORDERBY department, salary_rank;

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

2. RANK() 和 DENSE_RANK()

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

基本用法

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

实际应用场景：学生成绩排名

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

CREATETABLE student_scores (
    id INT,
    name VARCHAR(50),
    score INT);INSERTINTO 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(ORDERBY score DESC)as rank_number,
    DENSE_RANK()OVER(ORDERBY score DESC)as dense_rank_number
FROM student_scores;

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

3. LAG() 和 LEAD()

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

基本用法

SELECTdate,
    sales,
    LAG(sales)OVER(ORDERBYdate)as previous_day_sales,
    LEAD(sales)OVER(ORDERBYdate)as next_day_sales
FROM daily_sales;

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

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

CREATETABLE monthly_sales (yearINT,monthINT,
    sales DECIMAL(10,2));INSERTINTO monthly_sales (year,month, sales)VALUES(2022,1,10000),(2022,2,12000),(2022,3,15000),(2023,1,11000),(2023,2,13000),(2023,3,16000);SELECTyear,month,
    sales,
    LAG(sales)OVER(PARTITIONBYmonthORDERBYyear)as prev_year_sales,(sales - LAG(sales)OVER(PARTITIONBYmonthORDERBYyear))/ 
    LAG(sales)OVER(PARTITIONBYmonthORDERBYyear)*100as growth_rate
FROM monthly_sales
ORDERBYmonth,year;

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

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

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

基本用法

SELECTdate,
    sales,SUM(sales)OVER(ORDERBYdate)as cumulative_sales,AVG(sales)OVER(ORDERBYdateROWSBETWEEN6PRECEDINGANDCURRENTROW)as moving_avg
FROM daily_sales;

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

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

CREATETABLE sales (
    id INT,
    department VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10,2));INSERTINTO 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(PARTITIONBY department
        ORDERBY sale_date
    )as cumulative_sales
FROM sales
ORDERBY department, sale_date;

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

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

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

CREATETABLE stock_prices (dateDATE,
    price DECIMAL(10,2));INSERTINTO 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);SELECTdate,
    price,AVG(price)OVER(ORDERBYdateROWSBETWEEN6PRECEDINGANDCURRENTROW)AS moving_avg
FROM stock_prices
ORDERBYdate;

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

结论

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

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

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