开窗函数详解（MySQL\SQL Server）（三）Frame 子句部份

整个开窗函数中，Frame是最复杂的。
先看定义

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

frame_extent:
    {frame_start | frame_between}

frame_between:
    BETWEEN frame_start AND frame_end

frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING
}

CURRENT ROW
当是rows关键字的时候 当前行
当是RANGE关键字的时候 是和当前值排序字段一样值的行。

UNBOUNDED PRECEDING 分区窗口开始
UNBOUNDED FOLLOWING 分区窗口结束

expr PRECEDING
当是rows 关键字的时候 是以当前行为基准向前偏移几行
当是RANGE关键字的时候 是以当前行排序字段减去expr 值 >= 此值的行

expr FOLLOWING
当是rows 关键字的时候 是以当前行为基准向后偏移几行
当是RANGE关键字的时候 是以当前行排序字段减去expr 值 <= 此值的行

先看rows 与 range的区别

 with cte1 as
 (
   select 1 as id, 100 as num from dual
   union all
   select 1 as id ,110 as num from dual
   union all
   select 2 as id,150
 )
 
 select *,sum(num)over(order by id rows current row)
 from cte1 

 with cte1 as
 (
   select 1 as id, 100 as num from dual
   union all
   select 1 as id ,110 as num from dual
   union all
   select 2 as id,150
 )
 
 select *,sum(num)over(order by id RANGE current row)
 from cte1 

上述两个语句 当id =1时候的区别。

| expr PRECEDING
| expr FOLLOWING
expr 还可以是日期增量
使用详细见这里： https://www.modb.pro/db/406125

当窗口函数 没有指定order by 的时候，窗口范围默认是分区全部行

 with cte1 as
 (
   select 1 as id, 100 as num from dual
   union all
   select 1 as id ,110 as num from dual
   union all
   select 2 as id,150
 )
 
 select *,
 sum(num)over(),
 sum(num) over(partition by id)
 from cte1 

当窗口函数指定了order by 窗口范围默认是 分区开始到当前行。

 with cte1 as
 (
   select 1 as id, 100 as num from dual
   union all
   select 1 as id ,160 as num from dual
   union all
   select 2 as id,150
 )
 
 select *,
 sum(num)over(order by id)
 from cte1 

以下函数，即使指定了frame子句，也无效。作用范围始终为全分区

CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

 with cte1 as
 (
   select 1 as id, 100 as num from dual
   union all
   select 1 as id ,160 as num from dual
   union all
   select 2 as id,150
 )
 
 select *,
ROW_NUMBER() over(partition by id rows current row)
 from cte1 

另外当order by 的字段存在null的时候，窗口范围 也有特殊的情况，这种情况很少使用这里我就不讲了，官方文档有详细的例子。

参考：
https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html