psql where里有自定义函数慢_走进SQL：⑦窗口函数

整体内容的思维导图：

一、定义

窗口函数也称为OLAP函数（OLAP是online analytical processing,联机分析处理的简称），意思是对数据库进行实时分析处理。

Q;为什么叫“窗口”函数？

A:因为partition by分组后的结果称为“窗口”，表示“范围”的意思。

二、基本语法

窗口函数基本语法

注意：

• 窗口函数对where或者group by子句处理的结果进行分析处理，所以原则上只能写在select子句中。
• partition子句可以省略，省略就是不指定分组。

三、功能

1. 同时具有分组(partition by)和排序(order by)的功能
2. 不减少原表的行数，所以经常用在组内排名

Q:为什么用窗口函数分组而不用group by？

A:因为group by分组汇总后改变了表的行数，一行只有一个类型；而partition by和rank函数不会减少原表的行数。举个例子方便理解，例如下面统计每个班级的人数：

统计每个班级的人数

四、类型

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

1. 专用窗口函数，包括rank,dense_rank,row_number等
2. 聚合窗口函数，包括sum,avg,count,max,min等

1.专用窗口函数

• 包括：rank,dense_rank,row_number等
• 注意：a.这三个专用窗口函数后面的括号不需要任何参数，保持()空着就行。b.涉及到排名的问题，都可以使用窗口函数来解决（经典排名问题和经典topN问题）

（1） rank函数

例子：在每个班级内按成绩排名

解题代码：

-- 在每个班级内按成绩排名

分组排序的计算过程：

分组排序的计算原理

（2） 三个函数的区别——经典排序问题

• rank函数 如果有并列名次的行，会占用下一名次的位置
• dense_rank函数 如果有并列名次的行，不占用下一名次的位置
• row_number函数 不考虑并列名次的情况

三个函数排序的区别

（3） 经典top N问题

• 释义：每组的最大的N条记录。这类问题设计到“既要分组，又要排序 ”的情况，要能想到用窗口函数来实现。（下面用例子的解题步骤来解释万能模板的结构）
• 万能模板：

经典top N问题的万能模板

• 例子：查找每个学生成绩最高的2个科目

解题步骤：

1. 套入窗口函数的语法，按姓名分组(partition by 姓名)、并按成绩降序排列(order by 成绩 desc)，得到每个学生的所有成绩排名情况。
2. 用where子句提取出“排名”值小于等于2的数据。但是会报错，因为where子句先于select子句运行，where得不到“排名”的数据。
3. 用子查询解决。使用子查询语句，把(1)得到的查询结果作为一个新的表，然后用(2)中的where子句。

注意：别忘了子查询最后要给新表命名

代码：

-- 查找每个学生成绩最高的2个科目

2.聚合函数

• 包括：sum,avg,count,max,min等。
• 用法：和专用窗口函数完全相同，只需把聚合函数写在窗口函数的位置即可。
• 注意：函数后面的()不能为空。需要指定聚合的列名。
• 作用：可以在每一行的数据里直观地看到，截止到本行数据，统计数据是多少（最大值最小 值等）。同时可以看出每一行数据，对整体统计数据的影响。
• 计算过程：求和、平均、计数、最大最小值，都是针对自身记录、以及自身记录之上的所有数据进行计算的结果。 以sum作为窗口函数进行原理分析：

sum作为窗口函数的计算原理

• 例子：查询每个组里大于平均值的数据，可以有两种方法：窗口函数和关联子查询

代码：

-- 查询每个组里大于平均值的数据

查询结果：

两种方法查询每个组里大于平均值的数据

3.窗口函数的移动平均

• 使用：rows ... preceding ...内容是数字
• 作用：调整窗口函数的作用范围，范围是当前行与前...行之间。在以下场景中非常适用：在公司业绩名单排名中，可以通过移动平均。直观地查看与相邻名次业绩的平均、求和等统计数据。
• 例子：计算当前行和上两行，3位同学的平均成绩

代码：

-- 计算当前行和上两行，3位同学的平均成绩

计算过程：上面的句子中rows 2 preceding代表“之前2行”，也就是得到的结果是自身记录及前2行的平均。例如学号0004学生的current_avg，是自己和前两位同学的平均，即0002,0003,0004三位同学成绩的平均，其他数据也一样

窗口函数的移动平均的计算过程

查询结果：

查询结果

五、使用场景

1. 经典排名问题 业务需求“在组内排名”，比如：每个部门按业绩来排名
2. 经典topN问题 找出每个部门排名前N的员工进行奖励（有万能模板）
3. 在每个组里比较的问题 比如查找每个组里大于平均值的数据，可以有两种方法：①使用窗口函数 ②使用关联子查询