LISTAGGOracle 에서 기능 을 사용하려고합니다 . 해당 열에 대한 고유 한 값만 얻고 싶습니다. 함수 나 프로 시저를 만들지 않고 고유 한 값만 얻을 수있는 방법이 있습니까?
col1 col2 Created_by 1 2 스미스 요한 1 서 2 1 3 아제이 1 4 램 1 5 잭
col1과 LISTAGGcol2 를 선택해야합니다 (열 3은 고려되지 않음). 이렇게하면 다음과 같은 결과가 나타납니다 LISTAGG. [2,2,3,4,5]
여기서 중복 된 ‘2’를 제거해야합니다. col1에 대한 col2의 고유 한 값만 필요합니다.
답변
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;
답변
문제를 해결하는 방법은 다음과 같습니다.
select
regexp_replace(
'2,2,2.1,3,3,3,3,4,4'
,'([^,]+)(,\1)*(,|$)', '\1\3')
from dual
보고
2,2.1,3,4
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 개 이상의 문자열 크기 또는 큰 문자열 크기의 항목이 많은 경우 ‘문자열 연결 결과가 너무 깁니다’라는 오라클 문자열 크기 제한에 부딪 힐 수 있습니다.
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;
(그렇게 간단하지) 또 다른 해결책은 희망 오라클 문자열 크기 제한을 피하기 위해 – 문자열 크기가이 게시물에 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(),
'<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-항목 내에 포함 된 항목 예 : 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
답변
문서화되지 않은 wm_concat기능을 사용할 수 있습니다 .
select col1, wm_concat(distinct col2) col2_list
from tab1
group by col1;
이 함수는 dbms_lob.substrclob를 varchar2로 변환 하는 데 사용할 수있는 경우 clob 열을 반환합니다 .
답변
먼저 값을 그룹화하여이 문제를 극복 한 다음 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)
하나의 전체 테이블 액세스, 비교적 복잡한 쿼리로 확장하기 쉽습니다.
답변
이 변환을 여러 열에 적용하려는 경우 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
이것은 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64 비트 프로덕션입니다.
DISTINCT 및 ORDER 방법이 없기 때문에 STRAGG를 사용할 수 없습니다.
관심있는 모든 열을 추가하기 때문에 성능이 선형 적으로 확장됩니다. 위의 작업은 77K 행에 대해 3 초가 걸렸습니다. 단 한 번의 롤업의 경우 .172 초입니다. 한 번의 패스로 테이블의 여러 열을 구별하는 방법이 있습니다.
답변
여러 열에 걸쳐 고유 한 값을 원하고 정렬 순서를 제어하고 사라지는 문서화되지 않은 함수를 사용하지 않고 하나 이상의 전체 테이블 스캔을 원하지 않는 경우이 구성이 유용 할 수 있습니다.
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
);
답변
“구별 한”부분을 만드는 전용 함수를 만드는 것은 어떻습니까?
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;
