matlabif函数多个条件并列_SQL高级:窗口函数+聚合函数

一、什么是窗口函数？

（一）窗口函数有什么用？

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

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

2）topN问题：找出每个部门排名前N的员工进行奖励； 面对这类需求，就需要使用sql的高级功能窗口函数了。

（二）什么是窗口函数？ 窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。 窗口函数的基本语法如下：

[^<窗口函数>基本语法]: <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

那么语法中的<窗口函数>都有哪些呢？

<窗口函数>的位置，可以放以下两种函数：

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

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

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

（三）窗口函数用法示例

1、专用窗口函数rank

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

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

以班级“1”为例，这个班级的成绩“95”排在第1位，这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内，按成绩排名。

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

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

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

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

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

2）按成绩排名：

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

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

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？ 这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

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

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

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

2）不减少原表的行数；

3）语法如下：

[^<窗口函数>语法]: <窗口函数> over (partition by <用于分组的列名> order by<用于排序的列名>)

二、案例经典排名问题

【题目】

下面是‘class’表中的内容，记录了每个学生所在班级，和对应的成绩。

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

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

【解题思路】

1.涉及到排名问题，可以使用窗口函数

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 class;

得到结果：

从上面的结果可以看出：

rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

​ row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

这三个函数的区别如下：

最后，需要强调的一点是：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

根据题目要求的排名规则，这里我们使用dense_rank函数，座椅，最终的sql语句是：

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

得到的结果是：

【本题考点】

1、考察如何使用窗口函数

2、专用窗口函数排名的区别：rank，dense_rank，row_number

【举一反三】

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

例子，编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

实现代码：

select 成绩,
           dense_rank() over (order by 成绩 desc) as Rank1
from score;

三、案例：面试经典topN问题

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

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

如果找到每个类别下用户点击最多的5个商品是什么？

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

面对该类问题，如何解决呢？

下面我们通过score表的例子来给出答案。

成绩表是学生的成绩，里面有学号（学生的学号），课程号（学生选修课程的课程号），成绩（学生选修该课程取得的成绩）

每组取每组最大值

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

我们可以使用分组（group by）和汇总函数得到每个组里的一个值（最大值，最小值，平均值等）。但是无法得到成绩最大值所在行的数据。

select 课程号, max(成绩) as 最大成绩
from score
group by 课程号;

得到结果是：

我们可以使用关联子查询来实现：

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

得到结果是：

上面查询结果课程号“0001”有2行数据，是因为最大成绩80有2个

分组取每组最小值

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

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

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

得到的结果是：

每组最大的N条记录

案例：查询各科成绩前两名的记录

【解题思路】

1.看到问题中要查“每个”学生最高的成绩。还记得我们之前课程里讲过的吗？当有“每个”出现的时候，就要想到是要分组了。

这里是“每个学生”，结合表的结构，是按学生“姓名”来分组。

2.将表按学生姓名分组后，把成绩按降序排列，排在最前面的2个就是我们要找的“成绩最高的2个科目”。

3.现在分组后，需要排序，又不减少原表的行数，这种功能自然想到是窗口函数。

4.使用哪个专用窗口函数？

为了不受并列成绩的影响，使用row_number专用窗口函数：

​ row_number函数：也就是不考虑并列名次的情况，比如前3名是并列的名次，排名是正常的1， 2， 3， 4.

【解题步骤】

步骤一：按学号分组(partiotion by 学号)、并按成绩降序排列(order by 成绩 desc)，套入窗口函数的语法，就是下面的sql语句：

select *,
         row_number() over (partition by 学号 order by 成绩 desc) as ranking
from score;

步骤二：如上表红色框内的数据，每个同学成绩最好的2个科目，就是要求的解。

想得到这个解，只要提取出“ranking”值小于等于2的数据就可以了。那么，只需要在上一步的slq语句里加入条件字句where就可以了

select *,
         row_number() over (partition by 学号 order by 成绩 desc) as ranking
 from score
 where ranking <= 2;

