虚谷文档 v1.0.3

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


分析函数 ​

📄字数 6.5K
👁️阅读量 加载中...

一、功能描述 ​

分析函数可以单独使用并得到函数计算结果，也可以结合窗口子句使用，分析函数的输入值是从SELECT语句结果集中的一个或多个“窗口”中获取所得。

分析函数在一组与当前行相关的表行之间执行计算，这与使用聚合函数可以完成的计算类型类似。但与常规的聚合函数不同的是，使用分析函数不会将分组中的多行聚合为单行，而是对分组中每行执行计算。

分析函数一般作为查询的目标，可参见查询语法target_list章节中的target_el。

二、语法格式 ​

三、参数说明 ​

  • analytic_function：分析函数的名称，请参阅分析函数。
  • arguments：分析函数的参数。
  • analytic_clause：分析子句。
    • opt_win_parti：分组子句，定义统计组的分组规则。
    • opt_win_order：排序子句，定义统计分区内的排序规则。
    • opt_win_range：窗口子句，定义统计的窗口范围。

3.1 opt_win_parti ​

表示将数据按指定字段expr进行分组，每个分组独立计算分析函数。
例如：

sql
SUM(salary) OVER (PARTITION BY department_id)

表示在每个department_id分组中，分别累加工资。

  • 使用PARTITION BY子句根据一个或多个值（表字段）将查询结果集划分为多个组。如果省略此子句，则该函数将查询结果集的所有行作为单个组处理。
  • 同一个查询中可指定多个分析函数，每个函数都具有相同或不同的分组键。
  • opt_win_parti参数的有效值是常量、列、函数表达式或涉及其中任何一个表达式的表达式。

3.2 opt_win_order ​

ORDER BY expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]...表示将每个分区内的行排序，分析函数使用排序后的数据进行计算。

  • ASC：升序
  • DESC：降序
  • NULLS FIRST | NULLS LAST：指定NULL在排序中的位置

例如：

sql
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

表示在每个部门中按工资从高到低进行排名。

使用opt_win_order指定分组内的数据排序方式。对于所有分析函数，可以将分组中的值按多个键排序，每个键由opt_win_order参数定义，每个键定义一个排序序列。使用opt_win_order排序后分组内多行具有相同的值时：

  • DENSE_RANK、NTILE和RANK对每一行返回相同的结果。
  • ROW_NUMBER为每一行分配一个不同的值，该值取决于排序，排序结果无法保证全局唯一时，该值是不确定的。
  • 对于所有其他分析函数，结果取决于窗口规格。如果使用RANGE关键字指定一个逻辑窗口，那么该函数将为每一行返回相同的结果。如果使用ROWS关键字指定物理窗口，则结果是不确定的。

opt_win_order使用时有如下限制：

在分析函数中使用时，opt_win_order必须接受一个表达式expr，位置和列别名是无效的。若使用位置或列别名，则opt_win_order按照堆存储的排序返回结果。

使用RANGE关键字的分析函数可以在其中使用多个排序键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 FOLLOWING

在ORDER中，上述四个窗口边界之外的窗口边界只能有一个排序键，此限制不适用于ROWS关键字指定的窗口边界。

3.3 opt_win_range ​

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

3.3.1 opt_win_range指定ROWS ​

  • value_expr是物理偏移量，它必须是常量或值为非负数的表达式。
  • 若value_expr是起点的一部分，那它必须在终点之前对行求值。

3.3.2 opt_win_range指定RANGE ​

  • value_expr是逻辑偏移量，它必须是常量或值为非负数的表达式、时间、INTERVAL时间值或文字常量。
  • 若value_expr值为一个数字，那opt_win_order中的expr必须为数字或时间类型。
  • 若value_expr为一个间隔值，那opt_win_order中的expr必须是一个时间类型。

注意

当使用RANGE时，value_expr必须与ORDER BY字段类型兼容

四、窗口帧 ​

对于每一行，其分区内都有一组行，称为窗口帧（Window Frame），其确定了分析函数计算时哪些行参与运算，通过ROWS和RANGE来确定窗口的范围。

4.1 BETWEEN...AND...结构 ​

字段含义
UNBOUNDED PRECEDING窗口起点为分区第一行
value_expr PRECEDING往前value_expr行/排序值小于/大于当前行value_expr的行
UNBOUNDED FOLLOWING窗口终点为分区最后一行
value_expr FOLLOWING往后value_expr行/排序值大于/小于当前行value_expr的行
CURRENT ROW当前行

提示

若不定义窗口帧，则默认的窗口范围为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

4.2 ROWS ​

ROWS窗口由物理行构成，是基于行号的物理偏移，可以精确控制"向前几行"或"向后几行"。

计算规则 ​

WindowASC窗口计算规则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无效无效

