今天给大家分享一下 Oracle database 21c 中分析函数的语法增强，包括 WINDOW 子句、GROUPS 子句以及 EXCLUDE 子句。

数据初始化

本文介绍的案例将会使用以下示例表：

create table emp (
  empno    number(4) constraint pk_emp primary key,
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2) );

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;

create table t1 (id number, value number);

insert into t1 (id, value) values (1, 1);
insert into t1 (id, value) values (2, 2);
insert into t1 (id, value) values (3, 3);
insert into t1 (id, value) values (4, 3);
insert into t1 (id, value) values (5, 4);
insert into t1 (id, value) values (6, 6);
insert into t1 (id, value) values (7, 6);
insert into t1 (id, value) values (8, 7);
insert into t1 (id, value) values (9, 7);
insert into t1 (id, value) values (10, 8);
commit;

WINDOW 子句

在以前的版本中，窗口选项属于分析函数调用参数的一部分。以下查询使用 FIRST_VALUE 分析函数返回了每个部门中的最低月薪：

select empno,
       deptno,
       sal,
       first_value(sal)
over (partition by deptno order by sal) as lowest_in_dept
from   emp;

     EMPNO     DEPTNO        SAL LOWEST_IN_DEPT
---------- ---------- ---------- --------------
7934         10       1300           1300
7782         10       2450           1300
7839         10       5000           1300
7369         20        800            800
7876         20       1100            800
7566         20       2975            800
7788         20       3000            800
7902         20       3000            800
7900         30        950            950
7654         30       1250            950
7521         30       1250            950
7844         30       1500            950
7499         30       1600            950
7698         30       2850            950

从 Oracle 21c 开始，窗口选项可以使用单独的 window 子句进行定义，然后在分析函数调用中使用定义好的变量。例如：

select empno,
       deptno,
       sal,
       first_value(sal) over w1 as lowest_in_dept
from   emp
window w1 as (partition by deptno order by sal);

     EMPNO     DEPTNO        SAL LOWEST_IN_DEPT
---------- ---------- ---------- --------------
7934         10       1300           1300
7782         10       2450           1300
7839         10       5000           1300
7369         20        800            800
7876         20       1100            800
7566         20       2975            800
7788         20       3000            800
7902         20       3000            800
7900         30        950            950
7654         30       1250            950
7521         30       1250            950
7844         30       1500            950
7499         30       1600            950
7698         30       2850            950

这种法语方式意味着多个分析函数可以使用相同的窗口子句。下面的示例增加了一个 RANK 分析函数，基于员工在部门内的月薪从低到高进行排名：

select empno,
       deptno,
       sal,
       first_value(sal) over w1 as lowest_in_dept,
       rank() over w1 as sal_rank_in_dept
from   emp
window w1 as (partition by deptno order by sal);

     EMPNO     DEPTNO        SAL LOWEST_IN_DEPT SAL_RANK_IN_DEPT
---------- ---------- ---------- -------------- ----------------
7934         10       1300           1300                1
7782         10       2450           1300                2
7839         10       5000           1300                3
7369         20        800            800                1
7876         20       1100            800                2
7566         20       2975            800                3
7788         20       3000            800                4
7902         20       3000            800                4
7900         30        950            950                1
7654         30       1250            950                2
7521         30       1250            950                2
7844         30       1500            950                4
7499         30       1600            950                5
7698         30       2850            950                6

我们可以定义和使用多个不同的窗口选项。以下示例查询了每个部门中的最低月薪和最高月薪，查询中使用了两次 FIRST_VALUE 分析函数，每次指定的窗口选项不同：

select empno,
       deptno,
       sal,
       first_value(sal) over w1 as lowest_in_dept,
       first_value(sal) over w2 as highest_in_dept
from   emp
window w1 as (partition by deptno order by sal),
       w2 as (partition by deptno order by sal desc);

     EMPNO     DEPTNO        SAL LOWEST_IN_DEPT HIGHEST_IN_DEPT
---------- ---------- ---------- -------------- ---------------
7934         10       1300           1300            5000
7782         10       2450           1300            5000
7839         10       5000           1300            5000
7369         20        800            800            3000
7876         20       1100            800            3000
7566         20       2975            800            3000
7788         20       3000            800            3000
7902         20       3000            800            3000
7900         30        950            950            2850
7521         30       1250            950            2850
7654         30       1250            950            2850
7844         30       1500            950            2850
7499         30       1600            950            2850
7698         30       2850            950            2850

我们也可以定义一个通用的 WINDOW 子句，然后在分析函数调用中扩展该变量：

