sqlserver累计求和函数_SQL基础--SQL⾼级功能
⼀.窗⼝函数有什么⽤？
在每组内排名，⽐如下⾯的业务需求：
在⽇常⼯作中，经常会遇到需要在每组内排名
排名问题：每个部门按业绩来排名
topN问题：出每个部门排名前N的员⼯进⾏奖励
⾯对这类需求，就需要使⽤sql的⾼级功能窗⼝函数了。
⼆.什么是窗⼝函数？
窗⼝函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进⾏实时分析处理。
窗⼝函数的基本语法如下：
‹窗⼝函数›
语法中‹窗⼝函数›的位置，可以放以下两种函数：
1） 专⽤窗⼝函数，包括后⾯要讲到的rank, dense_rank, row_number等专⽤窗⼝函数。
2） 聚合函数，如sum. avg, count, max, min等
窗⼝函数原则上只能写在select⼦句中。
因为窗⼝函数是对where或者group by⼦句处理后的结果进⾏操作，所以窗⼝函数原则上只能写在select⼦句中
接下来，就结合实例，给⼤家介绍⼏种窗⼝函数的⽤法。
【经典排名问题】专⽤窗⼝函数rank
例如下图，是班级表中的内容
如果我们想在每个班级内按成绩排名，得到下⾯的结果。
得到上⾯结果的sql语句代码如下：
select
我们来解释下这个sql语句⾥的select⼦句。
rank函数的用法要求是“每个班级内按成绩排名”，这句话可以分为两部分：
1）每个班级内：按班级分组
partition by⽤来对表分组。在这个例⼦中，所以我们指定了按“班级”分组（partition by 班级）
partition by⽤来对表分组
2）按成绩排名
order by⼦句的功能是对分组后的结果进⾏排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这⼀列排order by⼦句的功能是对分组后的结果进⾏排序
序，加了desc关键词表⽰降序排列。
通过下图，我们就可以理解partiition by（分组）和order by（在组内排序）的作⽤了。
窗⼝函数具备了我们之前学过的group by⼦句分组的功能和order by⼦句排序的功能。那么，为什么还要⽤窗⼝函数呢？
group by分组汇总后改变了表的⾏数，⼀⾏只有⼀个类别。⽽partiition by和rank函数不会减少原表中的⾏数。例如这是因为，group by分组汇总后改变了表的⾏数，⼀⾏只有⼀个类别。⽽partiition by和rank函数不会减少原表中的⾏数
下⾯统计每个班级的⼈数。
那么为什么叫“窗⼝”函数呢？这是因为partition by分组后的结果称为“窗⼝”，这⾥的窗⼝不是我们家⾥的门窗，⽽是表⽰“范围”的意思。
简单来说，窗⼝函数有以下功能：
1）同时具有分组和排序的功能
2）不减少原表的⾏数
3）语法如下：
‹窗⼝函数›
三.其他窗⼝函数
专⽤窗⼝函数rank, dense_rank, row_number有什么区别呢？
举个例⼦，就能清楚了
select
从上⾯的结果可以看出：
rank函数
也就是如果有并列名次的⾏，会占⽤下⼀名次的位置。
rank函数：这个例⼦中是5位，5位，5位，8位，也就是如果有并列名次的⾏，会占⽤下⼀名次的位置。
也就是如果有并列名次的⾏，不占⽤下⼀名次的位置。
dense_rank函数
dense_rank函数：这个例⼦中是5位，5位，5位，6位，也就是如果有并列名次的⾏，不占⽤下⼀名次的位置。
也就是不考虑并列名次的情况。
row_number函数
row_number函数：这个例⼦中是5位，6位，7位，8位，也就是不考虑并列名次的情况。
最后，需要强调的⼀点是：在上述的这三个专⽤窗⼝函数中，函数后⾯的括号不需要任何参数，保持()空着就可以。
topN问题】
【topN问题
每组最⼤的N条记录
现有“成绩表”，记录了每个学⽣各科的成绩。表内容如下。问题：查每个学⽣成绩最⾼的2个科⽬
【解题思路】
1. 看到“每个”了，肯定是要进⾏分组，因为是每个学⽣，所以这⾥按姓名分组
2. 按姓名分组后，把成绩降序排列，取最前⾯两个
3. 因为不能减少原⾏数，所以⽤窗⼝函数进⾏分组，并且为了不受并列成绩影响，使⽤row_number专⽤窗⼝函数
【解体步骤】
步骤⼀：按姓名分组(partiotion by 姓名)、并按成绩降序排列(order by 成绩 desc)，套⼊窗⼝函数的语法
select
运⾏结果如下
步骤⼆：如上表黄⾊框内的数据，每个同学成绩最好的2个科⽬，就是要求的解。
那么⼀下就会想到只要提取出“ranking”值⼩于等于2的数据就可以了。因此，只需要在上⼀步的slq语
句⾥加⼊条件字句where就可以注意这样是错误的
了，注意这样是错误的
⼀定要注意命令的运⾏顺序，运⾏到”where ranking › 2”的时候，因为select字句还没有被执⾏，因此select中的“ranking”列还没⼀定要注意命令的运⾏顺序，
有出现，从⽽导致报错。
因此，在这⾥⽤⼦查询，也就是把第⼀步得到查询结果作为⼀个新的表，sql语句如下：
select
得到结果：
【如何在每个组⾥⽐较？】
还⽤上个例⼦中的表，查单科成绩⾼于该科⽬平均成绩的学⽣名单？
【解题思路】
1. 看到“每个”，就要分组。
2. 使⽤聚合窗⼝函数(求平均值avg)，将每门课的平均成绩求出以后，然后出⼤于⽐平均成绩的数据。
3. 因为要⽤原值和平均值⽐较，因此要求分组后不能减少表的⾏数，所以⽤partition by分组。
【解题步骤】
第1步，聚合函数avg()作为窗⼝函数，将每⼀科⽬成绩的平均值求出。sql语句如下：
select
结果如下
这⾥第2步，如上表，按科⽬分组后各科⽬的平均分已经计算出，那么，是不是只需要在上⼀步的slq语句⾥加⼊条件字句where就可以了？这⾥再次注意命令的执⾏顺序！
和上个例⼦⼀样⽤⼦查询，也就是把第⼀步得到查询结果作为⼀个新的表，sql语句如下：
select
运⾏结果如下：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论