原标题:Oracle 19c 新特性:ANY_VALUE 函数和 LISTAGG 的增强
注:Oracle 的 LiveSQL 站点已经升级到 19c 版本,可以通过该网站来测试 19c 的新特性。
在Oracle 19c中,一个新的函数 ANY_VALUE 被引入进来,这个函数的作用是基于ORDER BY定义返回每个组中的任何值,通过这一个函数,可以消除将每个列都指定为GROUP BY子句的一部分的必要性。
看一个示例。
select d.deptno,d.dname,sum(e.sal)
from scott.dept d,scott.emp e
where e.deptno = d.deptno group byd.deptno,d.dnameResult Set 13
DEPTNO
DNAME
SUM(E.SAL)
10
ACCOUNTING
8750
20
RESEARCH
10875
30
SALES
9400
在有了 ANY_VALUE 之后,Group By之后就不再需要增加冗余的字段,SQL 再次变得优雅:
select d.deptno,any_value(d.dname) as DEPTNAME,sum(e.sal)
from scott.dept d,scott.emp e
where e.deptno = d.deptno group byd.deptno
ANY_VALUE 也可以作为类似 MAX/MIN 的窗口函数发挥作用。
Listagg 是 Oracle 11.2 中新增的函数,对于指定的度量,LISTAGG对ORDER BY子句中指定的每个组内的数据进行排序,然后连接度量列的值。
作为单集聚合函数,LISTAGG对所有行进行操作并返回单个输出行。
作为一个组集聚合,该函数对GROUP BY子句定义的每个组进行操作并返回一个输出行。
作为分析函数,LISTAGG根据query_partition_clause中的一个或多个表达式将查询结果集划分为组。
由于具有灵活的数据操作性,LISTAGG函数是行列转换的首选。以下是一个简单的示例:
SELECT deptno,
LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees
FROM scott.empGROUP BY deptno;
DEPTNO
EMPLOYEES
10
CLARK,KING,MILLER
20
ADAMS,FORD,JONES,SCOTT,SMITH
30
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
大家可以在 livesql.oracle.com 网站体验 19c 的新特性:
在19c之前,Listagg 函数不能对转换的结果去重,如果你希望计算结果不包含重复值,则还需要进行一次嵌套处理。如下是常见的带有重复值计算输出:
select d.dname,
listagg (e.job,', ')
within group (order by e.job) jobs
from scott.dept d, scott.emp e
where d.deptno = e.deptno
group by d.dname
DNAME
JOBS
ACCOUNTING
CLERK, MANAGER, PRESIDENT
RESEARCH
ANALYST, ANALYST, CLERK, CLERK, MANAGER
SALES
CLERK, MANAGER, SALESMAN, SALESMAN, SALESMAN, SALESMAN
在Oracle 19c中,这个函数向前迈进了一小步,支持 Distinct 关键字,可以通过加入这个关键字直接去除重复值,SQL 又向优雅迈进了一小步:
select d.dname,
listagg (DISTINCTe.job,', ')
within group (order by e.job) jobs
from scott.dept d, scott.emp e
where d.deptno = e.deptno
group by d.dname
DNAME
JOBS
ACCOUNTING
CLERK, MANAGER, PRESIDENT
RESEARCH
ANALYST, CLERK, MANAGER
SALES
CLERK, MANAGER, SALESMAN
在19c 中,Oracle 还对位运算进行了增强,增加了一系列的位运算函数,通过位运算,避免一些排序操作,可以实现对于性能的巨大提升。
例如,如下案例,通过物化视图的自动改写,可以实现显著的性能提升。
希望提前体验19c 的同学们可以去 livesql.oracle.com 进行测试,19c 马上到来,抓紧时间学习吧!
责任编辑: