在Oracle中使用LISTAGG函数返回不重复的值。

25 浏览
0 Comments

在Oracle中使用LISTAGG函数返回不重复的值。

我试着在Oracle中使用LISTAGG函数。我想要获取该列的唯一值。有没有一种方法可以在不创建函数或过程的情况下仅获取唯一值？

  col1  col2 Created_by
   1     2     Smith 
   1     2     John 
   1     3     Ajay 
   1     4     Ram 
   1     5     Jack

我需要选择col1和col2的LISTAGG（不考虑第3列）。当我这样做时，LISTAGG的结果如下：[2,2,3,4,5]。

我需要移除这里重复的\'2\'，我仅需要col1对应的col2的唯一值。

admin 更改状态以发布 2023年5月21日
0

2 答案

0 Comments

从Oracle 19C开始，它是内置的，请参见此处

从18C及更早版本，请尝试使用Within Group，请参见此处

否则，请使用正则表达式

以下是解决问题的方法。

select  
      regexp_replace(
    '2,2,2.1,3,3,3,3,4,4' 
     ,'([^,]+)(,\1)*(,|$)', '\1\3')
from dual

返回

2,2.1,3,4

下面是答案：

select col1, 
regexp_replace(
    listagg(
     col2 , ',') within group (order by col2)  -- sorted
    ,'([^,]+)(,\1)*(,|$)', '\1\3') )
   from tableX
where rn = 1
group by col1;

注意：上述方法在大多数情况下都可行-列表应该进行排序，根据您的数据，您可能需要修剪所有尾随和前导空格。

如果您在一组中有很多项目> 20或字符串大小很大，则可能会遇到Oracle字符串大小限制“字符串连接的结果太长”。

从Oracle 12cR2开始，您可以消除此错误，请参见此处。或者在每个组中放置最大数量的成员。如果只列出第一个成员是可以的，那么这只能起作用。如果您有非常长的可变字符串，则可能不起作用。您将不得不进行实验。

select col1,
case 
    when count(col2) < 100 then 
       regexp_replace(
        listagg(col2, ',') within group (order by col2)
        ,'([^,]+)(,\1)*(,|$)', '\1\3')
    else
    'Too many entries to list...'
end
from sometable
where rn = 1
group by col1;

另一个解决方案（不是很简单），希望避免Oracle字符串大小限制-字符串大小受到限制为4000。感谢user3465996在此处的帖子

select col1  ,
    dbms_xmlgen.convert(  -- HTML decode
    dbms_lob.substr( -- limit size to 4000 chars
    ltrim( -- remove leading commas
    REGEXP_REPLACE(REPLACE(
         REPLACE(
           XMLAGG(
             XMLELEMENT("A",col2 )
               ORDER BY col2).getClobVal(),
             '',','),
             '',''),'([^,]+)(,\1)*(,|$)', '\1\3'),
                  ','), -- remove leading XML commas ltrim
                      4000,1) -- limit to 4000 string size
                      , 1)  -- HTML.decode
                       as col2
 from sometable
where rn = 1
group by col1;

V1-一些测试情况-FYI

regexp_replace('2,2,2.1,3,3,4,4','([^,]+)(,\1)+', '\1')
-> 2.1,3,4 Fail
regexp_replace('2 ,2 ,2.1,3 ,3 ,4 ,4 ','([^,]+)(,\1)+', '\1')
-> 2 ,2.1,3,4 Success  - fixed length items

V2-包含在项内的项，例如2,21

regexp_replace('2.1,1','([^,]+)(,\1)+', '\1')
-> 2.1 Fail
regexp_replace('2 ,2 ,2.1,1 ,3 ,4 ,4 ','(^|,)(.+)(,\2)+', '\1\2')
-> 2 ,2.1,1 ,3 ,4  -- success - NEW regex
 regexp_replace('a,b,b,b,b,c','(^|,)(.+)(,\2)+', '\1\2')
-> a,b,b,c fail!

v3-正则表达式谢谢Igor！ 适用于所有情况。

select  
regexp_replace('2,2,2.1,3,3,4,4','([^,]+)(,\1)*(,|$)', '\1\3') ,
---> 2,2.1,3,4 works
regexp_replace('2.1,1','([^,]+)(,\1)*(,|$)', '\1\3'),
--> 2.1,1 works
regexp_replace('a,b,b,b,b,c','([^,]+)(,\1)*(,|$)', '\1\3')
---> a,b,c works
from dual

0
0 Comments

19c及其之后版本：

select listagg(distinct the_column, ',') within group (order by the_column)
from the_table

18c及其之前版本：

select listagg(the_column, ',') within group (order by the_column)
from (
   select distinct the_column 
   from the_table
) t

如果您需要更多列，可能会需要类似以下内容：

select col1, listagg(col2, ',') within group (order by col2)
from (
  select col1, 
         col2,
         row_number() over (partition by col1, col2 order by col1) as rn
  from foo
  order by col1,col2
)
where rn = 1
group by col1;

0