select row_number () over w1 as row_order,
       sal,
avg(sal) over (w1 rows between unbounded preceding and current row) as avg_rolling,
avg(sal) over (w1 rows between unbounded preceding and unbounded following) as avg_all
from   emp
window w1 as (order by sal);

 ROW_ORDER        SAL AVG_ROLLING    AVG_ALL
---------- ---------- ----------- ----------
1        800         800 2073.21429
2        950         875 2073.21429
3       1100         950 2073.21429
4       1250        1025 2073.21429
5       1250        1070 2073.21429
6       1300  1108.33333 2073.21429
7       1500  1164.28571 2073.21429
8       1600     1218.75 2073.21429
9       2450  1355.55556 2073.21429
10       2850        1505 2073.21429
11       2975  1638.63636 2073.21429
12       3000  1752.08333 2073.21429
13       3000  1848.07692 2073.21429
14       5000  2073.21429 2073.21429

这种新的语法是一种查询转换（query transformation）。我们可以通过会话跟踪查看相关信息：

select value from v$diag_info where  name = 'Default Trace File';

VALUE
----------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_23984.trc

1 row selected.

执行语句的 10053 跟踪：

alter session set events '10053 trace name context forever';

select empno,
       deptno,
       sal,
       first_value(sal) over w1 as lowest_in_dept,
       first_value(sal) over w2 as highest_in_dept
from   emp
window w1 as (partition by deptno order by sal),
       w2 as (partition by deptno order by sal desc);

alter session set events '10053 trace name context off';

跟踪文件中以“Final query after transformations”开始的部分显示了执行查询转换之后实际执行的语句：

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "EMP"."EMPNO" "EMPNO",
"EMP"."DEPTNO" "DEPTNO",
"EMP"."SAL" "SAL",
       FIRST_VALUE("EMP"."SAL") OVER ( PARTITION BY "EMP"."DEPTNO" ORDER BY "EMP"."SAL"
         RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW ) "LOWEST_IN_DEPT",
       FIRST_VALUE("EMP"."SAL") OVER ( PARTITION BY "EMP"."DEPTNO" ORDER BY "EMP"."SAL" DESC
         RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW ) "HIGHEST_IN_DEPT"
FROM "TESTUSER1"."EMP" "EMP"

从以上结果可以看出，示例语句被重写成了 Oracle 21c 之前可以执行的语法。

GROUPS 子句

除了 ROWS 和 RANGE 关键字之外，新版本增加了 GROUPS 关键字，因此定义窗口子句的三个选项如下：

ROWS：以行为单位指定距离当前行的窗口，不考虑取值相同的更多数据行。排序字段数量不限。
RANGE：基于当前行的某个字段值指定一个取值范围。因此，在创建排序集合之前，系统不确定窗口包含多少行数据。此时需要有一个排序字段，而且字段类型能够支持加减运算，例如数字、日期或者时间间隔。
GROUPS：基于排序字段将数据分为多个组，等值行属于相同的组。当前行属于当前组，所以 preceding 或者 following 表示前面的组或者后面的组，而不是具体的行。与 RANGE 选项类似，在创建排序集合之前，系统不确定窗口包含多少行数据。排序字段数量不限。

以下查询比较了使用 ROWS、GROUPS 以及 RANGE 选项计算“between 1 preceding and current row”平均月薪的结果：

select row_number () over (order by sal) as row_order,
       sal,
avg(sal) over w1 as avg_rows,
avg(sal) over w2 as avg_groups,
avg(sal) over w3 as avg_range
from   emp
window w1 as (order by sal rows between 1 preceding and current row),
       w2 as (order by sal groups between 1 preceding and current row),
       w3 as (order by sal range between 1 preceding and current row);

 ROW_ORDER        SAL   AVG_ROWS AVG_GROUPS  AVG_RANGE
---------- ---------- ---------- ---------- ----------
1        800        800        800        800
2        950        875        875        950
3       1100       1025       1025       1100
4       1250       1175       1200       1250
5       1250       1250       1200       1250
6       1300       1275 1266.66667       1300
7       1500       1400       1400       1500
8       1600       1550       1550       1600
9       2450       2025       2025       2450
10       2850       2650       2650       2850
11       2975     2912.5     2912.5       2975
12       3000     2987.5 2991.66667       3000
13       3000       3000 2991.66667       3000
14       5000       4000 3666.66667       5000

AVG_ROWS 列返回了当前行和前一行的平均月薪。

