SQL开窗函数整理

开窗函数理解

开窗函数又称OLAP函数（Online Analytical Processing，翻译为 “在线分析处理”）。Mysql是在8.0版本才开始支持开窗函数的。

开窗用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合)，它对一组值进行操作，不需要使用 GROUP BY 子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

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

聚合函数是将多行变成一行，按某一列的值进行分组，然后再使用聚合函数count(),avg()……
聚合函数如果要显示其他的列必须将列加入到group by中

开窗函数是将一行变成多行
开窗函数可以不使用group by，直接将所有信息显示出来

聚合函数的分类

聚合开窗函数
聚合函数(列) OVER(选项)，这里的选项可以是PARTITION BY 子句，但不可以是 ORDER BY 子句。

排序开窗函数
排序函数(列) OVER(选项)，这里的选项可以是ORDER BY 子句，也可以是OVER(PARTITION BY 子句 ORDER BY 子句)，但不可以是 PARTITION BY 子句。

示例

建表

create table Score(name varchar(255), class int(255),score int(255));
insert into Score
	(name,class,score)
value
	('A1',1,60),
	('A2',1,59),
	('A3',1,90),
	('A4',2,80),
	('A5',2,91),
	('A6',2,56),
	('A7',3,79),
	('A8',3,99),
	('A9',3,100),
	('A10',3,75),
	('A11',3,88),
	('A12',3,96);

-- 查看所有数据
select * from  Score

开窗函数

row_number() over(order by score) as rownum
表示按score 升序的方式来排序，并得出排序结果的序号

在排序开窗函数中使用 PARTITION BY 子句需要放置在ORDER BY 子句之前。

rank() over(order by score) as rank
表示按 score升序的方式来排序，并得出排序结果的排名号。

这个函数求出来的排名结果可以并列（并列第一/并列第二），并列排名之后的排名将是并列的排名加上并列数

dense_rank() over(order by score) as dense_rank
表示按score 升序的方式来排序，并得出排序结果的排名号。

这个函数并列排名之后的排名是并列排名加１

简单说每个人只有一种排名，然后出现两个并列第一名的情况，这时候排在两个第一名后面的人将是第二名，也就是两个第一名，一个第二名

ntile(6) over(order by score)as ntile
表示按 score 升序的方式来排序，然后 6 等分成 6 个组，并显示所在组的序号。