Oracle 21c中的 ANY_VALUE 聚合函数
Oracle 21c中的 ANY_VALUE 聚合函数
一、ANY_VALUE 函数语法
二、目的
ANY_VALUE 总是返回一个不确定的表达式结果值。可以将其用作聚合函数。
使用 ANY_VALUE 优化包含 GROUP BY 子句的查询。ANY_VALUE 总是返回组中表达式的值。它经过优化以返回第一个值。
它确保了对任何传入的行都没有进行比较，并且还消除了将每一列指定为GROUP BY 子句的一部分的必要性。因为它不比较值，所以 ANY_VALUE 比为解决 GROUP BY 子句中列而使用的 MIN 或 MAX 更快地返回值。
三、语义解释
ALL，DISTINCT：这些关键字 ANY_VALUE 支持，尽管它们对查询结果没有影响。
expr：表达式可以是列、常量、绑定变量，也可以是涉及它们的表达式。
表达式中的空值将被忽略。
支持除LONG、LOB、FILE 或 COLLECTION 之外的所有数据类型。
如果使用LONG，则会引发ORA-00997。
如果使用LOB、文件或集合数据类型，则会引发ORA-00932。
ANY_VALUE 遵循与 MIN 和 MAX 相同的规则。
根据 group BY 规范返回每个组中的任何值。如果组中的所有行都有NULL表达式值，则返回NULL。
ANY_VALUE 的结果都是不确定的。
四、测试
4.1 初始化数据
-- drop table emp purge;
-- drop table dept purge;
create table dept (
deptno number(2) constraint pk_dept primary key,
dname varchar2(14),
loc varchar2(13)
) ;
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) constraint fk_deptno references dept
);
insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');
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;4.2 问题
我们希望返回一个部门列表，其中包含部门中员工数量的计数，因此我们使用count 聚合函数和GROUP BY子句。
select d.deptno,
d.dname,
count(e.empno) as employee_count
from dept d
left outer join emp e on d.deptno = e.deptno
group by d.deptno, d.dname
order by 1;
DEPTNO DNAME EMPLOYEE_COUNT
---------- -------------- --------------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
SQL>我们必须将 select 列表中的所有非聚合列都包含到 GROUP BY 中，否则会出现错误。在本例中，我们并不真正关心是否将 DNAME 列包含在GROUP BY中，但我们必须这样做。在 GROUP BY 中添加额外的列意味着更多的开销。为了解决这个问题，有时会使用 MIN 或 MAX 函数。
select d.deptno,
min(d.dname) as dname,
count(e.empno) as employee_count
from dept d
left outer join emp e on d.deptno = e.deptno
group by d.deptno
order by 1;
DEPTNO DNAME EMPLOYEE_COUNT
---------- -------------- --------------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
SQL>
select d.deptno,
max(d.dname) as dname,
count(e.empno) as employee_count
from dept d
left outer join emp e on d.deptno = e.deptno
group by d.deptno
order by 1;
DEPTNO DNAME EMPLOYEE_COUNT
---------- -------------- --------------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
SQL>使用 MIN 或者 MAX 函数后我们可以从 GROUP BY 中删除 DNAME 列，但现在我们有了与 MIN 和 MAX 函数相关的额外内容，这是一项新的开销。
4.3 ANY_VALUE 聚合函数
为了解决这个问题，在 Oracle21c 中引入了 ANY_VALUE 聚合函数。我们使用它的方式与使用 MIN 或 MAX 的方式相同，但它经过优化以减少聚合函数的开销。ANY_VALUE 只显示它找到的第一个非空值，而不是进行任何比较。
select d.deptno,
any_value(d.dname) as dname,
count(e.empno) as employee_count
from dept d
left outer join emp e on d.deptno = e.deptno
group by d.deptno
order by 1;
DEPTNO DNAME EMPLOYEE_COUNT
---------- -------------- --------------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
SQL>因此，现在我们可以通过减少 group by 中额外列的开销，而不必增加 MIN 或 MAX 函数的开销。
五、了解相关知识
1、当数据集小的时候你可能不会注意到性能有显著的提升，但随着数据集大小的增加，使用 MIN 或 MAX 函数的开销也会增加。
2、别人不会知道你是选择使用 MIN 或者 MAX 函数只是为了从 group by 中删除该列。ANY_VALUE 聚合函数可以向任何其他开发人员清楚地表明，您正在使用它将列从 group by 中删除。
3、ANY_VALUE 函数支持除 XMLTYPE、ANYDATA、LOB、file 或 collection 数据类型之外的任何数据类型，如果使用不支持的数据类型会导致ORA-00932错误。
4、与大多数函数一样，输入表达式可以是列、常量、绑定变量或由它们组成的表达式。