복붙노트

[SQL] 오라클의 LISTAGG는 고유 한 값을 반환

SQL

오라클의 LISTAGG는 고유 한 값을 반환

나는 오라클의 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 만 고유 한 값이 필요합니다.

해결법

  1. ==============================

    1.19C 이상 :

    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;
  2. ==============================

    2.여기에 귀하의 문제를 해결하는 방법은 다음과 같습니다.

    여기에 귀하의 문제를 해결하는 방법은 다음과 같습니다.

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

from dual

    보고

    2,2.1,3,4

    오라클 19C에서 그것은 여기에서 볼에 내장되어 있습니다

    18C 이전의 시도에서 그룹 내 여기 참조

    그렇지 않으면 정규 표현식을 사용

    답변 아래 :

    select col1, 

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

    참고 : 대부분의 경우 위의 의지 작업 - 목록 정렬해야합니다, 당신은 당신의 데이터에 따라 모든 후행 및 선행 공백을 트리밍 할 수 있습니다.

    당신이 그룹의 항목이 많은 경우> 20 또는 큰 문자열은 '문자열 연결의 결과가 너무 깁니다'오라클 문자열 크기 제한으로 실행될 수 있습니다 크기.

    오라클 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;

    (그렇게 간단하지) 또 다른 해결책은 희망 오라클 문자열 크기 제한을 피하기 위해 - 문자열 크기가 user3465996에 의해 여기이 게시물에 4000 감사로 제한됩니다

    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(),
             '<A>',','),
             '</A>',''),'([^,]+)(,\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는 항목이 예에 포함 -items. 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 - 정규식 이고르 감사합니다! 모든 경우에 작동합니다.

    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
  3. ==============================

    3.당신은 문서화되지 않은 wm_concat 기능을 사용할 수 있습니다.

    당신은 문서화되지 않은 wm_concat 기능을 사용할 수 있습니다.

    select col1, wm_concat(distinct col2) col2_list 
from tab1
group by col1;

    이 함수를 반환 CLOB 열은, 당신이 원하는 경우에 당신은 VARCHAR2로 변환 CLOB에 dbms_lob.substr 사용할 수 있습니다.

  4. ==============================

    4.내가 먼저, 다음 listagg 또 다른 집계를 수행 값을 그룹화하여이 문제를 극복했다. 이 같은:

    내가 먼저, 다음 listagg 또 다른 집계를 수행 값을 그룹화하여이 문제를 극복했다. 이 같은:

    select a,b,listagg(c,',') within group(order by c) c, avg(d)
from (select a,b,c,avg(d)
      from   table
      group by (a,b,c))
group by (a,b)

    하나의 전체 테이블 액세스는 비교적 쉽게 더 복잡한 쿼리에 확장

  5. ==============================

    5.의도 여러 열이 변환을 적용하는 경우에, 나는 a_horse_with_no_name의 솔루션을 확장했다 :

    의도 여러 열이 변환을 적용하는 경우에, 나는 a_horse_with_no_name의 솔루션을 확장했다 :

    SELECT * FROM
