27.02.2016 1 min to read

Новое в Oracle 11gR2: снова о склейке нескольких записей в одну строку (listagg)

Category : Статьи

На протяжении всего времени работы с 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

Tags: ,