SQL:聚合类窗口函数的preceding和following参数用法

目录

partition by 分组字段:

order by 排序字段

rows between 和range between 对比

unbound和current row


窗口函数:函数() over (partition by col1 order by col2)

聚合类窗口函数顾名思义,是指窗口函数中的函数部分为聚合函数,包括:

求和sum()、最大值max()、最小值min()、均值avg()、标准差std()等

这些函数在窗口上的使用规则都是类似的,我们以sum()求和函数为例来看一看

简单的数据示例

studentclassscore
11班90
21班80
32班96
42班85
52班70

partition by 分组字段:

partition by class 是指在班级内部进行sum()函数统计

partition by不设置,就是指在全部数据范围内进行sum函数统计

studentclassscoresum()over(partition by class )sum()over()
11班90170410
21班80170410
32班90240410
42班80240410
52班70240410

order by 排序字段

order by score 是指将分数从低到高排序(倒序的话:order by score desc)

studentclassscoresum(score)over(partition by class order by score desc )
11班9090
21班80170
32班9090
42班80170
52班70240

rows between 和range between 对比

当数据不重复时,这样写没有问题,但是如果数据重复,就会出现下面的问题

studentclassscoresum(score)over(order by score desc)
11班90180
32班90180
21班80340
42班80340
52班70410

 因为在排序时,同样是90分和80分的记录无法区分,所以只能全部加入结果。如果想要避免这样的问题就需要引入order by参数中的:

  • rows between 1 preceding and 2 following

含义:在当前行往前1行,往后2行,一共4行范围内进行计算

  • range between 1 preceding and 2 following 

含义:在当前行的数值往前1个数值,往后1个数值,进行计算,范围不一定是4行,因为可能会出现重复值

如果是简单的order by col,默认使用的事range between。所以说:如果存在重复值的时候,需要写成rows between。

unbound和current row

其中数值1和2可以替换成任意值,也可以直接使用unbounded和current row ,其中unbounded表示不做限制,current row 表示当前行

  • rows between unbounded preceding and unbounded following

含义:按照分组内全部行求和,不做任何限制

  • rows between unbounded preceding and current row

含义:从分组内排序的起始行到当前行

  • range between unbounded preceding and unbounded following 

含义:按照分组内全部行求和,不做任何限制

  • range between unbounded preceding and unbounded following 

含义:从分组内排序的起始行的值到当前行的值

由此可知,在上述案例中,如果我们想得到1、2班全部同学成绩从高到低排序的累计求和可以这样写:

sum(score)over (order by score desc rows between unbounded preceding and current row)

具体说明如下:

  1. 因为不需要对班级分组,所以partition by class不写
  2. order by score desc 表示按分数倒序排序
  3. rows between 是因为成绩中存在重复值,使用rows between可以保证和当前行的数值相同的行不会被计算进去
  4. unbounded preceding 表示从起始行开始
  5. current row 表示到当前行

实用价值

窗口函数的移动(proceding 和following)不仅可以实现上述累加求和的效果,在计算移动平均值的时候也非常有用,可以使用如下写法实现:

avg(求均值字段)over(order by date rows between 3 proceding and current row)

  • 3
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
回答: SQL窗口函数有一些高级用法。首先,窗口函数通常只能在SELECT子句中使用,并且窗口函数中的ORDER BY子句不会影响最终结果的排序,它只是用来确定窗口函数的计算顺序。其次,窗口函数可以使用GROUPING运算符来计算合计和小计。其中,ROLLUP是一种常用的GROUPING运算符,用于计算合计和小计。\[1\]\[2\] 窗口函数的语法如下: <窗口函数> OVER (ORDER BY <排序用列名> ROWS n PRECEDING) <窗口函数> OVER (ORDER BY <排序用列名> ROWS BETWEEN n PRECEDING AND n FOLLOWING) 这些语法可以用来指定窗口函数的排序方式和计算范围。例如,可以使用ROWS BETWEEN子句来指定窗口函数计算的范围,如"ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING"表示计算当前行及其前后一行的窗口函数值。\[2\]\[3\] 总之,SQL窗口函数的高级用法包括使用ORDER BY子句确定计算顺序,使用GROUPING运算符计算合计和小计,以及使用ROWS BETWEEN子句指定计算范围。这些用法可以帮助我们更灵活地使用窗口函数进行数据分析和计算。 #### 引用[.reference_title] - *1* [SQL学习——窗口函数](https://blog.csdn.net/luschka/article/details/114333624)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insert_down28v1,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* *3* [【SQL】(task5)SQL高级处理(窗口函数 | ROLLUP)](https://blog.csdn.net/qq_35812205/article/details/121425049)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insert_down28v1,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值