大家好，我是只谈技术不剪发的 Tony 老师。今天给大家分享一下 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 分析函数的语法和更多示例，可以参考官方文档。
