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

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：那么就是当前分区的所有行都包含在框架中，因为所有行都会成为当前行的相同行。【特别要注意最后一句的意思】