文档中心

ROWS和RANGE ​

功能描述 ​

ROWS和RANGE关键字为每一行定义一个窗口（一组物理或逻辑行），用于计算函数结果，然后将该函数应用于窗口中的所有行。窗口从上到下在查询结果集或分区中移动。

  • ROWS窗口由物理行构成。
  • RANGE窗口由逻辑偏移量构成。
  • 若完全忽略opt_win_range，则默认的窗口范围为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

窗口计算规则 ​

ROWS ​

Window|ASC/DESCASC窗口计算规则DESC窗口计算规则
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING窗口开始于分组第一行，结束于分组最后一行同ASC一致
ROWS [BETWEEN] UNBOUNDED PRECEDING [AND CURRENT ROW]窗口开始于分组第一行，结束于当前行同ASC一致
ROWS BETWEEN UNBOUNDED PRECEDING AND value_expr PRECEDING窗口开始于分组第一行，结束于当前行前value_expr行同ASC一致
ROWS BETWEEN UNBOUNDED PRECEDING AND value_expr FOLLOWING窗口开始于分组第一行，结束于当前行后value_expr行同ASC一致
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING窗口开始于当前行，结束于分组最后一行同ASC一致
ROWS [BETWEEN CURRENT ROW AND] CURRENT ROW窗口开始于当前行，结束于当前行同ASC一致
ROWS BETWEEN CURRENT ROW AND value_expr FOLLOWING窗口开始于当前行，结束于当前行后value_expr行同ASC一致
ROWS BETWEEN value_expr PRECEDING AND UNBOUNDED FOLLOWING窗口开始于当前行前value_expr行，结束于分组最后一行同ASC一致
ROWS [BETWEEN value_expr] PRECEDING [AND CURRENT ROW]窗口开始于当前行前value_expr行，结束于当前行同ASC一致
ROWS BETWEEN value_expr1 PRECEDING AND value_expr2 PRECEDING窗口开始于当前行前value_expr1行，结束于当前行前value_expr2行同ASC一致
ROWS BETWEEN value_expr1 PRECEDING AND value_expr2 FOLLOWING窗口开始于当前行前value_expr1行，结束于当前行后value_expr2行同ASC一致
ROWS BETWEEN value_expr FOLLOWING AND UNBOUNDED FOLLOWING窗口开始于当前行后value_expr行，结束于分组最后一行同ASC一致
ROWS BETWEEN value_expr1 FOLLOWING AND value_expr2 FOLLOWING窗口开始于当前行后value_expr1行，结束于当前行后value_expr2行同ASC一致
ROWS UNBOUNDED PRECEDING窗口开始于分组第一行，结束于当前行同ASC一致
ROWS CURRENT ROW窗口开始于当前行，结束于当前行同ASC一致
ROWS value_expr PRECEDING窗口开始于当前行前value_expr行，结束于当前行同ASC一致
ROWS BETWEEN CURRENT ROW AND value_expr PRECEDING无效无效
ROWS BETWEEN value_expr FOLLOWING AND CURRENT ROW无效无效
ROWS BETWEEN value_expr1 FOLLOWING AND value_expr2 PRECEDING无效无效
ROWS UNBOUNDED FOLLOWING无效无效
ROWS value_expr FOLLOWING无效无效

RANGE ​

