LISTAGG代替WM_CONCAT

  1. wm_concat可能会因为数据库版本的不同 返回clob或者varcahr2字段，增加线上报错概率比如ORA-22922
  2. wm_concat聚合的字段没有固定的顺序,listagg可以根据字段排序
  3. listagg的性能比wm_concat好
  4. wm_concat函数是可以支持distinct的,但是listagg分析函数是不支持distinct的,只能先去重再聚合
尽量使用LISTAGG代替WM_CONCAT

测试数据

SELECT * FROM TEST_IGNORE;

使用wm_concat

SELECT T.RCLASS,
       WMSYS.WM_CONCAT(T.BANK) AS BANK
FROM TEST_IGNORE T
GROUP BY T.RCLASS;

去重后

SELECT T.RCLASS,
       WMSYS.WM_CONCAT(distinct T.BANK) AS BANK
FROM TEST_IGNORE T
GROUP BY T.RCLASS;

使用listagg

SELECT t.rclass,
       LISTAGG( t.bank,',') WITHIN GROUP(order by t.id) as BANK
FROM TEST_IGNORE t
GROUP BY T.RCLASS;

listagg去重

SELECT T.RCLASS,
       LISTAGG(T.BANK, ',') WITHIN GROUP(ORDER BY T.ID) AS BANK
FROM (SELECT T.RCLASS,
             T.BANK,
             T.ID
      FROM (SELECT T.RCLASS,
                   T.BANK,
                   T.ID,
                   ROW_NUMBER() OVER(PARTITION BY T.RCLASS, T.BANK ORDER BY T.ID) AS RN
            FROM TEST_IGNORE T) T
      WHERE RN = 1) T
GROUP BY RCLASS;
参考：尽量使用LISTAGG代替WM_CONCAT
posted @ 2022-09-27 09:33  aspirant  阅读(402)  评论(0)  编辑  收藏  举报