SQLServer三种开窗函数详细用法
(福利推荐:你还在原价购买阿里云服务器?现在阿里云0.8折限时抢购活动来啦!4核8G企业云服务器仅2998元/3年,立即抢购>>>:9i0i.cn/aliyun)
一,开窗函数的语法
开窗函数的语法为:over(partition by 列名1 order by 列名2 ),括号中的两个关键词partition by 和order by 可以只出现一个。over() 前面是一个函数,如果是聚合函数,那么order by 不能一起使用。
二,从聚合开窗函数sum(score) over(partition by name )讲起
实不相瞒我看一眼就会了(假的,其实这种又臭又长的字实在懒得看)
sum(score) over(partition by name )
sum()是聚合函数,其实我聚合函数还没学明白,当 sum()函数 后面跟上 over()以后,由sum聚合函数就成为了开窗函数。
over() 括号里面就是定义窗口的内容了,partition 是分区,分组的意思。partition by 就是根据某个字段分组。
所以sum(score) over(partition by name ) ,就是先根据 name 分组(如图),当前面加了sum(score)后就把根据name分组后的,每个(组)窗口里面的字段 score进行求和操作。
select *,sum(score) over(partition by name) sum窗口函数举例 from kchs -- 为了简单就只有两个字段,name和score
聚合函数同样需要对数据进行排序,但不会显示排名结果。会将当前名次的数据 与 排在这之前的所有数据 依次做相应的计算。
执行语句:
select *, sum(score) over (order by id) as 累加求和 from kchs
拓展一下:
一,很多聚合函数都可以用作窗口函数的运算,如SUM、AVG、MAX、MIN、COUNT。
二,和gropu by 不同的是窗口函数会生成多行,而不是想group by 一样只有一行
三,开窗函数之first_value,last_value,lead,lag
first_value:是在窗口里面取到第一个值
first_value(score) over( partition by name)as first_score , 根据name分区(组),取score列的第一个值
last_value:是在窗口里面取到最后一个值
last_value(score) over(partition by name) as last_score --根据name分区(组),取score列的最后一个值
lead 是取当前行的上 N 条数据,并且可以设置默认值
lead(score,1,0) over(partition by name ) as lead_score --根据name分区(组),score列当前行的上面N行,,如果没有就为默认值0
lag 是取当前行的下 N 条数据,并且可以设置默认值
lag(score,1,0) over(partition by name ) as lag_score --根据name分区(组),score列当前行的下面N行,如果没有就为默认值0
四,排名开窗函数ROW_NUMBER、DENSE_RANK、RANK
row_number ()是为每组的行设置一个连续的递增的数字(123456)
ROW_NUMBER() over( partition by name order by score asc)as ROW_NUMBER_score
rank()是排名,也为每一组的行生成一个序号,如果有相同的值会生成相同的序号,并且接下来的序号是不连序的。例如:有三个人并列第一名,第四名序号为四(111456)
rank() over(partition by name order by score asc) as RANK_score
DENSE_RANK()和RANK()类似,不同的是如果有相同的序号,那么接下来的序号不会间断。例如:有三个人并列第一,第四名序号为2(111234)
DENSE_RANK() over(partition by name order by score asc) as DENSE_RANK_score
注意:
一,排名开窗函数可以单独使用ORDER BY 语句,也可以和PARTITION BY同时使用。
二,ORDER BY 指定排名开窗函数的顺序,在排名开窗函数中必须使用ORDER BY语句。
三,PARTITION BY用于将结果集进行分组,开窗函数应用于每一组。
到此这篇关于SQLServer三种开窗函数详细用法的文章就介绍到这了,更多相关SQLServer 开窗函数内容请搜索程序员之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持程序员之家!
相关文章
由于系统错误 126 (SQL Server),指定驱动程序无法加载
今天下午在给客户安装用友T3标准版时碰到一个非常棘手的问题,MSDE2000数据库和用友T3软件都安装好了,但是打开系统管理的时候提示:AUTOMATION错误,然后将数据库彻底卸载干净,用友通T3软件也彻底卸载干净,重新安装了一下,仍然是这个情况2021-03-03SQLServer2016 sa登录失败(错误代码18456)
18456错误是因密码或用户名错误而使身份验证失败并导致连接尝试被拒或者账户被锁定无法sa登录,本文就来介绍一下解决方法,感兴趣的可以了解一下2023-09-09
最新评论