sql max同一行_从零学会SQL--SQL高级功能 第七关作业

1，什么是窗口函数

sql的高级功能：窗口函数。

它的作用：解决在每组内排名的问题。比如：1，公司的每个部门按业绩排名（排名问题）；2，找出每个部门排名前N的员工，进行奖励（topN问题）。

窗口函数也叫OLAP函数（Online Analytical Processing 联机分析处理），

可以对数据库数据进行实时分析处理（注意这个“实时”）

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

窗口函数有两种：

1）专用窗口函数。

rank, dense_rank, row_number等窗口函数。

2）汇总函数。（这儿又叫“聚合函数”）

如count，sum，avg，max，min等

因为窗口函数是对where子句或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

2， 如何使用窗口函数？

下面是“班级表”：

班级表

如果想在每个班级内按成绩由高到低排序，即得到如下的结果：

可以得到上面结果的sql语句是：

分析上面的sql语句：rank()是排序函数；partition by 用来对表按指定的列进行分组，order by 子句是对分组后的结果进行排序，默认是按照升序（asc）排列，如果需要降序排列，就要加关键词desc。

通过下图就可以理解partition by 子句（作用：分组）和order by 子句（作用：在组内排序）的作用了：

窗口函数具备了我们之前学过的group by子句的分组功能和order by子句的排序功能。但它们有很大的区别：使用group by子句分组，会改变表的行数，即，将一组合并为一行；而使用partition by子句分组和rank()函数不会减少表的行数。例如下面统计每个班级的人数：

group by 分组会改变表的行数

partition by子句分组不会改变表的行数

上面结果只是为了展示两者分组的区别。根据结果可知，针对这个问题，是需要用group by子句和order by子句来得到题目要求的结果的。因为用窗口函数得到的结果看起来很奇怪，数据是重复的。

“窗口函数“之所以起名叫”窗口“是因为partition by分组后的结果称为“窗口”，是“范围”的意思。

简单来说，窗口函数有以下功能：

1）同时具有分组和排序的功能。

2）不减少原表的行数。

3， 三个专用窗口函数rank, dense_rank, row_number的区别

从上面图中，可以直观地看到这三个函数的区别。即：

rank()函数和dense_rank()函数都考虑并列名次的情况，他俩的区别是：rank()函数的名次不连续，即会占用下一名次的位置，而dense_rank()函数的名次数字是连续的。

row_number()函数不考虑名次并列的情况，只是显示排序的自然数序列。

它们三个的相同之处是函数的括号内不需要参数，都必须空着。

4，经典面试题之一：排名问题

将上面的班级表按成绩从高到低排名，如果成绩相同则名次并列，且名次的数字连续。

分析思路：需要排名且不能改变原表的行数，所以需要用窗口函数；因为要得到的结果要考虑并列名次且名次序列连续，所以要选用dense_rank函数。对应的sql语句及其运行结果是：

举一反三：涉及排名问题，都可以使用窗口函数解决。需要明白rank()，dense_rank()和row_number()函数的区别，根据题目要求选择对应的函数。

5，经典面试题之二：TOPN问题

工作中常遇到的业务问题：

1，如何找到每个类别下用户最喜欢的产品是哪个？

2，如何找到每个类别下用户点击最多的5个商品是什么？

这其实是一类问题：分组取每组最大值、最小值，或者每组的前N条（top N）记录。

对于问题1，分析：

1），对这类问题，如果使用group by子句和汇总函数，可以得到每个组里的一个值（最大值、最小值或平均值等），但无法得到成绩最大值所在行的数据。

select 课程号，max(成绩) as 最大成绩

from score

group by 课程号；

2），可以将上面的sql语句作为子查询语句，使用关联子查询来实现，sql语句如下：

select *

from score as a

where 成绩=（select max(成绩)

from score as b

where a.课程号=b.课程号

group by 课程号）；

（上面查询结果中课程号0001有2行数据，因为最大成绩80有2个）

3），如果要按课程号分组，取成绩最小值所在行的数据，只要将上面sql语句中的max换成mix函数就可以。

对于问题2，案例：查询每个学生成绩最高的两个科目。

“成绩表“如下：

分析：1）涉及“每个”学生，就要分组，按学生姓名进行分组。

2）将表按学生姓名分组后，对成绩进行降序排列。排最前面的2个就是要找的。

3）结合前面的分析：需要分组、排序，同时不能减少原表的行数，所以就要想到用窗口函数。

4）如果题意是不要考虑并列名次，只取前2条记录的话，就选择row_number函数。（其实我理解的题意，是要考虑这种两个科目成绩相同的并列名次情况的。）

步骤一：按姓名分组（partition by 姓名），并按成绩降序排序（order by 成绩 desc）的sql语句如下：

select *,row_number() over(partition by 姓名 order by 成绩 desc)as ranking