(SELECT LISTAGG(GRADE_LEVEL, ',') within group(order by GRADE_LEVEL) "Grade Levels" FROM (select distinct GRADE_LEVEL FROM Students) t)                     t1,
(SELECT LISTAGG(ENROLL_STATUS, ',') within group(order by ENROLL_STATUS) "Enrollment Status" FROM (select distinct ENROLL_STATUS FROM Students) t)          t2,
(SELECT LISTAGG(GENDER, ',') within group(order by GENDER) "Legal Gender Code" FROM (select distinct GENDER FROM Students) t)                               t3,
(SELECT LISTAGG(CITY, ',') within group(order by CITY) "City" FROM (select distinct CITY FROM Students) t)                                                  t4,
(SELECT LISTAGG(ENTRYCODE, ',') within group(order by ENTRYCODE) "Entry Code" FROM (select distinct ENTRYCODE FROM Students) t)                             t5,
(SELECT LISTAGG(EXITCODE, ',') within group(order by EXITCODE) "Exit Code" FROM (select distinct EXITCODE FROM Students) t)                                 t6,
(SELECT LISTAGG(LUNCHSTATUS, ',') within group(order by LUNCHSTATUS) "Lunch Status" FROM (select distinct LUNCHSTATUS FROM Students) t)                     t7,
(SELECT LISTAGG(ETHNICITY, ',') within group(order by ETHNICITY) "Race Code" FROM (select distinct ETHNICITY FROM Students) t)                              t8,
(SELECT LISTAGG(CLASSOF, ',') within group(order by CLASSOF) "Expected Graduation Year" FROM (select distinct CLASSOF FROM Students) t)                     t9,
(SELECT LISTAGG(TRACK, ',') within group(order by TRACK) "Track Code" FROM (select distinct TRACK FROM Students) t)                                         t10,
(SELECT LISTAGG(GRADREQSETID, ',') within group(order by GRADREQSETID) "Graduation ID" FROM (select distinct GRADREQSETID FROM Students) t)                 t11,
(SELECT LISTAGG(ENROLLMENT_SCHOOLID, ',') within group(order by ENROLLMENT_SCHOOLID) "School Key" FROM (select distinct ENROLLMENT_SCHOOLID FROM Students) t)       t12,
(SELECT LISTAGG(FEDETHNICITY, ',') within group(order by FEDETHNICITY) "Federal Race Code" FROM (select distinct FEDETHNICITY FROM Students) t)                         t13,
(SELECT LISTAGG(SUMMERSCHOOLID, ',') within group(order by SUMMERSCHOOLID) "Summer School Key" FROM (select distinct SUMMERSCHOOLID FROM Students) t)                               t14,
(SELECT LISTAGG(FEDRACEDECLINE, ',') within group(order by FEDRACEDECLINE) "Student Decl to Prov Race Code" FROM (select distinct FEDRACEDECLINE FROM Students) t)          t15

    64 비트 생산 - 이것은 오라클 데이터베이스 11g 엔터프라이즈 에디션 출시 11.2.0.2.0입니다. 나는 DISTINCT와 ORDER 할 수있는 방법이 없기 때문에 STRAGG를 사용 할 수 없습니다. 성능은 내가 관심의 모든 열을 추가하고 있기 때문에, 좋은 인 선형 적으로 확장됩니다. 위는 77K 행을 3 초 걸렸다. 한 롤업, 0.172 초하십시오. 나는 한 번에 테이블에 여러 열을 distinctify 할 수있는 방법이 있었다으로한다.

  6. ==============================

    6.여러 컬럼에 걸쳐 고유 한 값을 원하는 경우, 정렬 순서를 제어 할 사라질 수 있습니다 문서화되지 않은 기능을 사용하지 않으려는, 더 많은 일 전체 테이블 스캔보다 유용한이 구조를 찾을 수 있습니다 싶지 않아요 :

    여러 컬럼에 걸쳐 고유 한 값을 원하는 경우, 정렬 순서를 제어 할 사라질 수 있습니다 문서화되지 않은 기능을 사용하지 않으려는, 더 많은 일 전체 테이블 스캔보다 유용한이 구조를 찾을 수 있습니다 싶지 않아요 :

    with test_data as 
(
      select 'A' as col1, 'T_a1' as col2, '123' as col3 from dual
union select 'A', 'T_a1', '456' from dual
union select 'A', 'T_a1', '789' from dual
union select 'A', 'T_a2', '123' from dual
union select 'A', 'T_a2', '456' from dual
union select 'A', 'T_a2', '111' from dual
union select 'A', 'T_a3', '999' from dual
union select 'B', 'T_a1', '123' from dual
union select 'B', 'T_b1', '740' from dual
union select 'B', 'T_b1', '846' from dual
)
select col1
     , (select listagg(column_value, ',') within group (order by column_value desc) from table(collect_col2)) as col2s
     , (select listagg(column_value, ',') within group (order by column_value desc) from table(collect_col3)) as col3s
from 
(
select col1
     , collect(distinct col2) as collect_col2
     , collect(distinct col3) as collect_col3
from test_data
group by col1
);
  7. ==============================

    7.은 "구별"부분을 만들 것입니다 전용 기능을 만드는 방법에 대해 무엇 :

    은 "구별"부분을 만들 것입니다 전용 기능을 만드는 방법에 대해 무엇 :

    create or replace function listagg_distinct (t in str_t, sep IN VARCHAR2 DEFAULT ',') 
  return VARCHAR2
as 
  l_rc VARCHAR2(4096) := '';
begin
  SELECT listagg(val, sep) WITHIN GROUP (ORDER BY 1)
    INTO l_rc
    FROM (SELECT DISTINCT column_value val FROM table(t));
  RETURN l_rc;
end;
/

    그리고 집계를 수행하는 데 사용할 :

    SELECT col1, listagg_distinct(cast(collect(col_2) as str_t ), ', ')
  FROM your_table
  GROUP BY col_1;
  8. ==============================

    8.당신이 listagg 유사하다 XMLAGG를 사용할 수있는 문자열 길이의 문제를 돌아 다니기 만은 CLOB를 반환합니다.

    당신이 listagg 유사하다 XMLAGG를 사용할 수있는 문자열 길이의 문제를 돌아 다니기 만은 CLOB를 반환합니다.

    그런 다음 REGEXP_REPLACE를 사용하여 구문 분석하고 고유 한 값을 가져온 다음 dbms_lob.substr을 사용하여 문자열로 다시 설정할 수 있습니다 (). 당신은 고유 값의 엄청난 금액이있는 경우 당신은 여전히 ​​공간이 방법을 실행되지만 많은 경우 아래의 코드는 작동합니다.

    당신은 또한 당신이 사용하는 구분 기호를 변경할 수 있습니다. ','대신하지만 당신은 당신이 원하는 경우 내 코드 사용 쉼표의 대시를 교체 할 수 있어야한다 '-'내 경우에는 내가 원했다.

    select col1,
    dbms_lob.substr(ltrim(REGEXP_REPLACE(REPLACE(
         REPLACE(
           XMLAGG(
             XMLELEMENT("A",col2)
               ORDER BY col2).getClobVal(),
             '<A>','-'),
             '</A>',''),'([^-]*)(-\1)+($|-)', 
           '\1\3'),'-'), 4000,1) as platform_mix
