sql取最大值的那一行_七天学会SQL-07SQL高级功能

一、了解窗口函数

1、什么是窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理，是sql的一种高级功能。

为什么叫“窗口”函数呢？

这是因为partition by分组后的结果称为“窗口”，这里的窗口不是我们家里的门窗，而是表示“范围”的意思。

2、窗口函数的作用

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

2）不减少原表的行数；

3）遇到在组内排名的问题时可以使用，如以下业务问题：

排名问题：如每个部门按业绩来排名；

topN问题：如找出每个部门排名前N的员工进行奖励；

在每个组里比较的问题：如查找每个组里大于平均值的数据。

3、基本语法

‹窗口函数› over (partition by ‹用于分组的列名›                 
order by ‹用于排序的列名›)

【partition by分组后的结果称为“窗口”，表示一个范围】

4、窗口函数的分类

1）专用窗口函数：rank、dense_rank, row_number等；

2）聚合函数：如sum. avg, count, max, min等。

5、注意事项

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

2）【与group by分组子句的区别】group by分组汇总后改变了表的行数，一行只有一个类别；而partiition by和rank函数不会减少原表中的行数；

3）partition子句可是省略，省略就是不指定分组，只进行排序。

select *,        
rank() over (order by 成绩 desc) as ranking
from 班级表;

二、窗口函数的使用

1、专用窗口函数（rank）

透过案例了解、学习窗口函数rank

问题：在以下班级表内按每个班级内成绩排名

已知左表的情况，现在想要把左表得信息转变为右表得信息，这里就要用到窗口函数rank。

第一步：每个班级内，按班级分组

partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）

第二步：按成绩排名

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。通过下图，我们就可以理解partiition by（分组）和order by（在组内排序）的作用了。

SQL语句为：

select*, rank()over(partitionby 班级 orderby 成绩 desc)as ranking 
from 班级表;

2、窗口函数和group by的区别？

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。

例如下图：

3、窗口函数的功能

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

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

2）不减少原表的行数

语法如下：

‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)

4、其他专用窗口函数

除rank外，专用窗口函数还包括dense_rank，row_number等，下面举个例子来说明这三个专用窗口函数的区别：

select *,
rank() over (order by 成绩 desc) as ranking, 
dense_rank() over (order by 成绩 desc) as dese_rank,
row_number() over (order by 成绩 desc) as row_num
from 班级表;

运算结果：

在这三个专用窗口函数中，函数后面的()括号不需要任何参数，保持()空着就可以。

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。

5、案例：

1）经典排名问题

本题使用图表

现在需要按成绩来排名，如果两个分数相同，那么排名要是并列的。

正常排名是1，2，3，4，但是现在前3名是并列的名次，排名结果是：1，1，1，2。

解题思路：涉及排名，用窗口函数，再看用什么窗口函数？

select *, 
dense_rank() over (order by 成绩 desc) as dese_rank 
from 班级表;

注解：这里因为没有要求，不需要对班级进行分组，所以没有使用paritition by 班级。

涉及到排名的问题，都可以使用窗口函数来解决。记住rank, dense_rank, row_number排名的区别。

2）案例：经典top N问题

本题使用图表

这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（top N）记录。

案例：按课程号分组取成绩最大值所在行的数据（或最小值）

· 分组取最大值

-- 按课程号分组取成绩最大值所在行的数据
select * 
from score as a 
where 成绩 = ( select max(成绩) 
               from score as b 
               where b.课程号 = a.课程号);

· 分组取最小值

-- 按课程号分组取成绩最小值所在行的数据
select *
from score as a 
where 成绩 = (select min(成绩) from score as b where a.课程号=b.课程号);

案例：下图中的各科成绩表记录了每个学生各科的成绩，如何查找每个学生成绩最高的2个科目？

本题使用图表

-- 查询以下成绩表中每个学生成绩最高的两个科目。
select* 
from ( select *,row_number() over (partition by 课程号  order by 成绩 desc) as ranking 
       from score) as b
where ranking <= 2

子查询后面要加 as a

举一反三 遇到类似问题 可以使用模板

# topN问题 sql模板 
select * 
from (select *, row_number() over (partition by 要分组的列名  order by 要排序的列名 desc) as ranking 
      from 表名) as a 
where ranking ‹= N;

三、 聚合函数

1、聚合函数是什么？如何使用？

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

举个例子来说明聚合函数的作用：

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 班级表;

SQL运算结果为：

聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。不仅是sum求和，平均、计数、最大最小值，也是同理都是针对自身记录、以及自身记录之上的所有数据进行计算。（如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。）

2、聚合函数的作用？

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

3、案例：如何在每个组里比较？

查找单科成绩高于该科目平均成绩（两种解法）

解法1：窗口 函数

解题思路：

第1步，聚合函数avg()作为窗口函数，将每一课程号成绩的平均值求出。sql语句如下：

select *, 
avg(成绩) over (partition by 课程号) as 平均成绩 
from score;

第2步,因为运行顺序，这里需要结合子查询 注意as d

select*
from(select *, 
avg(成绩) over (partition by 课程号) as 平均成绩 
from score) as d
where 成绩>平均成绩

解法2：子查询

select *
from score as a
where 成绩>(select avg(成绩) from score as b where a.课程号=b.课程号 group by 课程号);

结论：查找每个组里大于平均值的数据，可以有两种方法：

1）使用上面讲到的窗口函数来实现

2） 使用关联子查询，可以回顾《从零学会sql：复杂查询》里的案例

四、窗口函数的移动平均

举个例子来说明移动平均的概念：

select *, 
avg(成绩) over (order by 学号 rows 2 preceding) as current_avg 
from 班级表;

对查询结果进行分析：

看上面的窗口函数中，用了rows和preceding这两个关键字，是“之前~行”的意思，上面的句子中，是之前2行。也就是得到的结果是自身记录及前2行的平均。想要计算当前行与前n行（共n+1行）的平均时，只要调整rows…preceding中间的数字即可。

这里需要注意：在移动平均中，被选出的数据构成一个“框架”，例如，刚才例子中的0002、0003、0004行数据，就是一个“框架”。

这样使用窗口函数有什么用呢？

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

五、 总结

1.窗口函数语法

‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)

2.分类

• 专用窗口函数---rank(),dense_rank(),row_number()（括号中没有参数，注意区分三者区别）
• 聚合函数---max(),min(),sum(),avg(),count() (括号中有参数）

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

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

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

4.注意事项

窗口函数原则上只能写在select子句中

partition子句是可以省略，省略就是不指定分组

5.窗口函数使用场景

1）经典top N问题

找出每个部门排名前N的员工进行奖励

2）经典排名问题

业务需求“在每组内排名”，比如：每个部门按业绩来排名

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

比如查找每个组里大于平均值的数据，可以有两种方法

方法1，使用前面窗口函数案例来实现

方法2，使用关联子查询

模版：

select*
from（select*，row_number() over (partition by 部门 order by salary desc) as ranking
from 表名) as a
where ranking<=N;

注意：不要忘记起别名，子查询的别名以及排序结果的别名

• 经典排名问题

业务需求“在每个组内排名”，比如，每个部门按业绩来排名

• 在每个组内比较问题

可以用关联子查询，也可以用窗口函数实现