pb9数据窗口中显示行数据与当前行区别_跟我一起零基础学会SQL——窗口函数篇...

1. 什么是窗口函数？

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

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

2. 如何使用窗口函数？

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

应用案例：

在每个班级内按成绩排名

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

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

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

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

2）按成绩排名

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

窗口函数有以下功能：

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

2）不减少原表的行数

例如下面统计每个班级的人数

3. 窗口函数有什么用？

3.1 专用窗口函数

专用窗口函数有：rank(), dense_rank(), row_number()，区别如下：

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

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

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

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

3.1.1案例：面试经典排名问题

对成绩表中的每个学生成绩根据所在的班级进行排名

请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”，所以应该使用窗口函数dense_rank()

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

3.1.2 案例：面试经典TopN问题

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

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

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

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

分组取每组最大值

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

方法一：使用group by

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

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

方法二：使用子查询

select * from 班级表as s1
where 成绩=(select max(成绩)from score as s2 where s1.课程号=s2.课程号)；

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

方法三：使用窗口函数dense_rank()

select * from
(select *,row_number() over (partition by 课程号 order by 成绩 desc )as ranking from 成绩表)as a 
where ranking =1;

TopN问题的sql万能模板如下：

select * from
 (select *,row_number() over (partition by 要分组的列名 order by要排序的列名 desc )as ranking from表名)as a  
where ranking <=N;

3.2 聚合函数作为窗口函数

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作为窗口函数

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

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算。

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

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

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

3.2.2 案例：每个组内比较问题

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

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

3.3 窗口函数的移动平均

案例：

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

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

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

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

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

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

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

4 注意事项

1. 专用窗口函数后面的括号不需要任何参数，保持()空着。
2. partition子句可省略，省略就是不指定分组。
3. 窗口函数原则上只能写在select子句中。