from table
  9. ==============================

    9.CASE 대 DECODE를 사용 a_horse_with_no_name의 ROW_NUMBER @ () 기반의 접근 방식에 유유의 보정 @ 또한 정제 (내가 여기 보았다). 나는 @Martin Vrbovsky 또한이 경우 접근 답을 가지고 참조하십시오.

    CASE 대 DECODE를 사용 a_horse_with_no_name의 ROW_NUMBER @ () 기반의 접근 방식에 유유의 보정 @ 또한 정제 (내가 여기 보았다). 나는 @Martin Vrbovsky 또한이 경우 접근 답을 가지고 참조하십시오.

    select
  col1, 
  listagg(col2, ',') within group (order by col2) AS col2_list,
  listagg(col3, ',') within group (order by col3) AS col3_list,
  SUM(col4) AS col4
from (
  select
    col1, 
    decode(row_number() over (partition by col1, col2 order by null),1,col2) as col2,
    decode(row_number() over (partition by col1, col3 order by null),1,col3) as col3
  from foo
)
group by col1;
  10. ==============================

    10.다가오는 오라클 19C는 LISTAGG와 DISTINCT 지원합니다.

    다가오는 오라클 19C는 LISTAGG와 DISTINCT 지원합니다.

    편집하다:

    오라클 19C LISTAGG DISTINCT

  11. ==============================

    11.사람이 BY 절 파티션을 사용하여 생각 했습니까? 이 애플리케이션 서비스 및 액세스 목록을 얻으려면이 쿼리에 나를 위해 일했다.

    사람이 BY 절 파티션을 사용하여 생각 했습니까? 이 애플리케이션 서비스 및 액세스 목록을 얻으려면이 쿼리에 나를 위해 일했다.

    SELECT DISTINCT T.APP_SVC_ID, 
       LISTAGG(RTRIM(T.ACCESS_MODE), ',') WITHIN GROUP(ORDER BY T.ACCESS_MODE) OVER(PARTITION BY T.APP_SVC_ID) AS ACCESS_MODE 
  FROM APP_SVC_ACCESS_CNTL T 
 GROUP BY T.ACCESS_MODE, T.APP_SVC_ID

    나는 NDA 내 where 절을 잘라했다,하지만 당신은 아이디어를 얻을.

  12. ==============================

    12.난이 도움이 될 생각 - CASE NULL로 컬럼 값이 중복 인 경우 - 다음은 LISTAGG 문자열에 추가 아니에요 :

    난이 도움이 될 생각 - CASE NULL로 컬럼 값이 중복 인 경우 - 다음은 LISTAGG 문자열에 추가 아니에요 :

    with test_data as 
(
      select 1 as col1, 2 as col2, 'Smith' as created_by from dual
union select 1, 2, 'John' from dual
union select 1, 3, 'Ajay' from dual
union select 1, 4, 'Ram' from dual
union select 1, 5, 'Jack' from dual
union select 2, 5, 'Smith' from dual
union select 2, 6, 'John' from dual
union select 2, 6, 'Ajay' from dual
union select 2, 6, 'Ram' from dual
union select 2, 7, 'Jack' from dual
)
SELECT col1  ,
      listagg(col2 , ',') within group (order by col2 ASC) AS orig_value,
      listagg(CASE WHEN rwn=1 THEN col2 END , ',') within group (order by col2 ASC) AS distinct_value
from 
    (
    select row_number() over (partition by col1,col2 order by 1) as rwn, 
           a.*
    from test_data a
    ) a
GROUP BY col1

    결과 :

    COL1  ORIG         DISTINCT
1   2,2,3,4,5   2,3,4,5
2   5,6,6,6,7   5,6,7
  13. ==============================

    13.listagg ()는 당신이 그렇다면 다음 NULL, 다른 '새로운 가치', 이전 레코드가 같은 값을 가지고 있는지 분석하는 지연 () 함수를 사용할 수 그래서 첫 번째 단계에서, NULL 값을 무시합니다.

    listagg ()는 당신이 그렇다면 다음 NULL, 다른 '새로운 가치', 이전 레코드가 같은 값을 가지고 있는지 분석하는 지연 () 함수를 사용할 수 그래서 첫 번째 단계에서, NULL 값을 무시합니다.

    WITH tab AS 
