mySQL开窗函数

什么是开窗函数

开窗函数/分析函数：over()

开窗函数也叫分析函数，有两类：一类是聚合开窗函数，另一类是排序开窗函数

开窗函数的调用格式：

函数名（列名）OVER(partition by 列名 order by 列名)

使用场景

聚合函数只返回一组值，而有时候只返回一组值是无法满足要求的，这个时候，我们需要引入开窗函数对其进行求解，比如：查询每门课程前三名的学生信息

开窗函数与聚合函数的区别

• SQL标准允许将所有聚合函数用作开窗函数，用OVER关键字区分开窗函数和聚合函数
• 聚合函数只返回一个值，开窗函数每组可以返回多个值，因为开窗函数所执行的聚合计算的行集组是窗口

开窗函数的使用及说明

分区排序：row_number() over()

• 建立学生表

CREATE TABLE `student` (
  `id` bigint NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `course` varchar(50) DEFAULT NULL,
  `score` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

• 往学生表当中添加数据
  
• 查询每门课程前三名的学生的信息

select * from(
select course,
row_number() over (PARTITION BY course ORDER BY score DESC) number,
name,score
FROM student
) a 
WHERE a.number <= 3

row_number() over()/rank() over()/dense_rank() over()/ntile(n) over()的区别

row_number() over()

• 对相等的值不进行区分，相等的值对应的排名不同

rank() over()

• 相等的值排名相同，但序号从1到n不连续，如果有两个人在第3名，则没有第4名

dense_rank() over()

• 相等的值排名相同，序号从1到n连续

ntile(n) over()

• 把有序的数据集合平均分配到指定的数量n的桶中，将桶号分配给每一行，排序对应的数字为桶号，序号从1到n连续。如果不能平均分配，则较小桶号分配额外的行，且每个桶中能放的数据条数最多相差1

执行顺序

• 使用row_number() over()函数的时候，over()里面的分组以及排序的执行，晚于where group by order by 的执行