SQL基础--SQL高级功能

一.窗口函数有什么用？

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

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

面对这类需求，就需要使用sql的高级功能窗口函数了。

二.什么是窗口函数？

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

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

‹窗口函数› over

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

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

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

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

接下来，就结合实例，给大家介绍几种窗口函数的用法。

【经典排名问题】专用窗口函数rank

例如下图，是班级表中的内容

得到上面结果的sql语句代码如下：

select *,rank over as ranking from 班级表;

我们来解释下这个sql语句里的select子句。

要求是“每个班级内按成绩排名”，这句话可以分为两部分：

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

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

2）按成绩排名

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

通过下图，我们就可以理解partiition by（分组）和order by（在组内排序）的作用了。

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

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

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

2）不减少原表的行数

3）语法如下：

‹窗口函数› over

三.其他窗口函数

专用窗口函数rank, dense_rank, row_number有什么区别呢？

举个例子，就能清楚了

select *,rank over as ranking dense_rank over as dese_rank,row_number over as row_numfrom 班级表;

【解题思路】

1. 看到“每个”了，肯定是要进行分组，因为是每个学生，所以这里按姓名分组
2. 按姓名分组后，把成绩降序排列，取最前面两个
3. 因为不能减少原行数，所以用窗口函数进行分组，并且为了不受并列成绩影响，使用row_number专用窗口函数

【解体步骤】

步骤一：按姓名分组、并按成绩降序排列，套入窗口函数的语法

select *,row_number over as ranking from 成绩表;

运行结果如下

【如何在每个组里比较？】

还用上个例子中的表，查找单科成绩高于该科目平均成绩的学生名单？

【解题思路】

1. 看到“每个”，就要分组。
2. 使用聚合窗口函数，将每门课的平均成绩求出以后，然后找出大于比平均成绩的数据。
3. 因为要用原值和平均值比较，因此要求分组后不能减少表的行数，所以用partition by分组。

【解题步骤】

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

select *, avg over as avg_score from 成绩表;

结果如下

四.聚合函数

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

以下面的命令为例

select *,sum over as current_sum,avg over as current_avg,count over as current_count,max over as current_max,min over as current_min from 班级表;

我单独用sum运算过程进行说明：

每一行得到的结果，都是当前行和前面2行的平均（共3行）。想要计算当前行与前n行（共n+1行）的平均时，只要调整rows…preceding中间的数字即可。

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

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

由于这里可以通过preceding关键字调整作用范围，在以下场景中非常适用：

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

六.总结