Oracle 19c 新特性:ANY_VALUE 函数和 LISTAGG 的增强

盖国强 2019-02-13
509

注: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 by d.deptno,d.dname

Result Set 13

DEPTNODNAMESUM(E.SAL)
10ACCOUNTING8750
20RESEARCH10875
30SALES9400


在有了 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 by d.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.emp GROUP BY deptno;


DEPTNOEMPLOYEES
10CLARK,KING,MILLER
20ADAMS,FORD,JONES,SCOTT,SMITH
30ALLEN,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

DNAMEJOBS
ACCOUNTINGCLERK, MANAGER, PRESIDENT
RESEARCHANALYST, ANALYST, CLERK, CLERK, MANAGER
SALESCLERK, MANAGER, SALESMAN, SALESMAN, SALESMAN, SALESMAN

在Oracle 19c中,这个函数向前迈进了一小步,支持 Distinct 关键字,可以通过加入这个关键字直接去除重复值,SQL 又向优雅迈进了一小步:

select d.dname,   

       listagg (DISTINCT 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   

DNAMEJOBS
ACCOUNTINGCLERK, MANAGER, PRESIDENT
RESEARCHANALYST, CLERK, MANAGER
SALESCLERK, MANAGER, SALESMAN

在19c 中,Oracle 还对位运算进行了增强,增加了一系列的位运算函数,通过位运算,避免一些排序操作,可以实现对于性能的巨大提升。

例如,如下案例,通过物化视图的自动改写,可以实现显著的性能提升。


希望提前体验19c 的同学们可以去 livesql.oracle.com 进行测试,19c 马上到来,抓紧时间学习吧!


资源下载

关注微信:数据和云(OraNews)回复关键字获取

2018DTCC , 数据库大会PPT

2018DTC,2018 DTC 大会 PPT

DBALIFE ,“DBA 的一天”海报

DBA04 ,DBA 手记4 电子书

122ARCH ,Oracle 12.2体系结构图

2018OOW ,Oracle OpenWorld 资料

学习交流

最后修改时间:2020-05-07 23:22:05
文章转载自盖国强,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论