from 成绩表；

步骤二：要在步骤一的基础上取出每个学生成绩最高的两个科目的成绩，要怎么办呢？可以用where子句指定查询条件where ranking<=2,但是这个时候一定要考虑到sql语句的运行顺序：select子句最后运行，所以如果直接把where子句写在步骤一的sql语句后面则会报错。

解决方法是什么呢？用子查询！

把第一步得到的结果作为一个新的表。sql语句如下：

select *

from (select *,row_number() over (partition by 姓名 order by 成绩 desc) as ranking

from 成绩表)

where ranking<=2;

举一反三：

因为topN问题（取每组最大的N条记录），涉及到“既要分组，又要排序”，所以要能想到用窗口函数来实现。将上面题目的sql语句的where子句的2改为N就可以称为这类问题的一个万能模版。如下：

topN问题 sql语句模版：

select *

from (select *,row_number() over (partition by 要分组的列名 order by 要排序的列名 desc) as ranking

from 表名)

where ranking<=N;

6，汇总函数作为窗口函数

汇总函数用作窗口函数时和专用窗口函数的用法完全相同。只是汇总函数后面的括号里面不能为空，需要指定汇总的列名。

以下是汇总函数用作窗口函数时的sql语句和对应的运行结果。简单来说就是，汇总函数用作窗口函数时，每一行的结果都是对自身记录和之上的所有数据进行计算。

所用的表：

sql语句：

select *,sum(成绩) over (order by 学号) as current_sum,

avg(成绩) over(order by 学号) as current_avg,

count(成绩) over (order by 学号) as current_count,

max(成绩) over (order by 学号) as current_max,

min(成绩) over (order by 学号) as current_min

from 班级表；

得到结果：

这样使用汇总函数的作用：可以在每一行的数据里直观的看到，截止到本行数据，统计数据（例如最大值、最小值等）是多少；同时可以看出每一行数据对整体统计数据的影响。

7，案例：如何在每个组里比较？

“成绩表“如下：

成绩表

问题：查找单科成绩高于该科目平均成绩的学生名单

思路分析：1）“查找单科成绩高于该科目平均成绩“，说明要在每个科目中比较，当要在一个类中比较时，就要用到分组。能实现“分组”功能的sql语句有两种：group by子句，和窗口函数的partition by。2）使用聚合窗口函数avg求出该门课程的平均成绩，然后找出比平均成绩高的数据。

sql语句如下：

select *

from (select *,avg(成绩) over (partition by 科目)as avg_score from 成绩表)

where 成绩> avg_score ;

注意，不能写成下面的这样：

select *,avg(成绩) over (partition by 科目)as avg_score

from 成绩表

where 成绩> avg_score ;

原因在前面的题目“查询每个学生成绩最高的两个科目”的分析里说过，因为select子句最后运行。

结果如下：

这个题也可以用前面学过的关联子查询来解决：

select *

from 成绩表 as a

where 成绩>（select avg(成绩)

from 成绩表 as b

where a.科目=b.科目

group by 科目）;

举一反三：查找每个组里大于平均值得数据，可以有两种方法：

1）使用窗口函数；

2）使用关联子查询。

8，窗口函数的移动平均（以平均为例，也可以移动求和）

用汇总函数avg作为窗口函数来说明：

select *,avg(成绩) over (order by 学号 rows 2 preceding) as current_avg

from 班级表；

运行结果如下：

上面的窗口函数中用了rows和preceding两个关键字，是“之前~行”的意思，也就是得到的结果是自身记录和之前两行的计算值。对这个题来说，就是平均值。每一行得到的结果，都是当前行和前面2行的平均值。当想要计算当前行与前n行（共n+1行）的平均值时，把数字2改为n就行了。

这种用法的使用场景：

由于这里可以通过preceding关键字调整作用范围，在以下场景中非常适用：在公司业绩名单排名中，可以通过移动，直观地查看与相邻名次业绩的平均、求和等统计数据。

9，总结

1.窗口函数的基本语法：

从前面的例子可以看出，partition by子句和order by子句不是必须同时出现，任一个都可以省略。省略即代表不需分组或排序。

2.窗口函数有两种：

1）专用窗口函数。例如rank, dense_rank, row_number等窗口函数。

2）汇总函数。（这儿又叫“聚合函数”）如count，sum，avg，max，min等

3.窗口函数有以下功能：

1）同时具有分组（partition by）和排序(order by)的功能。

2）不减少原表的行数，所以经常用来在每组内排名。

4.因为窗口函数是对where子句或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

5.窗口函数使用场景

1）在每组内排名问题

2）topN问题

3）在每个组里比较的问题

比如查找每个组里大于平均值的数据，有两种方法：方法1，使用窗口函数实现；方法2，使用关联子查询。