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

1. 什么是窗口函数？（---MySQL 8以上才有该函数---）

1） 窗口函数有什么用？

日常工作中，时常遇到需要在每组内排名，比如：

1.1 排名问题：每个部门按业绩排名

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

面对以上或类似需要，需使用SQL高级功能：窗口函数。

2） 什么是窗口函数？

窗口函数，也叫OLAP（online analytical processing，联机分析处理），可以对数据库数据进行实时分析处理。

基本语法如下：

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

以上（窗口函数）都有哪些？

1.1 专用窗口函数，如rank, dense_rank, row_number等专用窗口函数

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

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

2. 如何使用窗口函数

1）专用窗口函数rank

创建如下名为class的表：

问题：想在每个班级内部排名

以班级“1”为例，该班成绩“95”排在第一位，成绩“83”排在第四位，排序的SQL语句为：

解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分：

1）每个班级内：按班级分组

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

2）按成绩排名

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

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

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

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

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

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

2）不减少原表的行数

3）语法如下：

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

3. 其他专用窗口函数

专用窗口函数rank, dense_rank, rou_number

上述三个函数后面的括号不需要任何参数，保持空着就可以。

rank：如果有并列名次的行，会占用下一名次的位置。

dense_rank 如果有并列名次的行，不占用下一名次的位置。

row_number 不考虑并列名次的情况。

1）案例：面试经典排名问题

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

SQL语句：

select *,
dense_rank() over (order by 成绩 desc) as dense_ranking
from class;

2) 案例：面试经典topN问题

每个类别下用户最喜欢的产品是哪个？

每个类别下用户点击最多的5各商品是什么？

以score表为例：

① 分组取每组最大值：按课程号分组取成绩最大值所在行的数据。

② 使用关联子查询来实现同样目的

③ 分组取每组最小值 min

3) 每组最大的N条记录

案例：创建如下成绩表score1，查找每个学生成绩最高的2个科目

*****分析*****
1.1 每个学生---》分组
1.2 但为了取出前2的成绩，不能用group by
1.3 所以需要用窗口函数
1.4 此处自己原来使用rank()排序，若成绩各不相同时，没有问题，但是，若成绩相同，
会使得输出行数有误，因此采用row_number()，dense_rank()也会出现rank()相同的问题。
1.5 所以可先求出姓名分组，成绩排序的表，然后再在该表中取出各学生成绩的前两名
1.6 牢记SQL语句执行顺序，select最后执行

***** 该题可为topN问题的模板***** (where 排名 <= N)

4. 聚合函数作为窗口函数

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

案例：以创建的score1表为例

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

1.1 上述表格中平均成绩的求出未分组，比如班级，所以平均成绩为至上而下累加除以累加次数；

1.2 此处问题提到了单科成绩平均分，所以必须分组，窗口函数中有分组功能的为partition by，此处自己通过SQL语句发现无需排序，有无order by顺序都为数-英-语。

*****查找每个组里大于平均值的数据，两种方法*****
1.1 上述窗口函数
1.2 使用关联子查询

5. 窗口函数的移动平均

以class表为例：

Preceding 在….前

此时平均值为 现有行与前两行的平均值，如：学号0002平均成绩为（0001+0002）/2；学号0003平均成绩为（0001+0002+0003）/3。

*****该窗口函数使用场景*****
在公司业绩名单排名中，可以通过移动平均，直观的查看到与相邻名次业绩的平均、求和等统计数据。

6. 总结

1） partition子句可以省略，即不指定分组

2） 窗口函数

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

上述窗口函数位置可以放以下两种函数：

专用窗口函数：rank, dense_rank, row_number等

聚合函数：sum, avg, count, max, min等

3） 窗口函数具有分组和排序的功能；不减少原表的行数，常用来组内排名

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

5） 窗口函数使用场景

1.1 topN问题

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

1.2 排名问题

业务要求组内排名（每个部门内按业绩排名）

1.3 组内比较的问题

如查找每个组内大于平均值的数据，两种方法：

使用窗口函数实现

使用关联子查询