歡迎光臨
每天分享高質量文章

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

Result 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 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;

 

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

DNAME JOBS
ACCOUNTING CLERK, MANAGER, PRESIDENT
RESEARCH ANALYST, CLERK, MANAGER
SALES CLERK, 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 資料

    赞(0)

    分享創造快樂