Oracle 19c New FeaturesLISTAGG函數功能增強

IT那活兒發布于2023-01-11 13:20 / 1431人閱讀

一. LISTAGG函數簡介

LISTAGG函數是11g R2引入用于合并字符串（列轉行），可替換wmsys.wm_concat功能，而且效率更好。wm_concat函數是自定義聚集函數，而且是undocument的，從12C開始已經去掉，從11g R2開始，有字符串合并的需求，最好使用LISTAGG替代，以獲得更好的性能。

但是，LISTAGG函數在11g R2中有個缺點，就是不能直接DISTINCT，在ORACLE 19C中，ORACLE給它增加了DISTINCT功能，這樣可以剔除重復的字符串合并。19C的完整LISTAGG語法如下：

從語法圖上可以看出，LISTAGG函數可以是普通的組函數，也可以用于分析函數，并且12C開始增加了OVERFLOW語法，用于字符串過長的處理。

二. 使用LISTAGG函數實現字符串合并

如下例所示：

需求：對emp表，按照部門分組，按逗號合并部門員工名。

使用LISTAGG實現如下：

▼▼▼
select deptno, listagg(ename,,) within group(order by deptno) as enames
  from emp
  group by deptno
order by deptno;
 
DEPTNO ENAMES
------------------------------------------------------------------------------
    10 CLARK,KING,MILLER
    20 ADAMS,FORD,JONES,SCOTT,SMITH
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

下面給DEPTNO=10的部門插入2個重復名字的員工，如下所示：

▼▼▼
INSERT INTO emp VALUES (8000,KING,ANALYST,7782,to_date(1983-1-1,yyyy-mm-dd),2000,NULL,10);
INSERT INTO emp VALUES (9000,KING,MANADER,7782,to_date(1984-5-1,yyyy-mm-dd),2500,NULL,10);
COMMIT;

再次查詢，發現有重復的數據：

▼▼▼
SQL> select deptno, listagg(ename,,) within group(order by deptno) as enames
  2    from emp
  3    group by deptno
  4  order by deptno;
 
DEPTNO ENAMES
------ --------------------------------------------------------------------------------
    10 CLARK,KING,KING,KING,MILLER
    20 ADAMS,FORD,JONES,SCOTT,SMITH
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

一般遇到這種有重復數據的情況，需要剔除重復數據，在19C之前，需要先剔除重復數據：

▼▼▼
--紅色部分使用分析函數剔重，當然這里直接distinct也可以，結果與最前面的一致
select deptno, listagg(ename, ,) within group(order by deptno) as enames
  from (select deptno,
               ename,
               row_number() over(partition by deptno, ename order by empno) rn
          from emp)
 where rn = 1
 group by deptno
 order by deptno;
 
或者直接DISTINCT ：
select deptno, listagg(ename, ,) within group(order by deptno) as enames
  from (select distinct deptno,
               ename
          from emp)
 group by deptno
 order by deptno;
 
DEPTNO ENAMES
-------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

到了19C，LISTAGG提供了直接DISTINCT的功能，可以很簡單地實現剔除重復數據，然后合并，如下所示：

▼▼▼
select deptno, listagg(distinct ename,,) within group(order by deptno) as enames
  from emp
 group by deptno
 order by deptno;
 
DEPTNO ENAMES
-------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

默認情況下包含所有數據（ALL不用寫默認），也就是LISTAGG(ALL column),如下所示：

▼▼▼
select deptno, listagg(all ename,,) within group(order by deptno) as enames
  from emp
 group by deptno
 order by deptno;

三. WM_CONCAT函數與LISTAGG函數比較

如果使用wm_concat,則是：

▼▼▼
select deptno, wm_concat(distinct ename) as enames
  from emp
 group by deptno
 order by deptno;
 
DEPTNO ENAMES
------ --------------------------------------------------------------------------------
    10 CLARK,KING,MILLER
    20 ADAMS,FORD,JONES,SCOTT,SMITH
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

wm_concat這個undocument函數可以直接distinct,但是它與LISTAGG比缺點很明顯，一是性能差，而是undocument函數，比較如下：

wm_concat可以distinct去除重復的(用于分析函數，distinct不能帶order by)，19C之前的listagg不可以,可以先剔除重復，然后做listagg，19C之后的listagg可以distinct。
wm_concat不能保證排序，listagg可以保證排序.
WMSYS.WM_CONCAT是undocument的函數，最好不要使用，要使用也用自定義分析函數

四. 使用ON OVERFLOW處理字符串長度溢出問題

LISTAGG函數在12.2開始，合并長度最多32767字節，依賴于MAX_STRING_SIZE參數，如下所示：

