MySQL窗口函数OVER和GROUP BY语句学习

窗口函数简单介绍

OVER (partition by 字段名 order by 字段名)
窗口函数OVER（）指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。

开窗函数简单介绍

开窗函数的语法为：OVER (partition by 列名1 order by 列名2 )，
括号中的两个关键词partition by 和order by 可以只出现一个。

SELECT
	*,
	rank(  ) over ( PARTITION BY CLASSNO ORDER BY GRADE DESC ) 
FROM
	`test4`

1）每个班级内：按班级分组
partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）
2）按成绩排名
**order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

开窗函数主要分为以下两类：

1．排名开窗函数

RANK（）
DENSE_RANK（）
ROW_NUMBER（）

SELECT
	*,
	RANK() over ( ORDER BY GRADE DESC ) AS `rank`,
	DENSE_RANK() over ( ORDER BY GRADE DESC ) AS `dese_rank`,
	ROW_NUMBER() over ( ORDER BY GRADE DESC ) AS `row_num` 
FROM
	`test4`

rank函数：如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank函数：这如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

row_number函数：不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

分数	rank函数	dense_rank函数	row_number函数
80	1	1	1
80	1	1	2
80	1	1	3
70	4	2	4

2．聚合开窗函数

很多聚合函数都可以用作窗口函数的运算，如SUM、AVG、MAX、MIN、COUNT。
聚合开窗函数只能使用PARTITION BY子句，ORDER BY不能与聚合开窗函数一同使用。
注意：
一，排名开窗函数可以单独使用ORDER BY 语句，也可以和PARTITION BY同时使用。
二，ORDER BY 指定排名开窗函数的顺序，在排名开窗函数中必须使用ORDER BY语句。
三，PARTITION BY用于将结果集进行分组，开窗函数应用于每一组。
四，OVER() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。

加上order by也能执行，但是执行结果存在问题，具体可见文章末尾

与GROUP BY对比

1、聚合函数都会减少查询返回的行数。与带有GROUP BY子句的聚合函数一样，开窗函数也对行的子集进行操作，但它们不会减少查询返回的行数，开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。

2、group by 是分组函数，partition by是分析函数

3、执行顺序为from > where > group by > having > order，而partition by应用在以上关键字之后，可以简单理解为就是在执行完select之后，在所得结果集之上进行partition by分组
数据库选择

GROUP BY语句查询平均分

SELECT *,AVG(GRADE) 
FROM `test4`
GROUP BY CLASSNO

聚合开窗函数查询平均分

SELECT
	*,
	AVG( GRADE ) over ( PARTITION BY CLASSNO) 
FROM
	`test4`

简单来说，窗口函数有以下功能：
1）同时具有分组和排序的功能
2）不减少原表的行数

如果上述代码加上ORDER BY，会发现结果变成累计，PARTITION BY不生效

SELECT
	*,
	AVG( GRADE ) over ( PARTITION BY CLASSNO ORDER BY GRADE DESC) 
FROM
	`test4`

SELECT
	*,
	AVG( GRADE ) over ( ORDER BY GRADE DESC) 
FROM
	`test4`

以上两个运行结果都是下图