Я пытаюсь использовать функцию LISTAGG
в Oracle. Я хотел бы получить только отдельные значения для этого столбца. Есть ли способ, которым я могу получить только отдельные значения без создания функции или процедуры?
col1 col2 Created_by 1 2 Smith 1 2 John 1 3 Ajay 1 4 Ram 1 5 Jack
Мне нужно выбрать col1 и LISTAGG
col2 (столбец 3 не рассматривается). Когда я это делаю, я получаю что-то вроде этого в результате LISTAGG
: [2,2,3,4,5]
Мне нужно удалить дубликат '2' здесь; Мне нужны только отдельные значения col2 для col1.
Вы имеете в виду что-то вроде этого:
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;
listagg
является единственной агрегатной функцией в запросе, это должно быть сделано. Однако объединить его с другими агрегатными функциями сложнее.
Здесь как решить вашу проблему.
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. Результат конкатенации строк слишком длинный. Таким образом, максимальное количество членов в каждой группе. Это будет работать только в том случае, если будет нормально перечислять только первые члены. Если у вас очень длинные переменные строки, это может не сработать. вам придется экспериментировать.
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(),
'<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;
некоторые тестовые примеры - 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
элементы, содержащиеся в элементах, например. 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 поблагодарить Игоря! работает во всех случаях.
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
ORA-01489: result of string concatenation is too long
.
вы можете использовать недокументированную функцию wm_concat
.
select col1, wm_concat(distinct col2) col2_list
from tab1
group by col1;
эта функция возвращает столбец clob, если вы хотите, вы можете использовать dbms_lob.substr
для преобразования clob в varchar2.
Я преодолел эту проблему, сначала группируя значения, затем выполняю другую агрегацию с помощью 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 - 64bit Production.
Мне не удалось использовать STRAGG, потому что нет способа DISTINCT и ORDER.
Диапазон производительности линейно, что хорошо, так как я добавляю все столбцы, представляющие интерес. Вышеизложенное заняло 3 секунды для 77K строк. Всего за один раз, 0,172 секунды. Я делаю с тем, чтобы один раз различать несколько столбцов в таблице за один проход.
Чтобы обойти проблему длины строки, вы можете использовать XMLAGG
, который похож на listagg
, но возвращает 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
Если вам нужны разные значения в столбцах MULTIPLE, вы хотите контролировать порядок сортировки, не хотите использовать недокументированную функцию, которая может исчезнуть, и не хотите больше одного полного сканирования таблицы, вы можете найти эту конструкцию полезной:
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;
Дальнейшее уточнение @YoYo коррекции к подходу @a_horse_with_no_name row_number() с использованием DECODE vs. CASE (я видел здесь). Я вижу, что @Мартин Врбовский также имеет ответ на этот случай.
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;
select col1, listaggr(col2,',') within group(Order by col2) from table group by col1
означает объединение строк (col2) в список, поддерживающий порядок n, а затем обрабатывать дубликаты как группу по col1, что означает дублирование 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
Однако в более сложных запросах это может оказаться невозможным или легким для достижения этого. У меня это возникло в сценарии, использующем подход top-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;
Если вы оставите rn = 1
в качестве условия для запроса, вы бы неправильно заполнили другие столбцы.
Предстоящий Oracle 19c будет поддерживать DISTINCT
с LISTAGG
.
Эта функция идет с 19c:
SQL> select deptno, listagg (distinct sal,', ') within group (order by sal) 2 from scott.emp 3 group by deptno;
РЕДАКТИРОВАТЬ:
Функция агрегирования LISTAGG теперь поддерживает удаление дубликатов с помощью нового ключевого слова DISTINCT. Агрегатная функция LISTAGG упорядочивает строки для каждой группы в запросе в соответствии с выражением ORDER BY, а затем объединяет значения в одну строку. С новым ключевым словом DISTINCT дублирующиеся значения могут быть удалены из указанного выражения перед объединением в одну строку. Это избавляет от необходимости создавать сложную обработку запросов для поиска различных значений до использования агрегатной функции LISTAGG. С опцией DISTINCT, обработка для удаления повторяющихся значений может быть выполнена непосредственно в функции LISTAGG. В результате получается более простой, быстрый и эффективный SQL.
Я реализовал эту сохраненную функцию:
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.
но я думаю, что это хороший момент для начала;)
Один досадный аспект с LISTAGG
заключается в том, что если общая длина конкатенированной строки превышает 4000 символов (предел для VARCHAR2
в SQL), то возникает ошибка ниже, которую трудно обрабатывать в версиях Oracle до 12.1
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 символов, но не ORA-01489
ошибки ORA-01489
.
Вот некоторые из дополнительных опций ON OVERFLOW
:
ON OVERFLOW TRUNCATE 'Contd..'
: в конце строки отобразится 'Contd..'
(по умолчанию - ...
)ON OVERFLOW TRUNCATE ''
: отобразится 4000 символов без какой-либо завершающей строки.ON OVERFLOW TRUNCATE WITH COUNT
: отображает общее количество символов в конце после завершающих символов. Например: - " ...(5512)
"ON OVERFLOW ERROR
: Если вы ожидаете, что LISTAGG
завершится с ошибкой ORA-01489
(по умолчанию это значение по умолчанию).Я установил версию DISTINCT и получил эту работу.
RTRIM(REGEXP_REPLACE(
(value, ', ') WITHIN GROUP( ORDER BY value)),
'([^ ]+)(, \1)+','\1'),', ')
Если вам не нужен конкретный порядок конкатенированных значений, а разделитель может быть запятой, вы можете сделать:
select col1, stragg(distinct col2)
from table
group by col1
Самый простой способ обработать несколько listagg - использовать 1 WITH (коэффициент подзапроса) для каждого столбца, содержащего listagg этого столбца, из выделенного выделения:
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
Я написал функцию, которая обрабатывает это с помощью регулярных выражений. Параметры: 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;
Используйте функцию 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
Вы можете сделать это через замену 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
listagg() игнорирует значения NULL, поэтому на первом этапе вы можете использовать функцию lag() для анализа того, имела ли предыдущая запись одно и то же значение, если да, то 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. Теперь вы можете обернуть 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
Я думаю, что это могло бы помочь - СЛУЧАЙТЕ значение столбца 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
Кто-нибудь думал об использовании предложения 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, но вы поняли.
LISTAGG
. Похоже, у вас будет только один T.ACCESS_MODE
на строку, так как вы группируете по нему?