OVER(PARTITION BY)开窗函数常见荤素搭配

一、场景

        最近在刷sql题，遇到了一道有意思的题，想分享一下。如果用的mysql版本要在8.0及以上

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , 'Jack' , '1990-01-01' , '男');
insert into Student values('02' , 'Tommy' , '1990-05-20' , '男');
insert into Student values('03' , 'Ramboo' , '1990-05-20' , '男');
insert into Student values('04' , 'Author' , '1990-08-06' , '男');
insert into Student values('05' , 'John' , '1991-12-01' , '男');
insert into Student values('06' , 'Elizabeth' , '1992-03-01' , '女');
insert into Student values('07' , 'Wanika' , '1989-07-01' , '女');
insert into Student values('09' , 'Rose' , '2017-12-20' , '女');
insert into Student values('10' , 'Curry' , '2017-12-30' , '女');
insert into Student values('11' , 'Red' , '2017-12-30' , '女');
insert into Student values('12' , 'Rain' , '2017-01-01' , '女');
insert into Student values('13' , 'Ikun' , '2018-01-01' , '女');
 
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
 
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
 
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
 

        现要求：按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

各位不妨先想一下，题不算太难只是觉得这个要求有些怪异。

       结果看起来是有些怪异，感觉一个单表查询被自己搞得复杂化了，所以试着找更简单的实现方法。于是找到了Oracle提供的分析函数，其中OVER(PARTITION BY)叫做开窗函数。分析函数和聚合函数都是为了计算统计某些组的聚合值，但两者区别在于聚合函数对于每个组只返回一行，而分析函数可以返回多行。mysql8.0前并不支持开窗函数。

        常规写法如下：

SELECT
	t.SId,
	avg_score,
	CId,
	score 
FROM
	sc s1,
	( SELECT SId, AVG( score ) avg_score FROM sc GROUP BY SId ) t 
WHERE
	s1.SId = t.SId 
ORDER BY
	t.avg_score DESC

        用开窗函数解决如下：

SELECT
	sid,
	avg( score ) over ( PARTITION BY sid ) AS avg_score,
	cid,
	score 
FROM
	sc 
ORDER BY
	avg_score DESC

看到这里不由得感概sql编程的强大，于是学习一下开窗函数。

二、开窗函数理解

        由上面的例子来看，如果第二条sql把over给去了，稍加修改加上group by 会怎样，只会查出几条平均成绩的数据。这就是上面说的分析函数和聚合函数的区别。

2.1.写法

        over（partition by a order by b） 按照b排序进行累计，按照a分区。

　　over（order by a range between 5 preceding and 5 following）：窗口范围为当前行数据减5加5行后的范围内的。

　　over（order by a rows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。

2.2.GROUP BY和PARTITION BY的区别

        两者都有分组统计的功能，但partition by并不具有group by的汇总效果，个人认为这是两者最大的不同。根据需求选择，PARTITION BY还具有别的高级功能。

2.3.运用

        现有需求：查同性别，年龄从大到小。

SELECT
	*,
	rank ( ) over ( PARTITION BY Ssex ORDER BY Sage DESC ) NO 
FROM
	student

         这里用到的rank()函数是由sql server提供的窗口函数，以根据分类为各个字段指定等级。 它为每个参与的行返回一个汇总值。

        类似的还有ROW_NUMBER()，DENSE_RANK()，NTILE()。我们把rank()换成dense_rank()试试，如下：

        dense_rank()是连续排序 ，我们可以看到当有人排名一致时，下一个等级就不会跳跃。说着很别扭，自行体会吧。

        再来看ROW_NUMBER()，我们发现上面用rank()、dense_rank()查的相同年龄的等级为同一等级，无论跳跃还是不跳跃，就排名来说这显然是不合适的。这时就可以用ROW_NUMBER()代替。如下：

         接下来的NTILE()则比较另类，我们把sql稍加修改。如下：

SELECT
	*,
	ntile(3) over ( PARTITION BY Ssex ORDER BY Sage DESC ) NO 
FROM
	student

        结果集如下：

         可以看到排名分成了三个等级，但是大规则根据性别分类是没影响的。在同一性别结果集中根据年龄分为三个等级。 另外还有一些常见搭配如

        first_value() over()：第一个

        last_value() over()：最后一个

        sum() over()：总和

        count() over()：求分组后的总数。

        max() over()：求分组后的最大值。

        min() over()：求分组后的最小值。

        avg() over()：求分组后的平均值。

        lag() over()：取出前n行数据。　　

        lead() over()：取出后n行数据。

        ratio_to_report() over()：Ratio_to_report() 括号中就是分子，over() 括号中就是分母

        percent_rank() over()：百分数

各位有兴趣可以试试，这里不一一介绍。