Oracle listagg去重distinct的三種方式總結

2022-11-21 14:02:43

一、簡介

最近在工作中，在寫oracle統計查詢的時候，遇到listagg聚合函數分組聚合之後出現很多重複資料的問題，於是研究了一下listagg去重的幾種方法

以下通過範例講解三種實現listagg去重的方法。

二、方法

首先還原listagg聚合之後出現重複資料的現象,開啟plsql，執行如下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

執行結果：

如圖，listagg聚合之後很多重複資料，下面講解如何解決重複資料問題。

【a】第一種方法

使用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

如上圖，listagg聚合之後沒有出現重複資料了。oracle官方不太推薦使用wm_concat()來進行聚合，能儘量使用listagg就使用listagg。

【b】第二種方法

使用正則替換方式去重(僅適用於oracle字串大小比較小的情況)

--第二種方法：使用正則替換方式去重(僅適用於oracle字串大小比較小的情況)
select t.department_name depname,
       t.department_key,
       regexp_replace(listagg(t.class_key, ',') within
                      group(order by t.class_key),
                      '([^,]+)(,1)*(,|$)',
                      '13') as class_keys
  from V_YDXG_TEACHER_KNSRDGL t
 group by t.department_key, t.department_name;

這種方式處理listagg去重問題如果拼接的字串太長會報oracle超過最大長度的錯誤，只適用於資料量比較小的場景。

【c】第三種方法

先去重，再聚合(推薦使用)

--第三種方法：先去重，再聚合
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;