Oracle listagg去重

Oracle listagg去重

转载：Oracle listagg去重distinct三种方法总结_每天都要进步一点点的博客-CSDN博客

非常实用的文章，好几次遇到问题都要回来再查一遍，于是转载一下，做个记录

原sql

select t.department_name depname,
       t.department_key,
       listagg(t.class_key, ',') within group(order by t.class_key) as class_keys
  from V_YDXG_TEACHER_KNSRDGL t
 where 1 = 1
 group by t.department_key, t.department_name

第一种：使用使用wm_concat() + distinct去重聚合

--第一种方法： 使用wm_concat() + distinct去重聚合
select t.department_name depname,
       t.department_key,
       wm_concat(distinct t.class_key) as class_keys
  from V_YDXG_TEACHER_KNSRDGL t
 where 1 = 1
 group by t.department_key, t.department_name

oracle官方不太推荐使用wm_concat()来进行聚合，能尽量使用listagg就使用listagg。

第二种：使用正则替换方式去重(仅适用于oracle字符串大小比较小的情况)

--第二种方法：使用正则替换方式去重(仅适用于oracle字符串大小比较小的情况)
select t.department_name depname,
       t.department_key,
       regexp_replace(listagg(t.class_key, ',') within
                      group(order by t.class_key),
                      '([^,]+)(,\1)+', 
                      '\1') as class_keys
  from V_YDXG_TEACHER_KNSRDGL t
 group by t.department_key, t.department_name;

这种方式处理listagg去重问题如果拼接的字符串太长会报oracle超过最大长度的错误，只适用于数据量比较小的场景。

第三种：先去重(子查询)，再聚合(推荐使用)

--第三种方法：先去重，再聚合
select t.department_name depname,
       t.department_key,
       listagg(t.class_key, ',') within group(order by t.class_key) as class_keys
  from (select distinct s.class_key, s.department_key, s.department_name
          from V_YDXG_TEACHER_KNSRDGL s) t
 group by t.department_key, t.department_name
 
--或者
select s.department_key,
       s.department_name,
       listagg(s.class_key, ',') within group(order by s.class_key) as class_keys
  from (select t.department_key,
               t.department_name,
               t.class_key,
               row_number() over(partition by t.department_key, t.department_name, t.class_key order by t.department_key, t.department_name) as rn
          from V_YDXG_TEACHER_KNSRDGL t
         order by t.department_key, t.department_name, t.class_key) s
 where rn = 1
 group by s.department_key, s.department_name;