银川网站开发公司电话/佛山快速排名seo

一.什么是窗口函数

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

二.窗口函数的使用

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

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

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

• 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
• 聚合函数，如sum. avg, count, max, min等

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

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

• 同时具有分组和排序的功能
• 不减少原表的行数

三.专用窗口函数

3.1 各种专用窗口函数

rank()、dense_rank()、row_number()、...

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

3.2 各种专用窗口函数的区别

这三个函数区别在于对排名并列情况的输出结果不同。

rank()函数：如果有并列名次的行，会占用下一名次的位置。即排序数字不连续，比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank()函数：如果有并列名次的行，不占用下一名次的位置。即排序数字连续，比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

row_number()函数：不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

3.3 专用窗口函数的常见案例

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

面试topN问题

• 如何找到每个类别下用户最喜欢的产品是哪个？
• 如果找到每个类别下用户点击最多的5个商品是什么？

排名问题

• 每个部门按业绩来排名。

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

面试TOP N问题，灵活应用子查询，可将窗口函数子句放入FROM子句中进行分组排序

/*top N问题 SQL 模板*/
select *
from (select *,row_number() over(partition by<用于分组的列名> order by<用于排序的列名> )as <设置别名>from 表名) as <表的别名>
where <设置别名> <= n;

四.聚合窗口函数

4.1各种聚合函数作窗口函数

sum() avg() 、count() 、max() 、min()、...

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

4.2 各种聚合窗口函数的区别

上图可以看到各种聚合窗口函数对成绩的输出结果

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

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

比如0005号后面的聚合窗口函数结果是：学号0001~0005五人成绩的总和、平均、计数及最大最小值。如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

4.3 聚合窗口函数的常见案例

案例：业务问题：按照雇员编号升序排列，查找薪水的累计和（累计薪水）。其中累计薪水是前N个当前员工( 结束时间 = '9999-01-01')的薪水的累计和，其他以此类推。

“薪水表”中记录了员工发放的薪水。包含雇员编号，薪水、起始时间、结束时间。其中，薪水是指该雇员在起始时间到结束时间这段时间内的薪水。当前员工是指结束时间 = '9999-01-01'的员工（该公司业务：没有离职的员工，用这个值表示）。

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

上表为确诊人数表，包含日期和该日期对应的新增确诊人数。

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

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

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

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

/*累计求和，用sum。*/
sum(列名) over (order by ‹用于排序的列名›)
/*累计求平均值，用avg。*/
avg(列名) over (order by ‹用于排序的列名›)

所以，我们可以得出“累计求”问题的万能模板是：

select 列1,列2,..., <聚合窗口函数> over (order by ‹用于排序的列名›) as 累计值的别名 
from 表名;

五.窗口函数的移动值

5.1 窗口函数移动值的书写及理解

‹窗口函数› over ( order by ‹用于排序的列名› rows <移动值> preceding ) 

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

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

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

5.2 窗口函数的移动应用场景

通过row...preceding关键字调整作用范围，在以下场景中非常适用：

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

六.窗口函数的注意事项

• partition子句可以省略，省略就是不指定分组。
• ‹窗口函数›的位置，可以放以下两种函数：专用窗口函数和聚合函数。
• 窗口函数原则上只能写在select子句中
• 专用窗口函数，比如rank()、 dense_rank()、row_number()等专用窗口函数中的（）不可以省略不写，且不填写内容。
• 聚合函数作窗口函数时，比如sum()、 avg()、 count()、 max()、 min()等聚合窗口函数中（）必须填写所要求的列名。