sql取最大值的那一行_SQL学习-高级功能

一、窗口函数

窗口函数也称为OLAP函数。OLAP是online analytical processing的简称，意思是对数据库数据进行实时分析处理。

用于解决：

1.排名问题：每个部门按业绩排名
2.topN问题：找出每个部门排名前N的员工进行奖励

功能：

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

2）不减少原表的行数

基本语法：

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

‹窗口函数›的位置，可以放以下两种函数：

1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2） 聚合函数，如sum, avg, count, max, min等

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

1.专用窗口函数rank

e.g.

如果我们想在每个班级内按成绩排名，得到下面的结果。

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

注：partition by用来对表分组，order by子句的功能是对分组后的结果进行排序

partition by分组后的结果称为“窗口”，表示“范围”的意思

2.专用窗口函数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 班级表

结果：

案例

案例1：涉及到“面试经典排名问题”应区分rank(), dese_rank(), row_number()三者的区别。

案例2：topN问题

工作中会经常遇到这样的业务问题：

如何找到每个类别下用户最喜欢的产品是哪个？
如何找到每个类别下用户点击最多的5个商品是什么？

思路：分组取每组最大值、最小值，每组最大的N条（top N）记录

1.分组取每组最大值

若以简单的group by函数取最大值，无法取得最大值所在行的数据

以关联子查询得到

select *  from score as a  
where 成绩 = 
( select max(成绩)  from score as b  
where b.课程号 = a.课程号);

同理，每组最小值也由关联子查询得到。

2.每组最大的N条记录(topN)

问题：查找每个学生成绩最高的2个科目

思路：

1.看到“每个”要想到分组，此题可按姓名分组
2.分组后，按成绩降序排列，排在最前面的2个即为所求
3.分组排序后，不能减少原表的行数，所以需要用到窗口函数
4.为了不受并列成绩的影响，使用row_number专用窗口函数

步骤一：

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

步骤二：每个同学成绩最好的2个科目，就是要求的解。要提取出“ranking”值小于等于2的数据。

select *,               
row_number() over 
(partition by 姓名                                              
order by 成绩 desc) as ranking from 成绩表 
where ranking ‹=2;

此时运行SQL查询时会报错，因为sql书写顺序与运行顺序不一样。在运行where语句时ranking列并没有出现，此时需要用到子查询。

select * from 
(select *, 
row_number() over 
(partition by 姓名 
order by 成绩 desc) as ranking  from 成绩表) as a 
where ranking ‹= 2;

举一反三：

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

二、聚合函数

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

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

聚合函数sum()在窗口函数中，是对本身及以上数据的累加结果。

其余函数也为针对自身记录、以及自身记录之上的所有数据进行计算。

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

三、窗口函数的移动平均

e.g.用聚合函数avg的窗口函数举例说明：

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

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

适用于：在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。

总结：

一.注意事项

partition子句可以省略，省略就是不指定分组，结果如下，只是按成绩由高到低进行了排序：

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

二.总结

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

‹窗口函数›的位置，可以放以下两种函数：

1） 专用窗口函数，比如rank, dense_rank, row_number等

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

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

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

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

3.注意事项

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

4.窗口函数使用场景

1）经典top N问题

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

2）经典排名问题

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

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

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

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

方法2，使用关联子查询