hive开窗函数边界值ROWS BETWEEN 和 RANGE BETWEEN区别

目录

一、概念

1.rows between ... and ...

2.range between ... and ...

二、语法

1.关键词含义

---

一、概念

1.rows between ... and ...

rows：指以行号来决定frame的范围，是物理意义上的行。

2.range between ... and ...

range：指以当前行在开窗函数中的值为根基，然后按照order by进行排序，最后根据range去加减上下界。是逻辑意义上的行。

二、语法

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

1.关键词含义

CURRENT ROW ：表示当前行
UNBOUNDED PRECEDING ：表示窗口的起始边界是窗口中第一行之前的无限行，即初始行
UNBOUNDED FOLLOWING ：表示窗口的起始边界是窗口中第一行之后的无限行，即末尾行
UNBOUNDED ：无边界
PRECEDING ：往前
FOLLOWING ：往后
(num) PRECEDING ：表示当前行之前的num行。
(num) FOLLOWING ：表示当前行之后的num行