На протяжении всего времени работы с Oracle меня интересовал вопрос – почему при таком изобилии возможностей групповых вычислений и аналитических расчетов отсутствует такая простая и полезная функция как склейка строк группы записей в одну (т.е. агрегирование строк). В Oracle 11g Release 2 появилась функция listagg, позволяющая агрегировать строки группы в одну и работающая в двух режимах:
- групповка
- аналитическая функция
Синтаксис
LISTAGG( aggr_expr [, delimiter]) WITHIN GROUP (ORDER BY sort_expr1[, ]) [OVER (PARTITION BY [, ])]
listagg в режиме группировки
В этом режиме обязательными являются следующие параметры:
- агрегируемое выражение (aggr_expr)
- ключевое слово WITHIN GROUP
- сортировка (sort_expr)
Простейший вариант использования функции на примере стандартной схемы HR будет выглядеть следующим образом.
SELECT department_id AS deptno, LISTAGG(first_name, ',') WITHIN GROUP (ORDER BY first_name) AS emp FROM employees GROUP BY department_id;
В результате получаем список сотрудников через запятую для каждого отдела.
DEPTNO EMP ---------- ------------------------------------------------------------ 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
Сортировка агрегируемых значений может не совпадать с самими значениями, но присутствовать должна обязательно. Если порядок записей неважен, то в качестве выражения сортировки можно задать NULL. В этом случае, записи будут упорядочены по алфавиту.
В качестве разделителя может использоваться как литерал, так и групповое выражение. Например, поле, участвующее в группировке, и однострочная функция (CHR):
SELECT department_id AS deptno, LISTAGG(first_name, '(' || CHR(department_id+55) || '); ') WITHIN GROUP (ORDER BY hire_date) AS emp FROM employees GROUP BY department_id;
В результате, записи в строке будут разделены символом в скобках, одинаковым для всех записей группы:
DEPTNO EMP ---------- ------------------------------------------------------------ 10 CLARK(A); KING(A); MILLER 20 SMITH(K); JONES(K); FORD(K); SCOTT(K); ADAMS 30 ALLEN(U); WARD(U); BLAKE(U); TURNER(U); MARTIN(U); JAMES
Разделитель должно однозначно вычисляться для всех записей группы. В противном случае, будет получена ошибка. Например, некорректными выражениями разделителя будут:
CHR(employee_id) --по этому полю не выполняется группировка ROWNUM --этот псевдостолбец не группируется MAX(department_id) --групповая функция с группированным полем производит вторую группировку над уже сгруппированными данными
listagg в качестве аналитической функции
В этом случае используется конструкция OVER(). В режиме аналитических вычислений первый пример будет выглядеть следующим образом:
SELECT department_id AS deptno, first_name AS ename, hire_date, LISTAGG(first_name, ',') WITHIN GROUP (ORDER BY first_name) OVER (PARTITION BY department_id) AS emp FROM employees;
Т.к. аналитическая функция вычисляется отдельно для каждой записи и не группирует конечный результат, список сотрудников отдела будет выведен для каждого сотрудника организации.
DEPTNO ENAME HIRE_DATE EMP ---------- ---------- ----------- ------------------------------------- 10 CLARK 09.06.81 CLARK,KING,MILLER 10 KING 17.11.81 CLARK,KING,MILLER 10 MILLER 23.01.82 CLARK,KING,MILLER 20 SMITH 17.12.80 SMITH,JONES,FORD,SCOTT,ADAMS 20 JONES 02.04.81 SMITH,JONES,FORD,SCOTT,ADAMS 20 FORD 03.12.81 SMITH,JONES,FORD,SCOTT,ADAMS 20 SCOTT 19.04.87 SMITH,JONES,FORD,SCOTT,ADAMS 20 ADAMS 23.05.87 SMITH,JONES,FORD,SCOTT,ADAMS 30 ALLEN 20.02.81 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 WARD 22.02.81 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 BLAKE 01.05.81 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 TURNER 08.09.81 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 MARTIN 28.09.81 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 JAMES 03.12.81 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
Недостатки
К сожалению, функция умеет возвращать результат только в виде varchar2, что сразу же накладывает ограничение на длину получаемых строк в 4000 символов. Если такой длины недостаточно (что очень часто происходит в моей практике), то для получения результата в виде clob по-прежнему приходится использовать старые, проверенные временем алгоритмы.
Источник: listagg function in 11g release 2