mysql8.0窗口函数详解

在mysql5.7中使用group by 分组之后可以使用*查询所有行

select * from user group by age

这样的查询可以查询到分组后的第一条数据，但是同样的查询放到mysql8.0中就不行了，这个问题可以通过mysql8.0的窗口函数解决：但是MySQL 8.0 引入了窗口函数（Window Functions），允许我们在查询结果中同时保留原始数据和进行聚合操作。

窗口函数的基本语法如下：

WINDOW_FUNCTION(aggregate_expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [window_frame]
)

• WINDOW_FUNCTION：常用的窗口函数包括 SUM、AVG、MIN、MAX、COUNT、RANK、DENSE_RANK、ROW_NUMBER 等。
• PARTITION BY：用于将数据划分为不同的分区，类似 GROUP BY，但不改变行数。
• ORDER BY：在每个分区中对数据进行排序。
• window_frame：定义窗口的范围，包括起始行和结束行。

一、常用窗口函数

常用的窗口函数包括 SUM、AVG、MIN、MAX、COUNT、RANK、DENSE_RANK、ROW_NUMBER 等，它分为两类：聚合窗口函数和排名窗口函数

1、聚合窗口函数

SUM、AVG、MIN、MAX、COUNT：用于计算窗口内指定列的总和、平均值、最小值、最大值和行数。

示例：计算每个部门的累计销售额

SELECT 
    department_id,
    employee_id,
    salary,
    SUM(salary) OVER (PARTITION BY department_id) AS total_salary
FROM 
    employees;

2、排名窗口函数

RANK()

为每个分区分配排名，相同值会分配相同的排名，后续排名会跳过相应的数字。

-- 示例：按成绩为学生排名
SELECT 
    student_id,
    score,
    RANK() OVER (ORDER BY score DESC) AS score_rank
FROM 
    students;

DENSE_RANK()

和 RANK() 类似，但不同之处在于不会跳过排名数字。

-- 示例：计算学生成绩的密集排名
SELECT 
    student_id,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM 
    students;

ROW_NUMBER()

为每个分区中的每一行分配一个唯一的连续编号。

-- 示例：为每个分区中的学生分配连续编号
SELECT 
    department_id,
    student_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY score DESC) AS row_num
FROM 
    students;

二、PARTITION BY 子句

PARTITION BY 子句的作用是将数据划分为不同的分区，每个分区分别进行窗口函数的计算。

-- 示例：按部门分组计算每个员工的工资占部门总工资的比例
SELECT 
    department_id,
    employee_id,
    salary,
    salary / SUM(salary) OVER (PARTITION BY department_id) AS salary_ratio
FROM 
    employees;

三、ORDER BY 子句

ORDER BY 子句用于在每个分区中对数据进行排序，这对于需要有序数据的窗口函数（如排名函数）至关重要。

-- 示例：按部门分组，并为每个部门内的员工按工资排序并分配排名
SELECT 
    department_id,
    employee_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM 
    employees;

四、窗口帧（Window Frame）

窗口帧定义了窗口的范围，即当前行的窗口包括哪些其他行。窗口帧可以使用 RANGE 或 ROWS 关键字来指定。

RANGE 帧

-- 示例：假设有一张sales表，包含order_date和amount，计算每笔订单的过去7天累计销售额
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date 
        RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
    ) AS cumulative_sales
FROM sales;

ROWS 帧

ROWS 基于行数来定义窗口的范围。

-- 示例：计算当前行及其之前所有行的累计和
SELECT 
    date,
    amount,
    SUM(amount) OVER (
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sum
FROM 
    sales;

五、实战

笔者写这篇随笔是因为遇到了mysql5.7中的sql复制到mysql8.0中运行报错的问题：

select * from user group by age

使用窗口函数的写法是：

SELECT * FROM (
    SELECT * ,
    ROW_NUMBER() OVER (PARTITION BY age ORDER BY id DESC) AS row_num
    FROM `user`
) t
WHERE row_num = 1

注意：本文归作者所有，未经作者允许，不得转载