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() 为每一行分配一个唯一的整数。

基本用法

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

3. LAG() 和 LEAD()

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

基本用法

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() 也可以作为窗口函数使用，可以计算累计总和或移动平均值。

基本用法

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天移动平均价格：

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查询，大大提高数据分析的效率。窗口函数不仅可以简化查询，还可以提高查询性能，因为它们通常比使用子查询或自连接的等效查询更有效率。

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