APPS-ORACLE.RU

Жаль, что LISTAGG не позволяет использовать DISTINCT, чтобы убрать дубли перед агрегацией-конкатенацией (как в самописанной STRAGG). Но, несомненный плюс, это возможность сортировки элементов в конкатенированной строке (чего не было в STRAGG).

@Fima
Но всегда есть возможность из полученной строки удалить дубликаты.

@MErdock

regexp_replace(regexp_replace(listagg(field, ', ') within group(order by field) || ', ', '([0-9]{1,3}, )\1{1,}', '\1'), '..$', '')

Если использовать listagg без сортировки, то регулярное выражение будет сложнее

p.s.: да я ответил сам себе почти через два года, потому что забыл как реализовывал тогда и потратил время на решение.

А можно как-то ограничить список строк для группировки в listagg ? Проблема такая: обрабатывается 100 строк, и общая строка listagg() получается больше 4000 символов. ХОтелось быть ограничить в этом случае число строк 30ью, например. Это реально?

@Алексей
Можно, например, обрезать финальный список строковой функцией до нужных размеров.

@Алексей
Если вас устроит CLOB, то можно не ограничивать. Я извернулся вот таким способом, потому как ограничивать и обрезать было нельзя:

select dbms_xmlgen.convert(rtrim(xmlagg(
    xmlelement(e, level, ', ').extract('//text()')
    order by level
  ).getClobVal(), ', '), 1) as test
from dual
  connect by level <= 4000

DISTINCT
select id, LISTAGG(name, ‘, ‘) WITHIN GROUP (order by name) as lic_list
from
(
select
distinct ld.obj_id as id, dl.short_name as name
from
dic_license dl,
license_det ld
where
dl.id = ld.dic_license_id
)
group by id

@MErdock
У меня почему то после такой регулярки ничего не просходит со строкой (.

@Fima
А почему нельзя так-

select deptno, LISTAGG(t.ename, ‘, ‘) WITHIN GROUP (order by t.empno) as ename_list

from (select distinct deptno,ename from scott.emp ) t

@Mr.Grizz
а не потому, что уникальный id_obj не позволяет ?

@Алексей
У аналитической функции можно задать окно: Range/Rows
Окно задаётся после «order by»
Пример, listagg (поле_3,’,’) withing group (order by поле_2 rows 9 preceding) over (partition by поле_1)
таким образом должны захватиться текущая строка выборки и 9 предыдущих.

@l___RUS___l
закосячил. Listagg не работает с окном.
Необходимо использовать str_agg

у меня получилось сделать нормально LISTAGG в группировке и без повторений. Фишка в том, что нужно в подселекте сделать основную группировку, а во внешнем селекте сделать группировку с листаг — к этому моменту в подзапросе уже будут сгруппированы\удалены повторения и листагг их пропустит.

Ещё момент с двумя листагг в одном запросе — может потребоваться ещё подселект. для одного листагг — один селект с группировкой.

AlexB :
у меня получилось сделать нормально LISTAGG в группировке и без повторений.

А можно пример?

SELECT LISTAGG(APPLICID, ‘,’) WITHIN GROUP (ORDER BY APPLICID) into er_desc FROM RTDM_SAS_LOG rsa
WHERE rsa.EVENT_CODE NOT IN (‘0′,’4′,’2203′,’2205′)
AND ROUND(SYSTIMESTAMP,’HH’) — ROUND(rsa.TIME_STAMP,’HH’) < day;

Вот пример

Пример как на Орасле 11 получить несколько LISTAGG в одной группировке и с distinct к каждому полю.
Работает со скоростью wm_concat.
Стащено со stackoverflow.com и проверено на коде нескольких вьюх…

Внутренняя группировка строит колекции и делает агрегаты с групой.
Внешний запрос выполняет Listagg подзапросом к коллекции.
Получаем и distinct и нет ограничений на количество Listagg в одной групировке.

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
);

А чтобы не подвисал при длинных строках есть ON OVERFLOW TRUNCATE ‘…’
LISTAGG(distinct column_value, ‘; ‘ ON OVERFLOW TRUNCATE ‘…’)