成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

Oracle 19c New FeaturesLISTAGG函數(shù)功能增強

IT那活兒 / 1594人閱讀
Oracle 19c New FeaturesLISTAGG函數(shù)功能增強

一. LISTAGG函數(shù)簡介

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

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

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


二. 使用LISTAGG函數(shù)實現(xiàn)字符串合并

如下例所示：

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

使用LISTAGG實現(xiàn)如下：

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

再次查詢，發(fā)現(xiàn)有重復的數(shù)據(jù)：

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

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

▼▼▼
--紅色部分使用分析函數(shù)剔重，當然這里直接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的功能，可以很簡單地實現(xiàn)剔除重復數(shù)據(jù)，然后合并，如下所示：

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

默認情況下包含所有數(shù)據(jù)（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函數(shù)與LISTAGG函數(shù)比較
如果使用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函數(shù)可以直接distinct,但是它與LISTAGG比缺點很明顯，一是性能差，而是undocument函數(shù)，比較如下：

  • wm_concat可以distinct去除重復的(用于分析函數(shù)，distinct不能帶order by)，19C之前的listagg不可以,可以先剔除重復，然后做listagg，19C之后的listagg可以distinct。

  • wm_concat不能保證排序，listagg可以保證排序.

  • WMSYS.WM_CONCAT是undocument的函數(shù)，最好不要使用，要使用也用自定義分析函數(shù)


四. 使用ON OVERFLOW處理字符串長度溢出問題
LISTAGG函數(shù)在12.2開始，合并長度最多32767字節(jié)，依賴于MAX_STRING_SIZE參數(shù)，如下所示：

  • 如果MAX_STRING_SIZE=EXTEND,則對于VARCHAR2和RAW類型，最多返回32767字節(jié)

  • 如果MAX_STRING_SIZE=STANDARD,則對于VARCHAR2最多4000字節(jié)，對于RAW類型最多2000字節(jié) 

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

ON OVERFLOW TRUNCATE默認對溢出數(shù)據(jù)后面用…(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省略掉計數(shù)
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函數(shù)獲取CLOB值

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

▼▼▼
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增加了很多改進，在以后有字符串合并需求（列轉行）的時候，要優(yōu)先使用LISTAGG，而不是WM_CONCAT,對于自定義聚集函數(shù)，性能較差，而且功能也沒有LISTAGG強大。


END

更多精彩干貨分享

點擊下方名片關注

IT那活兒

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

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

相關文章

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<