AVG_GROUPS 列开始的结果和 AVG_ROWS 相同。但是对于第 4 行，存在一个等值行，第 4 行和第 5 行属于当前组，第 3 行属于前一组。所以返回的不是第 3 行和第 4 行的平均值，而是第 3 行、第 4 行以及第 5 行的平均值。

AVG_RANGE 列返回的是当前行对应的月薪。因为 RANGE 是按照数值范围，而不是行的数量指定窗口。“1 preceding”表示的是数值减 1。

下面是一个类似的查询。数据表 T1 中包含了间隔为 1 的数值，以及一些等值行和跳跃行，因为没有数字 5。

select row_number () over (order by value) as row_order,
value,
avg(value) over w1 as avg_rows,
avg(value) over w2 as avg_groups,
avg(value) over w3 as avg_range
from   t1
window w1 as (order by value rows between 1 preceding and current row),
       w2 as (order by value groups between 1 preceding and current row),
       w3 as (order by value range between 1 preceding and current row);

 ROW_ORDER      VALUE   AVG_ROWS AVG_GROUPS  AVG_RANGE
---------- ---------- ---------- ---------- ----------
1          1          1          1          1
2          2        1.5        1.5        1.5
3          3        2.5 2.66666667 2.66666667
4          3          3 2.66666667 2.66666667
5          4        3.5 3.33333333 3.33333333
6          6          5 5.33333333          6
7          6          6 5.33333333          6
8          7        6.5        6.5        6.5
9          7          7        6.5        6.5
10          8        7.5 7.33333333 7.33333333

AVG_ROWS 列返回了当前行和前一行的平均值。

AVG_GROUPS 列的第 3 行返回了不同的结果。第 3 行和第 4 行是等值行，它们是一个组，第 2 行是前一组。因此第 3 行和第 4 行的结果是 2、3、4 行的平均值。

AVG_RANGE 列和 AVG_GROUPS 列类似，但是遇到跳跃数值时不同。第 6 行和第 7 行是等值行，窗口范围从 6-1=5 开始，但是数据集中没有数字 5，所以第 6 行和第 7 行的结果只有当前行的值（6）。

EXCLUDE 子句

EXCLUDE 子句支持不同的选项，用于排除窗口内的某些数据行。

EXCLUDE NO OTHERS：不排除任何数据。这种方式等价于不指定 EXCLUDE 选项，因此是默认行为。
EXCLUDE CURRENT ROW：排除当前行。
EXCLUDE GROUP：排除当前组、当前行以及其他数值相同的其他数据行。
EXCLUDE TIES：排除数值和当前行相同的其他数据行，但是保留当前行。

以上排除选项都可以用于 ROWS、RANGE 以及 GROUPS 窗口定义。

以下查询针对每行数据计算前一行和后一行的平均值，但是不包含当前行：

select row_number () over w1 as row_order,
value,
avg(value) over (w1 rows between 1 preceding and 1 following exclude current row) as ex_current_row
from t1
window w1 as (order by value);

 ROW_ORDER      VALUE EX_CURRENT_ROW
---------- ---------- --------------
1          1              2
2          2              2
3          3            2.5
4          3            3.5
5          4            4.5
6          6              5
7          6            6.5
8          7            6.5
9          7            7.5
10          8              7
SQL>

以下查询针对每行数据计算前一行和后一行的平均值，但是不包含当前组。在窗口定义中使用 ROWS，同时在排除选项中使用组，可以返回一个有趣的结果：

select row_number () over w1 as row_order,
value,
avg(value) over (w1 rows between 1 preceding and 1 following exclude group) as ex_group
from t1
window w1 as (order by value);

 ROW_ORDER      VALUE   EX_GROUP
---------- ---------- ----------
1          1          2
2          2          2
3          3          2
4          3          4
5          4        4.5
6          6          4
7          6          7
8          7          6
9          7          8
10          8          7

以下查询基于 unbounded preceding 和 current row 范围计算移动平均值，同时排除了等值行，所以报告的范围和通常包含当前行的等值行不同：

select row_number () over w1 as row_order,
value,
avg(value) over (w1 range between unbounded preceding and current row exclude ties) as ex_ties
from t1
window w1 as (order by value);

 ROW_ORDER      VALUE    EX_TIES
---------- ---------- ----------
1          1          1
2          2        1.5
3          3          2
4          3          2
5          4        2.6
6          6 3.16666667
7          6 3.16666667
8          7          4
9          7          4
10          8        4.7

关于 Oracle 分析函数的语法和更多示例，可以参考官方文档。

ITPub博客

Oracle Database 21c 分析函数增强

数据初始化

WINDOW 子句

GROUPS 子句

EXCLUDE 子句

最新文章