註: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 馬上到來,抓緊時間學習吧!