4.3 RANGE ​

RANGE窗口由逻辑偏移量构成，按排序字段的值来决定窗口边界。

计算规则 ​

WindowASC窗口计算规则DESC窗口计算规则
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING最小值与最大值之间最大值与最小值之间
RANGE [BETWEEN] UNBOUNDED PRECEDING [AND CURRENT ROW]最小值与当前值之间当前值与最大值之间
RANGE BETWEEN UNBOUNDED PRECEDING AND value_expr PRECEDING最小值与当前值-value_expr之间当前值+value_expr与最大值之间
RANGE BETWEEN UNBOUNDED PRECEDING AND value_expr FOLLOWING最小值与当前值+value_expr之间当前值-value_expr与最大值之间
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING当前值与最大值之间最小值与当前值之间
RANGE [BETWEEN CURRENT ROW AND] CURRENT ROW等于当前值等于当前值
RANGE BETWEEN CURRENT ROW AND value_expr FOLLOWING当前值与当前值+value_expr之间当前值-value_expr与当前值之间
RANGE BETWEEN value_expr PRECEDING AND UNBOUNDED FOLLOWING当前值-value_expr与最大值之间最小值与当前值+value_expr之间
RANGE [BETWEEN value_expr] PRECEDING [AND CURRENT ROW]当前值-value_expr与当前值之间当前值与当前值+value_expr之间
RANGE BETWEEN value_expr1 PRECEDING AND value_expr2 PRECEDING当前值-value_expr1与当前值-value_expr2之间当前值+value_expr2与当前值+value_expr1
RANGE BETWEEN value_expr1 PRECEDING AND value_expr2 FOLLOWING当前值-value_expr1与当前值+value_expr2之间当前值-value_expr2与当前值+value_expr1
RANGE BETWEEN value_expr FOLLOWING AND UNBOUNDED FOLLOWING当前值+value_expr与最大值之间最小值与当前值-value_expr之间
RANGE BETWEEN value_expr1 FOLLOWING AND value_expr2 FOLLOWING当前值+value_expr1与当前值+value_expr2之间当前值-value_expr2与当前值-value_expr1
RANGE UNBOUNDED PRECEDING（与RANGE [BETWEEN] UNBOUNDED PRECEDING [AND CURRENT ROW]等价）最小值与当前值之间当前值与最大值之间
RANGE CURRENT ROW（与RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING等价）等于当前值等于当前值
RANGE value_expr PRECEDING（与RANGE [BETWEEN value_expr] PRECEDING [AND CURRENT ROW]等价）当前值-value_expr 与当前值之间当前值与当前值+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无效无效

五、示例 ​

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

SQL> INSERT INTO tab_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) as cnt FROM tab_over_test ORDER BY age;

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

SQL> CREATE TABLE tab_sal_info(id INT IDENTITY(1,1),name VARCHAR,deptno INT,sal NUMERIC(10,2));

SQL> INSERT INTO tab_sal_info VALUES
         (DEFAULT,'张三',10,5000)(DEFAULT,'李四',10,5500)(DEFAULT,'王五',10,4500)
         (DEFAULT,'赵六',10,4800)(DEFAULT,'陈七',10,5500)(DEFAULT,'刘八',20,3000)
         (DEFAULT,'李九',20,3500)(DEFAULT,'周十',20,3800)(DEFAULT,'张一',20,2300)
         (DEFAULT,'李二',20,3500)(DEFAULT,'吴二',20,3800)(DEFAULT,'张二',20,3800)
         (DEFAULT,'刘二',20,4000)(DEFAULT,'周二',20,4300);

-- RANK排名
SQL> SELECT *,RANK() OVER(PARTITION BY deptno ORDER BY sal) rk FROM tab_sal_info ORDER BY deptno,rk;

+----+------+--------+------+----+
| ID | NAME | DEPTNO | SAL  | RK |
+----+------+--------+------+----+
| 3  | 王五 | 10     | 4500 | 1  |
| 4  | 赵六 | 10     | 4800 | 2  |
| 1  | 张三 | 10     | 5000 | 3  |
| 5  | 陈七 | 10     | 5500 | 4  |
| 2  | 李四 | 10     | 5500 | 4  |
| 9  | 张一 | 20     | 2300 | 1  |
| 6  | 刘八 | 20     | 3000 | 2  |
| 10 | 李二 | 20     | 3500 | 3  |
| 7  | 李九 | 20     | 3500 | 3  |
| 11 | 吴二 | 20     | 3800 | 5  |
| 8  | 周十 | 20     | 3800 | 5  |
| 12 | 张二 | 20     | 3800 | 5  |
| 13 | 刘二 | 20     | 4000 | 8  |
| 14 | 周二 | 20     | 4300 | 9  |
+----+------+--------+------+----+