分析函数
📄字数 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 ROWRANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGRANGE BETWEEN CURRENT ROW AND CURRENT ROWRANGE 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窗口由物理行构成，是基于行号的物理偏移，可以精确控制"向前几行"或"向后几行"。
计算规则
| Window | ASC窗口计算规则 | 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窗口由逻辑偏移量构成，按排序字段的值来决定窗口边界。
计算规则
| Window | ASC窗口计算规则 | 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 |
+----+------+--------+------+----+