本题要求实现函数输出n行数字金字塔。_SQL高级功能-窗口函数

一.定义：

窗口函数也就叫OLAP函数，可以对数据库数据进行实时分析处理

二.基本语法

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

三.分类

1.专用窗口函数

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

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 班级表;

2.聚合函数

如sum. avg, count, max, min等，聚合函数在窗口函数中，是对自身记录、及位于自身记录以上的数据进行计算得到结果

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

例如：
select *, sum(成绩) over (order by 学号)  as  current_sum
from 班级表;

3.窗口函数的移动平移

用rows和preceding这两个关键字，是“之前几行”的意思，也就是得到的结果是自身记录及前几行的统计计算

想要计算当前行与前n行（共n+1行）的平均时，只要调整rows…preceding中间的数字即可

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

四.作用

1.功能

具有分组(partition by)和排序(order by)功能，不减少原表行数(group by分组后改变表的行数)

2.应用

排名问题，topN问题，每个组中比较问题，累计求和等

五.注意事项

1. partition by可以省略，当不需要分组时

2.窗口函数是对where或者group by子句处理后的结果进行操作，

原则上只能写在select子句中

3.上述的3个专用函数后面的括号中不需要任何参数，空着即可

4.聚合函数作为窗口函数，函数后的括号需要填写相应列名

六.案例应用

1. 面试经典排名问题

下图是"班级"表中的内容，记录了每个学生所在班级，和对应的成绩

现在需要按成绩来排名，如果两个分数相同，那么排名要是并列的。正常排名是1，2，3，4，但是现在前3名是并列的名次，排名结果是：1，1，1，2。

【本题考点】

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

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

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

2.面试经典topN问题

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

现有“成绩表”，记录了每个学生各科的成绩。表内容如下。问题：查找每个学生成绩最高的2个科目

【本题考点】

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

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

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

select * 
from (select *, row_number() over (partition by 姓名 order by 成绩 desc) 
as ranking from 成绩表) as a 
where ranking ‹= 2;

查询结果

3.累计求和：聚合函数作窗口函数的应用

下表为确诊人数表，包含日期和该日期对应的新增确诊人数，按照日期进行升序排列，查找日期、确诊人数以及对应的累计确诊人数。

select 日期,确诊人数, sum(确诊人数) over (order by 日期) 
as 累计确诊人数 
from 确诊人数表;

查询结果

【本题考点】

对于“累计”问题，要想到用聚合函数作为窗口函数。比如累计求和，用sum。

sum(列名) over (order by ‹用于排序的列名›)

累计求平均值，用avg

avg(列名) over (order by ‹用于排序的列名›)

4.如何在每组里比较？

可以有两种方法：

1）使用窗口函数来实现

2）使用关联子查询

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

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