sql 最小得10个值_SQL高级功能-窗口函数的使用

一、窗口函数有什么用？

主要解决每组内排名问题，分组的topN问题的需求，需要使用sql窗口函数。举例子：

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

窗口函数，也叫OLAP函数，可以对数据库数据进行实时分析处理。窗口函数的基本语法如下：

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

窗口函数类别：

1）专用窗口函数：rank，denserank，row_number等

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

窗口函数是对where和group by子句处理后的结果进行操作，按照SQL语句执行顺序，原则上窗口函数只能写在select子句中。

窗口函数与group by分组函数的区别：group by分组汇总改变了表的行数，一行只有一个类别。而partition by和rank函数不会减少原表中的行数。

举例子：要对每个班级内按成绩排名

班级表：

SQL语句：

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

得到效果如下：

总结起来，窗口函数有以下功能：

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

2)不减少原表行数

3)语法如下：

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

以班级成绩排名为例，rank()，denserank()，row_number()三个窗口函数区别如下：

1、rank函数，如果出现并列名次的行，会占用下一名次的位置，比如正常排名是1，2，3，4 前三名成绩相同是并列的名次，则结果是1，1，1，4

2、dense_rank函数：如果出现并列名次的行，不占用下一名次的位置，比如正常排名是1，2，3，4 前三名成绩相同是并列的名次，则结果是1，1，1，2

3、row_number函数：不考虑并列名次的情况，比如正常排名是1，2，3，4 前三名成绩相同是并列的名次，则结果还是1，2，3，4

题目：下列班级表中的内容，记录了每个学生所在班级，和对应的成绩，现在需要按成绩排名，如果两个分数相同，要求排名是并列的。

思路：由于要求对班级内按照成绩进行排名，涉及到排名，我们考虑用窗口函数。由于要求排名是并列的，所以我们考虑使用dense_rank()

Navicat图形化创建班级表如下：

窗口函数SQL语句如下：

结果如下：

二、如何解决topN问题

题目：topN问题：解决分组取每组最大值、最小值、每组最大的N条（top N）记录

以成绩表为例：

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

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

每组最大的N条记录
案例：现有“成绩表”，记录了每个学生各科成绩，查找每个学生成绩最高的两个科目

topN 的万能模板，即分组求出每组里面最大N个值：

三、聚合函数作为窗口函数

聚合函数区别于专用的窗口函数一个特点是括号里面带有对应的列名。聚合函数用法如下：

以班级表为例：

采用聚合函数作为窗口函数：

结果如下：

累计求和、求平均值、计数、最大值、最小值万能模板：

select *,sum(列名) |ava(列名)|count(列名)|max(列名)|min(列名) over (order by 排序列名) as 
累计计算列名 from 表名;

解题例子：

按照 日期进行升序排列，查找日期、确诊人数以及对应累计确诊人数。

结果：

组内进行比较

以成绩表为例：

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

1、需要以每个科目为类别进行分组，考虑到分组之后不能减少数据表的行数，我们用partition by窗口函数，而不是用group by。
2、求每科目的平均成绩，我们采用avg做窗口函数
3、高于该平均成绩的学生名单，我们选择成绩>avg值的结果进行展现。
4、借鉴前文topN问题的万能模板。

SQL语句如下：

结果：

窗口函数的移动平均：主要作用是直观看到与相邻名次业绩的平均、求和等统计数据。语法模板如下：

select *,avg(求平均值列名)over(order by 排序列名 rows N preceding)as 平均值名称 
from 表名;

总结：窗口函数分为两种类别，一是专用窗口函数，二是聚合函数。窗口函数主要功能是同时具有分组和排序的功能，并且不因为分组而减少原表的行数。原则上窗口函数只能写在select 子句中。主要的使用场景包括三种：一、经典topN问题，比如找出每个部门排名前N的员工进行奖励。二是经典组内排名问题，比如每个部门按业绩来排名。三是在每个组内进行比较的问题，比如查找每个组内大于平均值的问题等。窗口函数作为解决特定问题的有效手段应该背熟模板，灵活套用。