sql补0函数_SQL从0到1：窗⼝函数
本次内容包括：
什么是窗⼝函数？
如何使⽤窗⼝函数？
其他专⽤窗⼝函数
案例：⾯试经典问题
案例：⾯试经典topN问题
⼀、什么是窗⼝函数
1、定义
窗⼝函数，也称为OLAP函数（Online Analytical Processing，联机分析处理），可以对数据库的数据进⾏实时分析处理。
2、窗⼝函数有什么⽤？
在⽇常⼯作中，经常会遇到需要在每组内排名，⽐如以下业务需求：
排名问题：每个部门按业绩来排名 topN问题：出每个部门排名前N的员⼯进⾏奖励
⾯对这样的需求，就需要使⽤窗⼝函数
3、基本语法
<；窗⼝函数> over
(partition by <⽤于分组的列名>
order by <⽤于排序的列名>)
聚合函数，如<；窗⼝函数>的位置，可以放以下两种函数： 1. 专⽤窗⼝函数
专⽤窗⼝函数，包括rank，dense_rank，row_number等专⽤窗⼝函数 2. 聚合函数sum，avg，count，max，min等
select⼦句中。
由于窗⼝函数是对where或者group by⼦句处理后
处理后的结果进⾏操作，所以窗⼝函数原则上只能写在select⼦句
⼆、如何使⽤窗⼝函数？
1、专⽤窗⼝函数rank
以此表为例
如果需要按每个班级内成绩排名，得到下⾯的结果
以班级“1”为例，这个班级的成绩“95”排在第1位，这个班级的“83”排在第4位，对应的SQL语句如下
select *,rank() over(
partition by 班级
order by 成绩 desc) as ranking
from 成绩表
分析过程： 问题“每个班级内按成绩排名”可以拆分成两个部分：
1. 每个班级内：按班级分组
partition by⽤于对表分组
partition by⽤于对表分组。这个例⼦中⽤于分组的字段为“班级”
1. 按成绩排名
order by⽤于对分组后的结果进⾏排序
对分组后的结果进⾏排序。默认是升序（asc），加⼊关键字desc可指定为降序排列。
下图说明了partition by和order by的作⽤
2、为什么要⽤窗⼝函数？
问：group by和order by⼦句同样具有分组和排序的功能， 为什么还要⽤窗⼝函数？
rank函数不会减少原表中的⾏数
不会减少原表中的⾏数。
partition by和rank
rank函数的用法答：因为group by
group by分组汇总改变了表的⾏数
改变了表的⾏数，⼀⾏只有⼀个类别；⽽partition by
3、“窗⼝函数”这个名字的由来
partition by分组后产⽣的结果称为“窗⼝”，表⽰“范围”的意思。
4、窗⼝函数的功能
1. 同时具有分组和排序的功能
2. 不减少原表的⾏数
三、其他专⽤窗⼝函数
包括了rank，dense_rank，row_number函数
举例说明：
select *,rank()over(
order by 成绩 desc) as ranking,
dense_rank()over(
order by 成绩 desc) as dense_rank,
row_number()over(
order by 成绩 desc) as row_num #注意别名不可与窗⼝函数同名，否则报错
from 成绩表
得到的结果如下：
从结果可以看出：
当出现有并列名次的⾏，会占⽤下⼀个名次的位置。⽐如正常排名是1，2，3，4，但如 rank函数：例⼦中显⽰5、5、5、8。即 当出现有并列名次的⾏，会占⽤下⼀个名次的位置
果前三名是并列的话，结果会是1，1，1，4。
当出现有并列名次的⾏，不占⽤下⼀名次的位置。⽐如正常排名是1，2，3，4，dense_rank函数：例⼦中显⽰5、5、5、6。即 当出现有并列名次的⾏，不占⽤下⼀名次的位置
不考虑并列名次的情况。
如果前3名是并列的话，结果会是1，1，1，2。 row_number函数：这个例⼦中是5、6、7、8。也就是 不考虑并列名次的情况如果前3名是并列的话，排名是正常的1，2，3，4。
总结：这三个函数的区别是：
需要强调的⼀点是，在这三个专⽤窗⼝函数中，函数后⾯的括号不需要任何参数
函数后⾯的括号不需要任何参数，保持（）空着就可以了。
案例：经典排名问题
给出⼀个成绩表，⾥⾯记录了每个学⽣学号、所在班级和对应的成绩。
问：按成绩的⾼低进⾏排名（如果两个分数相同，排名是并列的）
解题思路：
1. 涉及排名问题，使⽤窗⼝函数
2. 分清楚窗⼝函数ran(),dense_rank(),row_number之间的区别
3. 正确选择所需的窗⼝函数
SELECT *,dense_rank() over(
ORDER BY 成绩 DESC)as 按成绩排名
from 成绩表
得到的结果是：
Tips：
涉及分数排名问题时，需选择dense_rank函数，因为相同的分数并列之后，下⼀个名次应该是下⼀个连续的整数值。案例：经典TopN问题
问题举例：
1. 如何到每个类别下⽤户最喜欢的产品是哪个？
2. 如何到每个类别下⽤户点击最多的5个商品是什么？
这类问题可以归类为：分组取每组最⼤值、最⼩值，每组最⼤的N条（Top N）记录。
以下⾯这个成绩表为例
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论