LISTAGG dans Oracle pour retourner des valeurs distinctes
j'essaie d'utiliser la fonction LISTAGG dans Oracle. Je voudrais obtenir uniquement les valeurs distinctes pour cette colonne. Y a-t-il un moyen pour que je n'obtienne que les valeurs distinctes sans créer une fonction ou une procédure?
col1 col2 Created_by 1 2 Smith 1 2 John 1 3 Ajay 1 4 Ram 1 5 Jack
je dois sélectionner col1 et le LISTAGG de col2 (la colonne 3 n'est pas considérée). Quand je fais ça, j'obtiens quelque chose comme ça à cause de LISTAGG : [2,2,3,4,5]
je dois supprimer le double " 2 " ici; je n'ai besoin que des valeurs distinctes de col2 par rapport à col1.
20 réponses
voulez-vous dire quelque chose comme ceci:
select listagg(the_column, ',') within group (order by the_column)
from (
select distinct the_column
from the_table
) t
si vous avez besoin de plus de colonnes, quelque chose comme ceci pourrait être ce que vous recherchez:
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;
Voici comment résoudre votre problème.
select
regexp_replace(
'2,2,2.1,3,3,3,3,4,4'
,'([^,]+)(,)*(,|$)', '')
from dual
retourne
2,2.1,3,4
réponse (Voir notes ci-dessous):
select col1,
regexp_replace(
listagg(
col2 , ',') within group (order by col2) -- sorted
,'([^,]+)(,)*(,|$)', '') )
from tableX
where rn = 1
group by col1;
Note: ce qui précède fonctionnera dans la plupart des cas - liste devrait être triée , vous pouvez avoir à couper tous les espaces de fuite et de tête en fonction de vos données.
Si vous avez beaucoup d'éléments dans un groupe > 20 ou grande chaîne de tailles que vous pourriez rencontrer oracle chaîne limite de taille 'résultat de la concaténation de chaîne est trop long" Afin de mettre un maximum sur les membres de chaque groupe. Cela ne fonctionnera que si ses ok pour répertorier uniquement les premiers membres. Si vous avez des chaînes de variables très longues, cela peut ne pas fonctionner. vous aurez à expérimenter.
select col1,
case
when count(col2) < 100 then
regexp_replace(
listagg(col2, ',') within group (order by col2)
,'([^,]+)(,)*(,|$)', '')
else
'Too many entries to list...'
end
from sometable
where rn = 1
group by col1;
une autre solution (pas si simple) pour éviter avec un peu de chance la taille limite de la chaîne oracle - la taille de la chaîne est limitée à 4000. Merci à ce post ici par utilisateur 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>',''),'([^,]+)(,)*(,|$)', ''),
','), -- 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;
certains des cas de test - FYI
regexp_replace('2,2,2.1,3,3,4,4','([^,]+)(,)+', '')
-> 2.1,3,4 Fail
regexp_replace('2 ,2 ,2.1,3 ,3 ,4 ,4 ','([^,]+)(,)+', '')
-> 2 ,2.1,3,4 Success - fixed length items
articles contenus dans des articles, par exemple: 2,21
regexp_replace('2.1,1','([^,]+)(,)+', '')
-> 2.1 Fail
regexp_replace('2 ,2 ,2.1,1 ,3 ,4 ,4 ','(^|,)(.+)(,)+', '')
-> 2 ,2.1,1 ,3 ,4 -- success - NEW regex
regexp_replace('a,b,b,b,b,c','(^|,)(.+)(,)+', '')
-> a,b,b,c fail!
v3-regex merci Igor! fonctionne tous les cas.
select
regexp_replace('2,2,2.1,3,3,4,4','([^,]+)(,)*(,|$)', '') ,
---> 2,2.1,3,4 works
regexp_replace('2.1,1','([^,]+)(,)*(,|$)', ''),
--> 2.1,1 works
regexp_replace('a,b,b,b,b,c','([^,]+)(,)*(,|$)', '')
---> a,b,c works
from dual
vous pouvez utiliser la fonction wm_concat non documentée.
select col1, wm_concat(distinct col2) col2_list
from tab1
group by col1;
cette fonction renvoie la colonne clob, si vous voulez vous pouvez utiliser dbms_lob.substr pour convertir clob en varchar2.
j'ai surmonté ce problème en regroupant les valeurs d'abord, puis en faisant une autre agrégation avec le listagg. Quelque chose comme ceci:
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)
un seul accès complet à la table, relativement facile à étendre à des requêtes plus complexes
si l'intention est d'appliquer cette transformation à plusieurs colonnes, j'ai étendu la solution de 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
il s'agit de la base de données Oracle 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.
Je n'ai pas pu utiliser STRAGG parce qu'il n'y a aucun moyen de distinguer et D'Ordonner.
échelles de Performance linéairement, ce qui est bien, puisque j'ajoute toutes les colonnes d'intérêt. Ce qui précède a pris 3 secondes pour 77K rows. Pour juste un correctif .172 secondes. Je fais avec il y avait un moyen de distinguer plusieurs colonnes dans un tableau en un seul passage.
si vous voulez des valeurs distinctes à travers plusieurs colonnes, si vous voulez contrôler l'ordre de tri, si vous ne voulez pas utiliser une fonction non documentée qui peut disparaître, et si vous ne voulez pas plus d'un balayage complet de la table, vous pouvez trouver cette construction utile:
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
);
pour contourner le problème de longueur de chaîne de caractères, vous pouvez utiliser XMLAGG qui est similaire à listagg mais qui renvoie un sabot.
vous pouvez ensuite analyser en utilisant regexp_replace et obtenir les valeurs uniques, puis le transformer en une chaîne de caractères en utilisant dbms_lob.substr() . Si vous avez un grand nombre de valeurs distinctes, vous manquerez d'espace de cette façon, mais pour beaucoup de cas, le code ci-dessous devrait fonctionner.
vous pouvez également changer les délimiteurs que vous utilisez. Dans mon cas, je voulais '-' au lieu de', ' mais vous devriez pouvoir remplacer les tirets dans mon code et utiliser des virgules si vous le voulez.
select col1,
dbms_lob.substr(ltrim(REGEXP_REPLACE(REPLACE(
REPLACE(
XMLAGG(
XMLELEMENT("A",col2)
ORDER BY col2).getClobVal(),
'<A>','-'),
'</A>',''),'([^-]*)(-)+($|-)',
''),'-'), 4000,1) as platform_mix
from table
Qu'en est-il de la création d'une fonction dédiée qui fera la partie "distincte":
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;
/
et ensuite l'utiliser pour faire l'agrégation:
SELECT col1, listagg_distinct(cast(collect(col_2) as str_t ), ', ')
FROM your_table
GROUP BY col_1;
Quelqu'un a-t-il pensé à utiliser une PARTITION par clause? Il a travaillé pour moi dans cette requête pour obtenir une liste des services d'application et l'accès.
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
j'ai dû supprimer ma clause où pour la NDA, mais vous avez l'idée.
je pense que cela pourrait aider-CASE la valeur des colonnes à NULL si c'est dupliqué - alors il n'est pas ajouté à la chaîne 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
résultats dans:
COL1 ORIG DISTINCT
1 2,2,3,4,5 2,3,4,5
2 5,6,6,6,7 5,6,7
listagg() ignore les valeurs nulles, donc dans un premier temps vous pouvez utiliser la fonction lag () pour analyser si l'enregistrement précédent avait la même valeur, si oui alors NULL, sinon 'nouvelle valeur'.
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;
résultats
COL1 COL2_WITH_NULLS CREAT
---------- --------------- -----
1 2 Smith
1 John
1 3 Ajay
1 4 Ram
1 5 Jack
noter que le second 2 est remplacé par nul. Maintenant vous pouvez envelopper un SELECT avec le listagg() autour de lui.
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 );
résultat
COL2_LIST
---------
2,3,4,5
vous pouvez recommencer plusieurs colonnes.
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 );
résultat
COL1_LIST COL2_LIST CREATED_BY_LIST
--------- --------- -------------------------
1 2,3,4,5 Ajay,Jack,John,Ram,Smith
vous pouvez le faire via RegEx replacement. Voici un exemple:
-- 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')),
'(^|,)(.+)(,)+', '')
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;
également publié ici: Oracle-unique Listagg valeurs
utiliser la fonction listagg_clob créée comme ceci:
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
j'ai écrit une fonction pour gérer ceci en utilisant des expressions régulières. Les paramètres sont: 1) la listagg appeler lui-même 2) une répétition du délimiteur
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||')+($|'||delimiter_in||')', ''), ',')
into hold_result
from dual;
return hold_result;
end;
Maintenant vous n'avez pas à répéter l'expression régulière chaque fois que vous faites ceci, dites simplement:
select distinct_listagg(
listagg(myfield,', ') within group (order by 1),
', '
)
from mytable;
la façon la plus simple de gérer plusieurs listagg est d'utiliser 1 avec (facteur de sous-titrage) par colonne contenant un listagg de cette colonne à partir d'un SELECT distinct:
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)
qui donne:
col1 col2List col3List
1 2,3,4,5 3,4,6
Si vous n'avez pas besoin d'un ordre particulier de valeurs concaténées, et le séparateur peut être une virgule, vous pouvez le faire:
select col1, stragg(distinct col2)
from table
group by col1
j'ai créé une version distincte de celui-ci et je l'ai fait fonctionner.
RTRIM(REGEXP_REPLACE(
(value, ', ') WITHIN GROUP( ORDER BY value)),
'([^ ]+)(, )+',''),', ')
un aspect ennuyeux avec LISTAGG est que si la longueur totale de chaîne concaténée dépasse 4000 caractères( limite pour VARCHAR2 en SQL ), l'erreur ci-dessous est lancée, ce qui est difficile à gérer dans les versions Oracle jusqu'à 12.1
ORA-01489: résultat de la concaténation de la chaîne est trop long
une nouvelle caractéristique ajoutée dans 12cR2 est la clause ON OVERFLOW de LISTAGG .
La requête incluant ceci clause ressemblerait à:
SELECT pid, LISTAGG(Desc, ' ' on overflow truncate) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;
ce qui précède limitera la sortie à 4000 caractères mais ne lancera pas l'erreur ORA-01489 .
ce sont quelques-unes des options supplémentaires de ON OVERFLOW clause:
-
ON OVERFLOW TRUNCATE 'Contd..': cela affichera'Contd..'à la fin de la chaîne (valeur par Défaut est...) -
ON OVERFLOW TRUNCATE '': cela affichera les 4000 caractères sans toute chaîne de terminaison. -
ON OVERFLOW TRUNCATE WITH COUNT: cela affichera le total nombre de caractères à la fin, après les caractères de fin. Par exemple:- '...(5512)' -
ON OVERFLOW ERROR: si vous vous attendez à ce que leLISTAGGéchoue avec leORA-01489erreur (qui est par défaut de toute façon ).
j'ai implémenté cette fonction stockée:
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;
je suis désolé, mais dans certains cas (pour un très grand ensemble), Oracle pourrait retourner cette erreur:
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.
mais je pense que c'est un bon point de départ ;)
Oracle 19c supportera DISTINCT avec LISTAGG .
LISTAGG avec option distincte :
cette caractéristique vient avec 19c:
SQL> select deptno, listagg (distinct sal,', ') within group (order by sal) 2 from scott.emp 3 group by deptno;