[译] Oracle Database 21c 中的 ANY_VALUE 聚合函数

原文地址：https://oracle-base.com/articles/21c/any_value-21c 原文作者：Tim Hall

ANY_VALUE 函数允许我们从 GROUP BY 子句中安全地删除列，以减少任何性能开销。

环境准备

本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟

本文中的示例需要提前创建以下表和数据：

SQL

1-- drop table emp purge;
2-- drop table dept purge;
3
4create table dept (
5  deptno number(2) constraint pk_dept primary key,
6  dname varchar2(14),
7  loc varchar2(13)
8) ;
9
10create table emp (
11  empno number(4) constraint pk_emp primary key,
12  ename varchar2(10),
13  job varchar2(9),
14  mgr number(4),
15  hiredate date,
16  sal number(7,2),
17  comm number(7,2),
18  deptno number(2) constraint fk_deptno references dept
19);
20
21insert into dept values (10,'ACCOUNTING','NEW YORK');
22insert into dept values (20,'RESEARCH','DALLAS');
23insert into dept values (30,'SALES','CHICAGO');
24insert into dept values (40,'OPERATIONS','BOSTON');
25
26insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);
27insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
28insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
29insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,null,20);
30insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
31insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,null,30);
32insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,null,10);
33insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,null,20);
34insert into emp values (7839,'KING','PRESIDENT',null,to_date('17-11-1981','dd-mm-yyyy'),5000,null,10);
35insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
36insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,null,20);
37insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,null,30);
38insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,null,20);
39insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,null,10);
40commit;

问题描述

我们需要返回一个部门列表，其中包含部门中的员工数量，因此我们可以使用 COUNT 聚合函数和 GROUP BY 子句来实现：

SQL

1select d.deptno,
2       d.dname,
3       count(e.empno) as employee_count
4from   dept d
5       left outer join  emp e on d.deptno = e.deptno
6group by d.deptno, d.dname
7order by 1;
8
9    DEPTNO DNAME          EMPLOYEE_COUNT
10---------- -------------- --------------
11        10 ACCOUNTING                  3
12        20 RESEARCH                    5
13        30 SALES                       6
14        40 OPERATIONS                  0
15
16SQL>

在 21C 以前的版本中，我们必须将所有非聚合列包含在 GROUP BY 条件中，否则将会报错且无法执行。由于我们并不关心 GROUP BY 中是否包含 DNAME 列，但是受制于语法必须得这样做，同时在 GROUP BY 中添加额外的列也会造成不必要的开销，为了避免这个问题，大家可能会使用 MIN 或者 MAX 函数。

SQL

1select d.deptno,
2       min(d.dname) as dname,
3       count(e.empno) as employee_count
4from   dept d
5       left outer join  emp e on d.deptno = e.deptno
6group by d.deptno
7order by 1;
8
9    DEPTNO DNAME          EMPLOYEE_COUNT
10---------- -------------- --------------
11        10 ACCOUNTING                  3
12        20 RESEARCH                    5
13        30 SALES                       6
14        40 OPERATIONS                  0
15
16SQL>
17
18
19select d.deptno,
20       max(d.dname) as dname,
21       count(e.empno) as employee_count
22from   dept d
23       left outer join  emp e on d.deptno = e.deptno
24group by d.deptno
25order by 1;
26
27    DEPTNO DNAME          EMPLOYEE_COUNT
28---------- -------------- --------------
29        10 ACCOUNTING                  3
30        20 RESEARCH                    5
31        30 SALES                       6
32        40 OPERATIONS                  0
33
34SQL>

这种写法使得我们可以从 GROUP BY 中拿掉 DNAME 列，但是新增了 MIN 或者 MAX 函数造成了新的开销。

解决方案：ANY_VALUE

在 Oracle 21c 中引入了 ANY_VALUE 聚合函数来解决这个问题。原理同样是使用 MIN 或者 MAX 函数的方式，只是以 ANY_VALUE 进行替代，它不进行任何类型的比较，而是显示它找到的第一个非 NULL 值，但是经过内部优化可以做到最大幅度减少聚合函数的开销。

SQL

1select d.deptno,
2       any_value(d.dname) as dname,
3       count(e.empno) as employee_count
4from   dept d
5       left outer join  emp e on d.deptno = e.deptno
6group by d.deptno
7order by 1;
8
9    DEPTNO DNAME          EMPLOYEE_COUNT
10---------- -------------- --------------
11        10 ACCOUNTING                  3
12        20 RESEARCH                    5
13        30 SALES                       6
14        40 OPERATIONS                  0
15
16SQL>

所以现在我们可以减少 GROUP BY 中附加列的开销，而不必添加 MIN 或 MAX 函数的开销。

注意事项

它具有不确定性，所以不要在预设场景外使用此函数。
数据量小的情况下，我们可能无法观测到性能的改进，但随着数据量的增加，GROUP BY 或者使用 MIN 和 MAX 函数的开销必然超过 ANY_VALUE。
由于 MIN 和 MAX 函数是有确定意义的，如果代码编写者当时仅出于从 GROUP BY 中排除非必要列，非代码编写者读代码时可能对于该写法无法理解其用意，但是 ANY_VALUE 函数是非确定性的，因此使用它对任何其他开发人员来说都是一个明确的信息，即您正在使用它将列从 GROUP BY 中删除。从支持的角度来看，这种额外的清晰度是一件好事。
ANY_VALUE 函数支持 ALL 和 DISTINCT 关键字，但它们没有任何功能。
表达式中的 NULL 值被忽略，因此 ANY_VALUE 将返回它找到的第一个非 NULL 值。如果表达式中的所有值都是 NULL，那么将返回 NULL 值。
它支持除 XMLTYPE、ANYDATA、LOB、文件或集合数据类型之外的任何数据类型，这会导致 ORA-00932 错误。
与大多数函数一样，输入表达式可以是列、常量、绑定变量或由它们组成的表达式。