MySQL 中的窗口函数

   窗口函数是 MySQL 8.0 引入的一个重要特性，它可以在不改变数据行数的前提下，对每一行数据进行聚合、排名、累积等计算。与 GROUP BY 不同，窗口函数会保留所有原始行。

一、窗口函数

   窗口函数的执行逻辑是：在每一行数据上，基于“窗口”（一个定义好的数据范围）进行计算，然后把计算结果的附加列（如排名、累计值等）直接写到对应的行上，不合并行。

语法结构

SELECT
    列名,
    窗口函数() OVER (
        PARTITION BY 分区列    -- 分组
        ORDER BY 排序列        -- 排序
        ROWS/RANGE BETWEEN ... -- 窗口范围
    ) AS 别名
FROM 表名;

二、窗口函数的三大类别

1. 排名函数

示例：按成绩排名

-- 创建示例表
CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    score INT,
    class VARCHAR(10)
);

INSERT INTO students VALUES
(1, 'Alice', 95, 'A'),
(2, 'Bob', 85, 'A'),
(3, 'Charlie', 95, 'A'),
(4, 'David', 78, 'B'),
(5, 'Eva', 92, 'B'),
(6, 'Frank', 85, 'B');

-- 各排名函数的对比
SELECT 
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank,
    NTILE(3) OVER (ORDER BY score DESC) AS ntile
FROM students;

结果：

+---------+-------+---------+------+------------+-------+
| name    | score | row_num | rank | dense_rank | ntile |
+---------+-------+---------+------+------------+-------+
| Alice   | 95    | 1       | 1    | 1          | 1     |
| Charlie | 95    | 2       | 1    | 1          | 1     |
| Eva     | 92    | 3       | 3    | 2          | 2     |
| Bob     | 85    | 4       | 4    | 3          | 2     |
| Frank   | 85    | 5       | 4    | 3          | 3     |
| David   | 78    | 6       | 6    | 4          | 3     |
+---------+-------+---------+------+------------+-------+

区别：

• ROW_NUMBER()：1,2,3,4,5,6

• RANK()：1,1,3,4,4,6

• DENSE_RANK()：1,1,2,3,3,4

• NTILE(3)：1,1,2,2,3,3

2. 聚合函数（作为窗口函数）

示例：累计求和与移动平均

SELECT 
    date,
    sales,
    SUM(sales) OVER (ORDER BY date) AS cumulative_sales,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM sales_data;

3. 取值函数

示例：计算环比增长率

SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month)) / 
        LAG(revenue, 1) OVER (ORDER BY month) * 100, 2
    ) AS growth_rate_percent
FROM monthly_revenue;

三、PARTITION BY（分组计算）

PARTITION BY 将数据分组，窗口函数在每个分组内独立计算。

SELECT 
    class,
    name,
    score,
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class
FROM students;

结果：

+-------+---------+-------+---------------+
| class | name    | score | rank_in_class |
+-------+---------+-------+---------------+
| A     | Alice   | 95    | 1             |
| A     | Charlie | 95    | 1             |
| A     | Bob     | 85    | 3             |
| B     | Eva     | 92    | 1             |
| B     | Frank   | 85    | 2             |
| B     | David   | 78    | 3             |
+-------+---------+-------+---------------+

四、窗口范围（Frame）

窗口范围决定计算时包含哪些行：

SELECT 
    date,
    sales,
    SUM(sales) OVER (ORDER BY date) AS cumulative,                  -- 默认从开始到当前
    SUM(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS last_3_days,
    SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_all
FROM sales_data;

五、窗口函数 vs GROUP BY

-- GROUP BY：只能看到汇总结果
SELECT department, AVG(salary) FROM employees GROUP BY department;

-- 窗口函数：每行都保留，同时显示平均工资
SELECT 
    employee_id,
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;