반응형
고유한 값을 반환하는 Oracle의 LISTAGG
있어요.LISTAGG기능을 합니다.나는 그 열에 대해 구별되는 값만 얻고 싶다.함수나 프로시저를 작성하지 않고 고유값만 취득할 수 있는 방법이 있습니까?
col1 col2 Created_by1 2 Smith1 2 존13 에이제이1 4 RAM1 5 잭
과 col1을 .LISTAGG(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;
Oracle 19C부터 내장되어 있습니다.여기를 참조해 주십시오.
18C 이전부터 그룹 내에서 시도합니다.여기를 참조해 주십시오.
그렇지 않으면 정규 표현을 사용합니다.
여기 당신의 문제를 해결하는 방법이 있습니다.
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 string size limit 'result of string connectation is too long'이 발생할 수 있습니다.
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으로 제한됩니다.사용자 3465996의 이 게시물 덕분에
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 - 일부 테스트 케이스 - 참고
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 - regex Thank 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;
열을 반환하며, clob 열을 반환할 수 .dbms_lob.substrclob varchar2로 합니다.
여러 열에서 고유한 값을 사용하고, 정렬 순서를 제어하고, 사라질 수 있는 문서화되지 않은 함수를 사용하지 않고, 두 개 이상의 전체 테이블 검색을 원하지 않는 경우 다음 구성이 유용할 수 있습니다.
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
);
먼저 값을 그룹화한 후 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 11.2.0 - 64.
DISIGNT ORDER는 SRAGGG를 사용합니다.
이치노모든 관심 열을 추가하므로 좋습니다.77K는 3K입니다.딱딱 、 172 . . .172 、 . 。테이블 내의 여러 열을 한 번에 구분할 수 있는 방법이 있었습니다.
는 【Oracle 19c】를 합니다.DISTINCTLISTAGG.
LISTAGG와 DISTING 옵션:
이 기능은 19c에 포함되어 있습니다.
SQL> select deptno, listagg (distinct sal,', ') within group (order by sal) 2 from scott.emp 3 group by deptno;
편집:
LISTAGG 집약 함수는 새로운 DISTINT 키워드를 사용하여 중복 제거를 지원합니다.LISTAG 집약 함수는 ORDER BY 식에 따라 쿼리 내 각 그룹의 행을 정렬하고 값을 단일 문자열로 연결합니다.새로운 DISTINCT 키워드를 사용하면 단일 문자열에 연결하기 전에 지정된 식에서 중복된 값을 삭제할 수 있습니다.이를 통해 집약 LISTAGG 함수를 사용하기 전에 개별 값을 찾기 위해 복잡한 쿼리 처리를 작성할 필요가 없어집니다.DISTINT 옵션을 사용하면 LISTAGG 함수 내에서 중복된 값을 제거하는 처리를 직접 수행할 수 있습니다.그 결과 SQL이 더 단순하고, 더 빠르고, 더 효율적입니다.
"구분" 부분을 만드는 전용 함수를 만드는 것은 어떨까요?
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;
하려면 , 「」를 해 주세요.XMLAGG 생각합니다.listagg딱지를 돌려줍니다.
'아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아,아.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
또한 DECORD vs CASE를 사용하여 @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;
listagg()는 NULL 값을 무시하기 때문에 첫 번째 단계에서는 lag() 함수를 사용하여 이전 레코드의 값이 동일한지 여부를 분석할 수 있습니다.그렇다면 NULL, 그렇지 않으면 'new value'입니다.
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로 대체됩니다.이제 SELECT를 listagg()로 랩할 수 있습니다.
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
다음 저장된 기능을 구현했습니다.
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;
죄송합니다. (매우 큰 집합의 경우) Oracle에서 다음 오류를 반환할 수 있습니다.
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.
하지만 저는 이것이 좋은 출발점이라고 생각합니다;)
PARTITION 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에 대한 '어디' 조항을 삭제해야 했지만, 무슨 말인지 아시겠죠?
열 값이 중복된 경우 NULL에 CASE하면 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
RegEx 교환을 통해 할 수 있습니다.다음은 예를 제시하겠습니다.
-- 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;
또한 여기에 게시: Oracle - 고유한 Listagg 값
저는 정규 표현을 사용해서 이것을 처리하는 함수를 썼습니다.in 파라미터는 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;
연결된 값의 특정 순서가 필요하지 않고 구분 기호를 쉼표로 사용할 수 있는 경우 다음을 수행할 수 있습니다.
select col1, stragg(distinct col2)
from table
group by col1
나는 이것의 DISTINT 버전을 주의해서 이것을 해결했다.
RTRIM(REGEXP_REPLACE(
(value, ', ') WITHIN GROUP( ORDER BY value)),
'([^ ]+)(, \1)+','\1'),', ')
한한 aspect with with with with with with 의 귀찮은 점LISTAGG의 총 ('4000'의 :VARCHAR2이 에러는 버전에서는 하기 어렵습니다.
ORA-01489: 문자열 연결 결과가 너무 깁니다.
은 12cR2 입니다.ON OVERFLOW의 절LISTAGG과 같습니다
SELECT pid, LISTAGG(Desc, ' ' on overflow truncate) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;
되지만 4000자, 4000자, 4000자, 4000자 등은 슬로우 .ORA-01489
입니다.ON OVERFLOW절:
ON OVERFLOW TRUNCATE 'Contd..': 표시됩니다.'Contd..'문자열 끝에 있습니다(디폴트는...)ON OVERFLOW TRUNCATE '': 종료 문자열 없이 4000자가 표시됩니다.ON OVERFLOW TRUNCATE WITH COUNT:종료문자뒤에끝에총문자수가표시됩니다.예:- '...(5512)'ON OVERFLOW ERROR: 이 경우,LISTAGG실패하다ORA-01489error(기본값입니다).
select col1, listaggr(col2,',') within group(Order by col2) from table group by col1즉, 문자열(col2)을 목록으로 집약하여 순서n을 유지한 후 중복된 것을 col1에 의해 그룹으로 처리합니다.즉, merge col1은 1개의 그룹에서 중복됩니다.col3를 원하는 경우 listagg()를 하나 더 추가해야 합니다.select col1, listaggr(col2,',') within group(Order by col2),listaggr(col3,',') within group(order by col3) from table group by col1
「」를 사용합니다.SELECT DISTINCT ...LISTAGG를 호출하기 전에 서브쿼리의 일부로 사용하는 것이 @a_horse_with_no_name에서 알 수 있듯이 간단한 쿼리에 가장 적합한 방법일 수 있습니다.
그러나 더 복잡한 쿼리에서는 이 작업이 불가능하거나 쉽지 않을 수 있습니다.분석 함수를 사용하여 상위 N개 접근 방식을 사용하는 시나리오에서 이 방법을 생각해 냈습니다.
그래서 집계 함수를 찾았습니다.이 문서에는 다음과 같은 내용이 기재되어 있습니다.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,
...
)
기본적으로 를 사용하여 컬렉션의 중복을 제거합니다.
'아까보다'의 합니다.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;
여기서 나가시면rn = 1쿼리의 where 조건으로 다른 열을 잘못 집계할 수 있습니다.
다음과 같이 작성된 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
여러 listagg를 처리하는 가장 간단한 방법은 선택한 고유 항목에서 해당 열의 listagg를 포함하는 열당 1개의 WITH(서브쿼리 팩터)를 사용하는 것입니다.
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
매우 간단 - 쿼리에서 선택한 구분과 함께 하위 쿼리를 사용합니다.
SELECT question_id,
LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM
(SELECT distinct question_id, element_id
FROM YOUR_TABLE)
GROUP BY question_id;
언급URL : https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values
반응형
'programing' 카테고리의 다른 글
| .js 파일 내에서 PHP 코드 실행 (0) | 2023.03.06 |
|---|---|
| 파일에 sqlplus 출력 쓰기 (0) | 2023.03.06 |
| Angularjs 라디오 버튼 (0) | 2023.03.06 |
| Google Chrome/Chromium 및 Safari에서 드래그 앤 드롭 파일을 업로드하시겠습니까? (0) | 2023.03.06 |
| xcode 8 Swift 3에 첨자 멤버가 없는 '임의' 유형 (0) | 2023.03.06 |