(           
          SELECT 1 as col1, 2 as col2, 'Smith' as created_by FROM dual
UNION ALL SELECT 1 as col1, 2 as col2, 'John'  as created_by FROM dual
UNION ALL SELECT 1 as col1, 3 as col2, 'Ajay'  as created_by FROM dual
UNION ALL SELECT 1 as col1, 4 as col2, 'Ram'   as created_by FROM dual
UNION ALL SELECT 1 as col1, 5 as col2, 'Jack'  as created_by FROM dual
)
SELECT col1
     , CASE 
       WHEN lag(col2) OVER (ORDER BY col2) = col2 THEN 
         NULL 
       ELSE 
         col2 
       END as col2_with_nulls
     , created_by
  FROM tab;

    결과

          COL1 COL2_WITH_NULLS CREAT
---------- --------------- -----
         1               2 Smith
         1                 John
         1               3 Ajay
         1               4 Ram
         1               5 Jack

    두 번째 (2) NULL로 대체합니다. 지금 당신은 주변의 listagg ()와 SELECT를 래핑 할 수 있습니다.

    WITH tab AS 
(           
          SELECT 1 as col1, 2 as col2, 'Smith' as created_by FROM dual
UNION ALL SELECT 1 as col1, 2 as col2, 'John'  as created_by FROM dual
UNION ALL SELECT 1 as col1, 3 as col2, 'Ajay'  as created_by FROM dual
UNION ALL SELECT 1 as col1, 4 as col2, 'Ram'   as created_by FROM dual
UNION ALL SELECT 1 as col1, 5 as col2, 'Jack'  as created_by FROM dual
)
SELECT listagg(col2_with_nulls, ',') WITHIN GROUP (ORDER BY col2_with_nulls) col2_list
  FROM ( SELECT col1
              , CASE WHEN lag(col2) OVER (ORDER BY col2) = col2 THEN NULL ELSE col2 END as col2_with_nulls
              , created_by
           FROM tab );

    결과

    COL2_LIST
---------
2,3,4,5

    너무 여러 열을 통해이 작업을 수행 할 수 있습니다.

    WITH tab AS 
(           
          SELECT 1 as col1, 2 as col2, 'Smith' as created_by FROM dual
UNION ALL SELECT 1 as col1, 2 as col2, 'John'  as created_by FROM dual
UNION ALL SELECT 1 as col1, 3 as col2, 'Ajay'  as created_by FROM dual
UNION ALL SELECT 1 as col1, 4 as col2, 'Ram'   as created_by FROM dual
UNION ALL SELECT 1 as col1, 5 as col2, 'Jack'  as created_by FROM dual
)
SELECT listagg(col1_with_nulls, ',') WITHIN GROUP (ORDER BY col1_with_nulls) col1_list
     , listagg(col2_with_nulls, ',') WITHIN GROUP (ORDER BY col2_with_nulls) col2_list
     , listagg(created_by, ',')      WITHIN GROUP (ORDER BY created_by) created_by_list
  FROM ( SELECT CASE WHEN lag(col1) OVER (ORDER BY col1) = col1 THEN NULL ELSE col1 END as col1_with_nulls
              , CASE WHEN lag(col2) OVER (ORDER BY col2) = col2 THEN NULL ELSE col2 END as col2_with_nulls
              , created_by
           FROM tab );

    결과

    COL1_LIST COL2_LIST CREATED_BY_LIST
--------- --------- -------------------------
1         2,3,4,5   Ajay,Jack,John,Ram,Smith
  14. ==============================

    14.당신은 정규식 교체를 통해 그것을 할 수 있습니다. 다음은 그 예이다 :

    당신은 정규식 교체를 통해 그것을 할 수 있습니다. 다음은 그 예이다 :

    -- Citations Per Year - Cited Publications main query. Includes list of unique associated core project numbers, ordered by core project number.
SELECT ptc.pmid AS pmid, ptc.pmc_id, ptc.pub_title AS pubtitle, ptc.author_list AS authorlist,
  ptc.pub_date AS pubdate,
  REGEXP_REPLACE( LISTAGG ( ppcc.admin_phs_org_code || 
    TO_CHAR(ppcc.serial_num,'FM000000'), ',') WITHIN GROUP (ORDER BY ppcc.admin_phs_org_code || 
    TO_CHAR(ppcc.serial_num,'FM000000')),
    '(^|,)(.+)(,\2)+', '\1\2')
  AS projectNum
FROM publication_total_citations ptc
  JOIN proj_paper_citation_counts ppcc
    ON ptc.pmid = ppcc.pmid
   AND ppcc.citation_year = 2013
  JOIN user_appls ua
    ON ppcc.admin_phs_org_code = ua.admin_phs_org_code
   AND ppcc.serial_num = ua.serial_num
   AND ua.login_id = 'EVANSF'
