Главная > oracle, SQL > Функция LISTAGG (объединение строк)


Функция LISTAGG (объединение строк)

LISTAGG упорядочивает данные, объединенные в группы конструкцией ORDER BY, затем соединяет указаный столбец measure_expr.

1) Как одиночная агрегатная функция, LISTAGG обрабатывает все строки и возвращает одно значение.

2) Как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы определенной в GROUP BY.

3) Как аналитическая функция, LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_partition_clause.

Пример агрегатной функции

SQL> select deptno, LISTAGG(t.ename, ', ') WITHIN GROUP (order by t.empno) as ename_list
  2  from scott.emp t
  3  group by deptno;

DEPTNO ENAME_LIST
------ --------------------------------------------------------------------------------
    10 CLARK, KING, MILLER
    20 SMITH, JONES, SCOTT, ADAMS, FORD, SMIT2
    30 ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES

Пример аналитической функции

SQL> select deptno,
  2         ename,
  3         job,
  4         hiredate, listagg(t.ename, ', ') 
  6           WITHIN GROUP(order by t.empno) over(partition by deptno) as ename_list
  7  from scott.emp t
  8  where hiredate > to_date('01-01-1982','dd-mm-yyyy');

DEPTNO ENAME      JOB       HIREDATE    ENAME_LIST
------ ---------- --------- ----------- -----------------------
    10 MILLER     CLERK     23.01.1982  MILLER
    20 SCOTT      ANALYST   09.12.1982  SCOTT, ADAMS, SMIT2
    20 ADAMS      CLERK     12.01.1983  SCOTT, ADAMS, SMIT2
    20 SMIT2      CLERK     25.07.2011  SCOTT, ADAMS, SMIT2

Oracle Database SQL Language Reference 11g Release 2 : LISTAGG

Похожие записи:

  1. 20 Май 2013 в 09:43 | #1

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

  2. MErdock
    8 Ноябрь 2013 в 12:56 | #2

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

  3. MErdock
    1 Июль 2015 в 15:16 | #3

    @MErdock

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

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

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

  4. Алексей
    15 Октябрь 2015 в 17:04 | #4

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

  5. 21 Октябрь 2015 в 12:03 | #5

    @Алексей
    К сожалению, мне такой способ неизвестен. В этом случае надо использовать альтернативные методы.

  6. Ярослав
    9 Декабрь 2015 в 14:18 | #6

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

  7. 10 Декабрь 2015 в 12:25 | #7

    @Ярослав
    А пример есть? А то я таких не знаю способов )

    SQL> select substr(LISTAGG(level, ', ') WITHIN GROUP (order by level),1,50) as test from dual
      2  connect by level< =4000
      3  /
    select substr(LISTAGG(level, ', ') WITHIN GROUP (order by level),1,50) as test from dual
    connect by level<=4000
    ORA-01489: результат строковой конкатенации слишком велик

  8. Андрей
    4 Март 2016 в 11:48 | #8

    @Алексей
    Если вас устроит 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
  9. Аноним
    21 Сентябрь 2016 в 10:24 | #9

    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

  10. Mr.Grizz
    6 Октябрь 2016 в 17:09 | #10

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

  11. Аноним
    19 Октябрь 2016 в 12:53 | #11

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

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

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

  12. Аноним
    19 Октябрь 2016 в 13:01 | #12

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

  13. l___RUS___l
    15 Ноябрь 2016 в 05:15 | #13

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

  14. l___RUS___l
    15 Ноябрь 2016 в 05:42 | #14

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

  15. 18 Январь 2017 в 13:10 | #15

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

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

  16. Sonia
    23 Март 2017 в 13:03 | #16

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

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

  17. Евдоким
    11 Октябрь 2017 в 11:48 | #17

    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;

    Вот пример

  18. 12 Октябрь 2017 в 11:27 | #18

    @Евдоким
    Почему вы решили, что тут убираются повторы? Это видимо просто частный случай данных.
    1) У меня нет такой таблицы, поэтому вот пример, где видно что повторы остались:

    with RTDM_SAS_LOG as 
    (select 1 id, 1 val from dual
     union all
     select 2, 1 from dual
     union all
     select 3, 2 from dual 
     union all
     select 4, 3 from dual  
     union all
     select 5, 2 from dual   
    )
    SELECT LISTAGG(val, ',') WITHIN GROUP (ORDER BY id) as list_val FROM RTDM_SAS_LOG rsa
    
    LIST_VAL
    --------------------------------------------------------------------------------
    1,1,2,3,2
    

  19. Eddy
    21 Февраль 2020 в 15:32 | #19

    Пример как на Орасле 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
    );

  20. Аноним
    27 Май 2021 в 16:16 | #20

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

  1. Пока что нет уведомлений.