如果MAX_STRING_SIZE=EXTEND,則對于VARCHAR2和RAW類型，最多返回32767字節
如果MAX_STRING_SIZE=STANDARD,則對于VARCHAR2最多4000字節，對于RAW類型最多2000字節

那么在合并的字符串超過限制，溢出時，默認報錯，但是12.2引入了ON OVERFLOW可以截斷處理，如下：

ON OVERFLOW TRUNCATE默認對溢出數據后面用…(count),如下所示：

▼▼▼
SELECT deptno, LISTAGG(ename, ,  ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY deptno) AS enames
FROM  (select deptno,ename from emp,(select level from dual connect by level<1000))
GROUP BY deptno
ORDER BY deptno;
 
DEPTNO ENAMES
--------------------------------------------------------------------------------------------
    10 CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLAR
       省略
       ARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,
       CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,CLARK,...(4334)
 
    20 ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAM
       S,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,AD
       AMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,
       省略
       ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,ADAMS,...(4334)
 
    30 ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLE
       N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL
       LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,
      省略
       LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,
       ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,...(5333)

其他用法如下：

▼▼▼
--1.改變省略的格式，換成~~~
SELECT deptno, LISTAGG(ename, , ON OVERFLOW TRUNCATE ~~~) WITHIN GROUP (ORDER BY deptno) AS enames
FROM  (select deptno,ename from emp,(select level from dual connect by level<1000))
GROUP BY deptno
ORDER BY deptno;
 
--2.通過WINTHOUT COUNT省略掉計數
SELECT deptno, LISTAGG(ename, , ON OVERFLOW TRUNCATE WITHOUT COUNT) WITHIN GROUP (ORDER BY deptno) AS enames
FROM  (select deptno,ename from emp,(select level from dual connect by level<1000))
GROUP BY deptno
ORDER BY deptno;
 
--3.默認格式ON OVERFLOW ERROW，溢出則報錯ORA-01489: result of string concatenation is too long
 
SELECT deptno, LISTAGG(ename, , ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY deptno) AS enames
FROM  (select deptno,ename from emp,(select level from dual connect by level<1000))
GROUP BY deptno
ORDER BY deptno;

五. 使用XMLAGG返回CLOB處理字符串合并超長需求

1. 針對超過字符串長度溢出的問題，如果要完整顯示，可以使用如下方式：

自定義wm_concat返回值類型為CLOB
使用XMLAGG函數獲取CLOB值

下面使用XMLAGG函數處理超長合并字符串問題，如下所示：

▼▼▼
SELECT deptno,  RTRIM(xmlagg(xmlelement(c, ename || ,)
ORDER BY deptno).extract(//text()).getclobval(),
             ,) AS enames
FROM  (select deptno,ename from emp,(select level from dual connect by level<1000))
GROUP BY deptno
ORDER BY deptno;

總結：ORACLE 19C針對LISTAGG增加了很多改進，在以后有字符串合并需求（列轉行）的時候，要優先使用LISTAGG，而不是WM_CONCAT,對于自定義聚集函數，性能較差，而且功能也沒有LISTAGG強大。

END

更多精彩干貨分享

點擊下方名片關注

IT那活兒

GPU云服務器云服務器 new函數關閉函數功能開啟函數功能 oracle去重函數

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129862.html

19C?DG?Broker配置和測試

19C?DG?Broker配置和測試 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; ...

IT那活兒 2023-01-11 13:20 評論0 收藏2941
聊聊 Java8 以后各個版本的新特性

摘要：于是抽時間看了看以后各個版本的特性，做了一個總結。年和公開版本發布，取名為。此后對應版本就是，。發布，是一個重大版本更新。在此之后，就是每六個月發布一次新版本。以上和參考資料聊了一些關于的歷史，下面我們看看各個版本有那些新特性。【這是 ZY 第 11 篇原創技術文章】某天在網上閑逛，突然看到有篇介紹 Java 11 新特性的文章，頓時心里一驚，畢竟我對于 Java 的版本認識...

K_B_Z 2019-08-19 10:35 評論0 收藏0
Java 10 新特性解密，引入類型推斷機制，2018 年 3 月 20 日發布

摘要：目標發布目前有兩個主要功能針對局部變量類型推斷這將刪除大部分對象實例化所需的冗長的包含手動類型信息整合源樹的庫即不同的庫將被合并成一個單一的存儲庫。特別是，承諾為局部變量實例化引入類型推斷機制，并將現有的存儲庫合并到一個存儲庫中。 JDK 10 何時發布？ JDK 10 是 Java 10 標準版的部分實現，將于 2018 年 3 月 20 日發布，改進的關鍵點包括一個本地類型推斷、一...

caspar 2019-08-15 14:36 評論0 收藏0