GROUP BY ptc.pmid, ptc.pmc_id, ptc.pub_title, ptc.author_list, ptc.pub_date
ORDER BY pmid;

    또한 여기에 게시 : 오라클 - 독특한 Listagg 값을

  15. ==============================

    15.다음과 같이 생성 listagg_clob 기능을 사용 :

    다음과 같이 생성 listagg_clob 기능을 사용 :

    create or replace package list_const_p
is
list_sep varchar2(10) := ',';
end list_const_p;
/
sho err

create type listagg_clob_t as object(
v_liststring varchar2(32767),
v_clob clob,
v_templob number,

static function ODCIAggregateInitialize(
sctx IN OUT listagg_clob_t
) return number,
member function ODCIAggregateIterate(
self IN OUT listagg_clob_t, value IN varchar2
) return number,
member function ODCIAggregateTerminate(
self IN OUT listagg_clob_t, returnValue OUT clob, flags IN number
) return number,
member function ODCIAggregateMerge(
self IN OUT listagg_clob_t, ctx2 IN OUT listagg_clob_t
) return number
);
/
sho err

create or replace type body listagg_clob_t is

static function ODCIAggregateInitialize(sctx IN OUT listagg_clob_t)
return number is
begin
sctx := listagg_clob_t('', '', 0);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(
self IN OUT listagg_clob_t,
value IN varchar2
) return number is
begin
if nvl(lengthb(v_liststring),0) + nvl(lengthb(value),0) <= 4000 then
self.v_liststring:=self.v_liststring || value || list_const_p.list_sep;
else
if self.v_templob = 0 then
dbms_lob.createtemporary(self.v_clob, true, dbms_lob.call);
self.v_templob := 1;
end if;
dbms_lob.writeappend(self.v_clob, length(self.v_liststring), v_liststring);
self.v_liststring := value || list_const_p.list_sep;
end if;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(
self IN OUT listagg_clob_t,
returnValue OUT clob,
flags IN number
) return number is
begin
if self.v_templob != 0 then
dbms_lob.writeappend(self.v_clob, length(self.v_liststring), self.v_liststring);
dbms_lob.trim(self.v_clob, dbms_lob.getlength(self.v_clob) - 1);
else
self.v_clob := substr(self.v_liststring, 1, length(self.v_liststring) - 1);
end if;
returnValue := self.v_clob;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT listagg_clob_t, ctx2 IN OUT listagg_clob_t) return number is
begin
if ctx2.v_templob != 0 then
if self.v_templob != 0 then
dbms_lob.append(self.v_clob, ctx2.v_clob);
dbms_lob.freetemporary(ctx2.v_clob);
ctx2.v_templob := 0;
else
self.v_clob := ctx2.v_clob;
self.v_templob := 1;
ctx2.v_clob := '';
ctx2.v_templob := 0;
end if;
end if;
if nvl(lengthb(self.v_liststring),0) + nvl(lengthb(ctx2.v_liststring),0) <= 4000 then
self.v_liststring := self.v_liststring || ctx2.v_liststring;
ctx2.v_liststring := '';
else
if self.v_templob = 0 then
dbms_lob.createtemporary(self.v_clob, true, dbms_lob.call);
self.v_templob := 1;
end if;
dbms_lob.writeappend(self.v_clob, length(self.v_liststring), self.v_liststring);
dbms_lob.writeappend(self.v_clob, length(ctx2.v_liststring), ctx2.v_liststring);
self.v_liststring := '';
ctx2.v_liststring := '';
end if;
return ODCIConst.Success;
end;
end;
/
sho err

CREATE or replace FUNCTION listagg_clob (input varchar2) RETURN clob
PARALLEL_ENABLE AGGREGATE USING listagg_clob_t;
/
sho err

  16. ==============================

    16.나는 정규 표현식을 사용하여이 문제를 처리 할 수있는 기능을 썼다. 매개 변수의는 다음과 같습니다 1) listagg 호출 자체 구분 기호의 2) 반복

    나는 정규 표현식을 사용하여이 문제를 처리 할 수있는 기능을 썼다. 매개 변수의는 다음과 같습니다 1) listagg 호출 자체 구분 기호의 2) 반복

    create or replace function distinct_listagg
  (listagg_in varchar2,
   delimiter_in varchar2)

   return varchar2
   as
   hold_result varchar2(4000);
   begin

   select rtrim( regexp_replace( (listagg_in)
      , '([^'||delimiter_in||']*)('||
      delimiter_in||'\1)+($|'||delimiter_in||')', '\1\3'), ',')
      into hold_result
      from dual;

return hold_result;

