窗口函数rows between 、range between的使用

最新推荐文章于 2025-02-28 23:01:15 发布

卖山楂啦prss

最新推荐文章于 2025-02-28 23:01:15 发布

阅读量3.6w

点赞数 56

分类专栏： # 数据分析之SQL

本文链接：https://blog.csdn.net/qq_42374697/article/details/115109386

版权

数据分析之SQL 专栏收录该内容

13 篇文章

订阅专栏

本文深入探讨了SQL中的窗口函数，特别是ROWS和RANGE子句的用法。通过多个示例，展示了如何利用rowsbetween来控制窗口范围，包括当前行、前后行的累加、移动平均等计算。同时，解释了RANGE关键字如何基于值而不是物理行来定义窗口，强调了两者在处理相同值时的不同。文章还提供了多个实用的窗口函数应用实例，帮助读者更好地理解和运用这些高级SQL技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 rows between

rows between 控制窗口函数的范围

使用rows between可以根据自己的需求任意的控制窗口函数的范围

UNBOUNDED ：不受控的，无限的；
PRECEDING ：在…之前；
FOLLOWING：在…之后；

rows between …… and ……

unbounded preceding 前面所有行
unbounded following 后面所有行
current row 当前行
n following  后面n行
n preceding  前面n行

rows between 例子

当前行与后面所有行的累加（分区内）

// 从当前行到最后的数据
sum(sales_volume) over(partition by id rows between current row and unbounded following) sum_sales

前面所有行与当前行的累加（分区内）

sum(sales_volume) over(partition by id rows between unbounded preceding and current row) sum_sales

当前行与后两行的累加（分区内）

sum(sales_volume) over(partition by id rows between current row and 2 following) sum_sales

前一行与当前行的累加（分区内）

sum(sales_volume) over(partition by id rows between 1 preceding and current row) sum_sales

前一行的值+当前行的值+后一行的值

sum(id) over(partition by category rows between 1 preceding and 1 following) rank from t

取当前行的前两条及后两条来参与计算，一般用于移动平均值

rows between 2 preceding and 2 following

按DEPTNO分区，ENAME顺序排列，统计从开始到结束的所有数据

这里相当于没有写ORDER BY

SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

按DEPTNO分区，ENAME顺序排列，统计从当前行到下一行数据

SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)

起始行到末尾行（分组范围内）

rows between unbounded preceding and unbounded following

除此之外，引用文章例子https://blog.csdn.net/sqlserverdiscovery/article/details/50497631

select *,
       count(*) over(partition by groupname 
	                     order by id 
						 rows between current row and 1 following) 当前行_后1行,
 
       count(*) over(partition by groupname 
	                     order by id 
						  rows between 1 preceding and current row ) 前1行_当前行,
 
       count(*) over(partition by groupname 
	                     order by id 
						 rows between current row and unbounded following) 当前行_最后1行,
 
       count(*) over(partition by groupname 
	                     order by id 
						  rows between unbounded preceding and current row) 第1行_当前行,
 
 
	   count(*) over(partition by groupname 
	                     order by id rows between 1 preceding and 1 following) 前1行_后1行,
 
	   count(*) over(partition by groupname 
	                     order by id 
						  rows between unbounded preceding and 1 following) 第1行_后1行,
 
	   count(*) over(partition by groupname 
	                     order by id 
						  rows between 1 preceding and unbounded following) 前1行_最后1行
from test

2 range between 按照列值限制窗口大小（在非条件表达式中等同于rows）

rows表示行，就是前n行，后n行

而range表示的是具体的值，比这个值小n的行，比这个值大n的行

range between是以当前值为锚点进行计算

比如

range between 4 preceding AND 7 following

表示：如果当前值为10的话就取前后的值在6到17之间的数据。

sum(close) range between 100 preceding and 200 following

则通过字段差值来进行选择。如当前行的 close 字段值是 200，那么这个窗口大小的定义就会选择分区中 close 字段值落在 100 至 400 区间的记录（行）。

窗口范围为该分区的第一行和该分区的最后一行，在非条件表达式中等同于ROWS

SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING  AND UNBOUNDED FOLLOWING)

窗口范围为该分区的第一行和本行，在非条件表达式中等同于ROWS

SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

窗口范围为该分区内小于本记录hire_date-365天的所有的薪资累计

SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND 365/*value_expr*/ PRECEDING)

UNBOUNDED PRECEDING 可以理解为第一行

参考：PostgreSQL窗口函数中 ROWS 和 RANGE 模式的区别

ROWS：是按物理行来进行窗口级别里再次进行范围选择的。
RANGE：是按逻辑行来进行窗口级别里再次进行范围选择的。RANGE时，相同行会被合并成同一条数据再进行计算，相同行窗口计算时的结果也是相同的。
是否是相同行，是根据ORDER BY排序时的结果决定的。
有ORDER BY时：同行是说在ORDER BY排序时不唯一的行。【即具有相同数值的行】
             不同行是说ORDER BY排序时具有不同的数值的行。
没有ORDER BY：那么就是当前分区的所有行都包含在框架中，因为所有行都会成为当前行的相同行。【特别要注意最后一句的意思】