但这样写sql会报错:

为什么会报错？

这是因为sql的书写顺序和运行顺序的缘故。在运行顺序中，select子句是最后被运行的。

当明白了运行顺序以后，就知道错误的原因了：运行到”where ranking › 2”的时候，因为select字句还没有被执行，因此select中的“ranking”列还没有出现，从而导致报错。

解决方法是什么呢？

这种情况就可以用子查询，也就是把第一步得到查询结果作为一个新的表，sql语句如下：

select * from
          (select *,
                   row_number() over (partition by 学号 order by 成绩 desc) as ranking
                    from score) as a
                    where ranking <= 2;

得到的结果是：

【本题考点】

1.主要考查对窗口函数的灵活使用。

2.在筛选过程中，非常容易因为子查询问题报错，本题也考察了对子查询的熟练运用。

3.本题间接考察了对sql语句执行顺序的熟悉程度。

【举一反三】

经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。

本题的sql语句修改下（将where字句里的条件修改成N），就可以成为这类问题的一个万能模板，遇到这类问题往里面套就可以了：

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 class;

得到的结果是：

有发现什么吗？我单独用sum举个例子：

如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算，现在再结合刚才得到的结果（下图），是不是理解起来容易多了？

比如0005号后面的聚合窗口函数结果是：学号0001~0005五人成绩的总和、平均、计数及最大最小值。

如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

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

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

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

【题目】

现在有“score1”表，记录了每个学生各科的成绩，表内容如下：

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

【解题思路】

1.'查找单科成绩高于该科目平均成绩'，也就是在“每个”科目里比较。还记得我们之前课程里讲过的吗？当有“每个”出现的时候，就要想到是要分组了。

能实现“分组”功能的sql有两种，一是group by字句，另一个是窗口函数的partition by。

2.使用聚合窗口函数(求平均值avg)，将每门课的平均成绩求出以后，然后找出大于比平均成绩的数据。

这就要求分组后不能减少表的行数。

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

所以这里我们使用使用窗口函数的partition by。

【解题步骤】

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

sql select *, avg(成绩) over (partition by 科目) as avg_score from score1;

运行结果如下：

第2步，如上表，按科目分组后各科目平均分已经计算出，接下来只要筛选出成绩大于平均分的数据即可。 那么，只需要在上一步的slq语句里加入条件字句where就可以了

select *,
         avg(成绩) over (partition by 科目) as avg_score
         from score1 where 成绩 > avg_score;

这样写sql会报错：

我们要牢记sql的书写顺序和运行顺序。在运行顺序中，select子句是最后被运行的。

当明白了运行顺序以后，就知道错误的原因了：运行到”where ranking › 2”的时候，因为select字句还没有被执行，因此select中的“ranking”列还没有出现，从而导致报错。

解决方法是什么呢？

这种情况就可以用子查询，也就是把第一步得到查询结果作为一个新的表，sql语句如下：

select * from
          (select *,
           avg(成绩) over (partition by 科目) as avg_score
            from score1) as b
           where 成绩 > avg_score;

运行结果如下：

【本题考点】

1.主要考查对窗口函数的灵活使用。

2.在筛选过程中，非常容易因为子查询问题报错，本题也考察了对子查询的熟练运用。

3.本题间接考察了对sql语句执行顺序的熟悉程度。

【举一反三】

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

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

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

六、窗口函数的移动平均

这里我们直接用聚合函数avg的窗口函数举例说明：

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

运行结果如下：

仔细看上面的窗口函数中，用了rows和preceding这两个关键字，是“之前~行”的意思，上面的句子中，是之前2行。也就是得到的结果是自身记录及前2行的平均。

例如：学号0004学生的current_avg，是自己和前2位同学的平均，即0002，0003，0004三位同学成绩的平均，其他数据的情况也一样，下图非常直观的可以看到计算过程：

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

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

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

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

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

七、总结

(一)注意事项

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

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

得到结果：

(二)总结

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，使用关联子查询