Window|ASC/DESCASC窗口计算规则DESC窗口计算规则
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGWHERE expr BETWEEN first_value AND last_valueWHERE expr BETWEEN last_value AND first_value
RANGE [BETWEEN] UNBOUNDED PRECEDING [AND CURRENT ROW]WHERE expr BETWEEN first_value AND current_valueWHERE expr BETWEEN current_value AND first_value
RANGE BETWEEN UNBOUNDED PRECEDING AND value_expr PRECEDINGWHERE expr BETWEEN first_value AND current_value-value_exprWHERE expr BETWEEN current_value+value_expr AND first_value
RANGE BETWEEN UNBOUNDED PRECEDING AND value_expr FOLLOWINGWHERE expr BETWEEN first_value AND current_value+value_exprWHERE expr BETWEEN current_value-value_expr AND first_value
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGWHERE expr BETWEEN current_value AND last_valueWHERE expr BETWEEN last_value AND current_value
RANGE [BETWEEN CURRENT ROW AND] CURRENT ROWWHERE expr = current_valueWHERE expr = current_value
RANGE BETWEEN CURRENT ROW AND value_expr FOLLOWINGWHERE expr BETWEEN current_value AND current_value+value_exprWHERE expr BETWEEN current_value-value_expr AND current_value
RANGE BETWEEN value_expr PRECEDING AND UNBOUNDED FOLLOWINGWHERE expr BETWEEN current_value-value_expr AND last_valueWHERE expr BETWEEN last_value AND current_value+value_expr
RANGE [BETWEEN value_expr] PRECEDING [AND CURRENT ROW]WHERE expr BETWEEN current_value-value_expr AND current_valueWHERE expr BETWEEN current_value AND current_value+value_expr
RANGE BETWEEN value_expr1 PRECEDING AND value_expr2 PRECEDINGWHERE expr BETWEEN current_value-value_expr1 AND current_value-value_expr2WHERE expr BETWEEN current_value+value_expr2 AND current_value+value_expr1
RANGE BETWEEN value_expr1 PRECEDING AND value_expr2 FOLLOWINGWHERE expr BETWEEN current_value-value_expr1 AND current_value+value_expr2WHERE expr BETWEEN current_value-value_expr2 AND current_value+value_expr1
RANGE BETWEEN value_expr FOLLOWING AND UNBOUNDED FOLLOWINGWHERE expr BETWEEN current_value+value_expr AND last_valueWHERE expr BETWEEN last_value AND current_value-value_expr
RANGE BETWEEN value_expr1 FOLLOWING AND value_expr2 FOLLOWINGWHERE expr BETWEEN current_value+value_expr1 AND current_value+value_expr2WHERE expr BETWEEN current_value-value_expr2 AND current_value-value_expr1
RANGE UNBOUNDED PRECEDING
（与RANGE [BETWEEN] UNBOUNDED PRECEDING [AND CURRENT ROW]等价）		WHERE expr BETWEEN first_value AND current_valueWHERE expr BETWEEN current_value AND first_value
RANGE CURRENT ROW
（与RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING等价）		WHERE expr = current_valueWHERE expr = current_value
RANGE value_expr PRECEDING
（与RANGE [BETWEEN value_expr] PRECEDING [AND CURRENT ROW]等价）		WHERE expr BETWEEN current_value-value_expr AND current_valueWHERE expr BETWEEN current_value AND current_value+value_expr
RANGE BETWEEN CURRENT ROW AND value_expr PRECEDING无效无效
RANGE BETWEEN value_expr FOLLOWING AND CURRENT ROW无效无效
RANGE BETWEEN value_expr1 FOLLOWING AND value_expr2 PRECEDING无效无效
RANGE UNBOUNDED FOLLOWING无效无效
RANGE value_expr FOLLOWING无效无效

ORDER BY排序为单字段 ​

相当于给opt_win_order中的expr加WHERE限定条件，即WHERE expr BETWEEN a AND b构成了一个逻辑窗口，此窗口在expr包含的行上滑动求值。a和b的值可结合opt_win_range窗口求值确定。order_by_clause子句为ASC和DESC，a和b的求值可能有所不同。
假设分组排序后第一行的值为first_value，最后一行的值为last_value，当前行的值为current_value：

  • UNBOUNDED PRECEDING = first_value
  • UNBOUNDED FOLLOWING = last_value
  • CURRENT ROW = current_value
  • value_expr PRECEDING = current_value -/+ value_expr
  • value_expr FOLLOWING = current_value +/- value_expr

说明：

若排序指定DESC。

  • 窗口为value_expr PRECEDING则current_value + value_expr。
  • 窗口为value_expr FOLLOWING则current_value - value_expr。

ORDER BY排序为多字段 ​

仅允许下述开窗。

  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  • RANGE BETWEEN CURRENT ROW AND CURRENT ROW
  • RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED

示例 ​

ROWS ​

sql
SQL> CREATE TABLE over_test(name VARCHAR(20),age INT);

SQL> INSERT INTO over_test VALUES('a',15)('e',10)('f',21)('c',15)('d',9)('b',9);

SQL> SELECT name,age,COUNT(age) OVER(PARTITION BY age ORDER BY age ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM over_test ORDER BY age;

NAME | AGE | EXPR1 | 
------------------------------------------------------------------------------
b| 9 | 2 |
d| 9 | 1 |
e| 10 | 1 |
c| 15 | 2 |
a| 15 | 1 |
f| 21 | 1 |

RANGE ​

sql
SQL> SELECT name,age,COUNT(age) OVER(PARTITION BY age ORDER BY age RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM over_test ORDER BY age;

NAME | AGE | EXPR1 | 
------------------------------------------------------------------------------
b| 9 | 2 |
d| 9 | 2 |
e| 10 | 1 |
c| 15 | 2 |
a| 15 | 2 |
f| 21 | 1 |