end;

    이제 정규 표현식에게이 작업을 수행 할 때마다 반복하지 않아도 간단하게 말 :

    select distinct_listagg(
                       listagg(myfield,', ') within group (order by 1),
                       ', '
                       )
     from mytable;
  17. ==============================

    17.쉼표를 사용하면 연결된 값의 특정 순서를 필요로하지 않으며, 분리 될 수 있다면, 당신은 할 수 있습니다 :

    쉼표를 사용하면 연결된 값의 특정 순서를 필요로하지 않으며, 분리 될 수 있다면, 당신은 할 수 있습니다 :

    select col1, stragg(distinct col2)
  from table
 group by col1
  18. ==============================

    18.나는이의 DISTINCT 버전이 필요하고이 하나가 운동을 얻었다.

    나는이의 DISTINCT 버전이 필요하고이 하나가 운동을 얻었다.

    RTRIM(REGEXP_REPLACE(
                       (value, ', ') WITHIN GROUP( ORDER BY value)), 
                            '([^ ]+)(, \1)+','\1'),', ')
  19. ==============================

    19.LISTAGG 함께 한 성가신 측면은 연결된 문자열의 총 길이는 4000 자 (SQL에서 VARCHAR2에 대한 제한)를 초과하는 경우, 오류가 아래가 12.1 개까지 오라클 버전에서 관리하기가 어렵있는 슬로우됩니다 있다는 것입니다

    LISTAGG 함께 한 성가신 측면은 연결된 문자열의 총 길이는 4000 자 (SQL에서 VARCHAR2에 대한 제한)를 초과하는 경우, 오류가 아래가 12.1 개까지 오라클 버전에서 관리하기가 어렵있는 슬로우됩니다 있다는 것입니다

    12cR2에 추가 된 새로운 기능은 LISTAGG의 ON OVERFLOW 절입니다. 같을 것이다이 절을 포함한 쿼리 :

    SELECT pid, LISTAGG(Desc, ' ' on overflow truncate) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;

    위는 4000 개 문자로 출력이 제한됩니다하지만 ORA-01489 오류가 발생하지 않습니다.

    이들은 ON 용량 초과 조항의 추가 옵션 중 일부입니다 :

  20. ==============================

    20.나는이 저장 기능을 구현 :

    나는이 저장 기능을 구현 :

    CREATE TYPE LISTAGG_DISTINCT_PARAMS AS OBJECT (ELEMENTO VARCHAR2(2000), SEPARATORE VARCHAR2(10));

CREATE TYPE T_LISTA_ELEMENTI AS TABLE OF VARCHAR2(2000);

CREATE TYPE T_LISTAGG_DISTINCT AS OBJECT (

    LISTA_ELEMENTI T_LISTA_ELEMENTI,
        SEPARATORE VARCHAR2(10),

    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX  IN OUT            T_LISTAGG_DISTINCT) 
                    RETURN NUMBER,

    MEMBER FUNCTION ODCIAGGREGATEITERATE   (SELF  IN OUT            T_LISTAGG_DISTINCT, 
                                            VALUE IN                    LISTAGG_DISTINCT_PARAMS ) 
                    RETURN NUMBER,

    MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF         IN     T_LISTAGG_DISTINCT,
                                            RETURN_VALUE OUT    VARCHAR2, 
                                            FLAGS        IN     NUMBER      )
                    RETURN NUMBER,

    MEMBER FUNCTION ODCIAGGREGATEMERGE       (SELF               IN OUT T_LISTAGG_DISTINCT,
                                                                                        CTX2                 IN         T_LISTAGG_DISTINCT    )
                    RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY T_LISTAGG_DISTINCT IS 

    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LISTAGG_DISTINCT) RETURN NUMBER IS 
    BEGIN
                SCTX := T_LISTAGG_DISTINCT(T_LISTA_ELEMENTI() , ',');
        RETURN ODCICONST.SUCCESS;
    END;

    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LISTAGG_DISTINCT, VALUE IN LISTAGG_DISTINCT_PARAMS) RETURN NUMBER IS
    BEGIN

                IF VALUE.ELEMENTO IS NOT NULL THEN
                        SELF.LISTA_ELEMENTI.EXTEND;
                        SELF.LISTA_ELEMENTI(SELF.LISTA_ELEMENTI.LAST) := TO_CHAR(VALUE.ELEMENTO);
                        SELF.LISTA_ELEMENTI:= SELF.LISTA_ELEMENTI MULTISET UNION DISTINCT SELF.LISTA_ELEMENTI;
                        SELF.SEPARATORE := VALUE.SEPARATORE;
                END IF;
        RETURN ODCICONST.SUCCESS;
    END;

    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LISTAGG_DISTINCT, RETURN_VALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
      STRINGA_OUTPUT            CLOB:='';
            LISTA_OUTPUT                T_LISTA_ELEMENTI;
            TERMINATORE                 VARCHAR2(3):='...';
            LUNGHEZZA_MAX           NUMBER:=4000;
    BEGIN

                IF SELF.LISTA_ELEMENTI.EXISTS(1) THEN -- se esiste almeno un elemento nella lista

                        -- inizializza una nuova lista di appoggio
                        LISTA_OUTPUT := T_LISTA_ELEMENTI();

                        -- riversamento dei soli elementi in DISTINCT
                        LISTA_OUTPUT := SELF.LISTA_ELEMENTI MULTISET UNION DISTINCT SELF.LISTA_ELEMENTI;

                        -- ordinamento degli elementi
                        SELECT CAST(MULTISET(SELECT * FROM TABLE(LISTA_OUTPUT) ORDER BY 1 ) AS T_LISTA_ELEMENTI ) INTO LISTA_OUTPUT FROM DUAL;

                        -- concatenazione in una stringa                        
                        FOR I IN LISTA_OUTPUT.FIRST .. LISTA_OUTPUT.LAST - 1
                        LOOP
                            STRINGA_OUTPUT := STRINGA_OUTPUT || LISTA_OUTPUT(I) || SELF.SEPARATORE;
                        END LOOP;
                        STRINGA_OUTPUT := STRINGA_OUTPUT || LISTA_OUTPUT(LISTA_OUTPUT.LAST);

                        -- se la stringa supera la dimensione massima impostata, tronca e termina con un terminatore
                        IF LENGTH(STRINGA_OUTPUT) > LUNGHEZZA_MAX THEN
                                    RETURN_VALUE := SUBSTR(STRINGA_OUTPUT, 0, LUNGHEZZA_MAX - LENGTH(TERMINATORE)) || TERMINATORE;
                        ELSE
                                    RETURN_VALUE:=STRINGA_OUTPUT;
                        END IF;

                ELSE -- se non esiste nessun elemento, restituisci NULL

                        RETURN_VALUE := NULL;

                END IF;

        RETURN ODCICONST.SUCCESS;
    END;

    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LISTAGG_DISTINCT, CTX2 IN T_LISTAGG_DISTINCT) RETURN NUMBER IS
    BEGIN
        RETURN ODCICONST.SUCCESS;
    END;

