Главная > 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 в группировке и без повторений.

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

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