如何在SQL中优先考虑使用LISTAGG而不是WM_CONCAT？

最编程 2024-08-08 12:12:09

...

Oracle

尽量使用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;

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

本站文章除注明转载/出处外，均为本站原创，转载前请务必署名,转载请标明出处最后编辑时间为: 2021/11/29 18:37:29

上一篇：理解Oracle Database 19c中的DISTINCT与LISTAGG函数

下一篇： 5个例子教你如何在PostgreSQL中实现交叉表或行列转换