END; -- fine corpo

CREATE
FUNCTION LISTAGG_DISTINCT (INPUT LISTAGG_DISTINCT_PARAMS) RETURN VARCHAR2
    PARALLEL_ENABLE AGGREGATE USING T_LISTAGG_DISTINCT;

// Example
SELECT LISTAGG_DISTINCT(LISTAGG_DISTINCT_PARAMS(OWNER, ', ')) AS LISTA_OWNER
FROM SYS.ALL_OBJECTS;

    미안하지만 (매우 큰 세트에 대한) 어떤 경우, 오라클은이 오류를 반환 할 수 있습니다 :

    Object or Collection value was too large. The size of the value
might have exceeded 30k in a SORT context, or the size might be
too big for available memory.

    하지만 이것은 시작의 좋은 점이라고 생각)

  21. ==============================

    21.COL1이 순서를 유지리스트로 집계 문자열 (COL2)를 의미하는 N 다음 나중에 병합 COL1 중복을 의미 COL1에 의해 그룹으로 중복 처리에 의해 테이블 ​​그룹에서 (COL2에 의한 주문) 그룹 내에서 COL1, listaggr (COL2를, ',')를 선택 한 그룹이다. 그것이 있어야로 아마이 깨끗하고 간단 보인다 경우에 당신이 잘하는 것처럼 COL3를 원한다면 당신은 그룹 내에서 (COL2에 의한 주문) 그룹 내에서 ( ',', COL2)를 선택 COL1, listaggr은 그) (한 번 더 listagg을 추가, listaggr (COL3를, ',')가 필요합니다 COL1 의해 테이블 ​​그룹 (COL3 의해 순서)

    COL1이 순서를 유지리스트로 집계 문자열 (COL2)를 의미하는 N 다음 나중에 병합 COL1 중복을 의미 COL1에 의해 그룹으로 중복 처리에 의해 테이블 ​​그룹에서 (COL2에 의한 주문) 그룹 내에서 COL1, listaggr (COL2를, ',')를 선택 한 그룹이다. 그것이 있어야로 아마이 깨끗하고 간단 보인다 경우에 당신이 잘하는 것처럼 COL3를 원한다면 당신은 그룹 내에서 (COL2에 의한 주문) 그룹 내에서 ( ',', COL2)를 선택 COL1, listaggr은 그) (한 번 더 listagg을 추가, listaggr (COL3를, ',')가 필요합니다 COL1 의해 테이블 ​​그룹 (COL3 의해 순서)

  22. ==============================

    22.@a_horse_with_no_name에서 언급 한 바와 같이 SELECT DISTINCT를 사용하여 ... 서브 쿼리의 일환으로 LISTAGG를 호출하기 전에하는 것은, 아마 간단한 쿼리에 대한 최선의 방법입니다

    @a_horse_with_no_name에서 언급 한 바와 같이 SELECT DISTINCT를 사용하여 ... 서브 쿼리의 일환으로 LISTAGG를 호출하기 전에하는 것은, 아마 간단한 쿼리에 대한 최선의 방법입니다

    그러나 더 복잡한 쿼리, 그것은 가능하지 않을 수도 있고, 쉽고, 이러한 목표를 달성 할 수 있습니다. 나는이 분석 함수를 사용하여 최고-N 방식을 사용하고 그 시나리오에 올라와 있었다.

    그래서 COLLECT 집계 함수를 발견했다. 사용할 수있는 UNIQUE 또는 DISTINCT 수정을 가지고 설명되어 있습니다. 단 10g에, 조용히 (이 오류없이 수정을 무시) 실패합니다. 그러나 다른 답변에서,이 문제를 극복하기 위해,이 솔루션에 온 :

    SELECT
  ...
  (
    SELECT LISTAGG(v.column_value,',') WITHIN GROUP (ORDER BY v.column_value)
    FROM TABLE(columns_tab) v
  ) AS columns,
  ...
FROM (
  SELECT
    ...
    SET(CAST(COLLECT(UNIQUE some_column ORDER BY some_column) AS tab_typ)) AS columns_tab,
    ...
)

    기본적으로, SET를 사용하여, 나는 내 컬렉션에 중복을 제거합니다.

    당신은 여전히 ​​기본 수집 유형으로 tab_typ을 정의해야하고, VARCHAR의 경우, 이것은 예를 들면 다음과 같습니다

    CREATE OR REPLACE type tab_typ as table of varchar2(100)
/

    또한 당신이 (또는 그 이상)의 열 세 번째에 아직 집계 할 수 있습니다 멀티 열 상황에 @a_horse_with_no_name의 대답에 수정 같은 :

    select
  col1, 
  listagg(CASE rn2 WHEN 1 THEN col2 END, ',') within group (order by col2) AS col2_list,
  listagg(CASE rn3 WHEN 1 THEN col3 END, ',') within group (order by col3) AS col3_list,
  SUM(col4) AS col4
from (
  select
    col1, 
    col2,
    row_number() over (partition by col1, col2 order by null) as rn2,
    row_number() over (partition by col1, col3 order by null) as rn3
  from foo
)
group by col1;

    쿼리에 WHERE 조건으로 RN = 1을 떠날 경우, 당신은 잘못 다른 열을 집계합니다.

  23. ==============================

    23.여러 listagg의를 처리하는 가장 간단한 방법은 선택 별개에서 해당 컬럼의 listagg을 포함하는 컬럼 당 1 (하위 쿼리 요소)를 사용하는 것입니다 :

    여러 listagg의를 처리하는 가장 간단한 방법은 선택 별개에서 해당 컬럼의 listagg을 포함하는 컬럼 당 1 (하위 쿼리 요소)를 사용하는 것입니다 :

        WITH tab AS 
    (           
        SELECT 1 as col1, 2 as col2, 3 as col3, 'Smith' as created_by FROM dual
        UNION ALL SELECT 1 as col1, 2 as col2, 3 as col3,'John'  as created_by FROM dual
        UNION ALL SELECT 1 as col1, 3 as col2, 4 as col3,'Ajay'  as created_by FROM dual
        UNION ALL SELECT 1 as col1, 4 as col2, 4 as col3,'Ram'   as created_by FROM dual
        UNION ALL SELECT 1 as col1, 5 as col2, 6 as col3,'Jack'  as created_by FROM dual
    )
    , getCol2 AS
    (
        SELECT  DISTINCT col1, listagg(col2,',') within group (order by col2)  over (partition by col1) AS col2List
        FROM ( SELECT DISTINCT col1,col2 FROM tab)
    )
    , getCol3 AS
    (
        SELECT  DISTINCT col1, listagg(col3,',') within group (order by col3)  over (partition by col1) AS col3List
        FROM ( SELECT DISTINCT col1,col3 FROM tab)
    )
    select col1,col2List,col3List
    FROM getCol2
    JOIN getCol3
    using (col1)

    어떤 준다 :

    col1  col2List  col3List
1     2,3,4,5   3,4,6

    24. from https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL은 전체 테이블을 바꾸어  (0) 2020.03.19
[SQL] SQL 아이덴티티 (일련 번호)는 트랜잭션 롤백으로도 증가  (0) 2020.03.19
[SQL] 어떻게 마이크로 소프트 SQL 서버와 LIMIT를 구현하는 방법?  (0) 2020.03.19
[SQL] MySQL의 트리거 업데이트 후 행이 변경된 경우에만  (0) 2020.03.19
[SQL] 설명 자체 조인  (0) 2020.03.19
,

티스토리툴바