Функция LISTAGG

Статьи про Oracle -> Программирование

Функция LISTAGG

v:1.0 09.08.2011
Петрелевич Сергей
Источник информации: Oracle® Database SQL Language Reference

Синтаксис

Синтаксис функции LISTAGG

Дополнительная информация:
"Analytic Functions" описание синтаксиса, семантики и ограничений конструкций ORDER BY и OVER

Назначение

Функция LISTAGG упорядочивает данные, объединенные в группы конструкцией ORDER BY, после чего соединяет значение колонки <measure>

Функция может использоваться по разному:

  • Как одиночная агрегатная (aggregate) функция LISTAGG обрабатывает все ряды и возвращает одно значение.
  • Как групповая агрегатная функция LISTAGG обрабатывает группы данных, определенных конструкциями GROUP BY и возвращает результирующее значение для каждой группы.
  • Как аналитическая функция LISTAGG обрабатывает данные, разделенные на блоки, задаваемые одним или несколькими выражениями query_partition_clause.

Аргументы функции определяются следующими правилами:

  • Аргумент measure_expr может быть любым выражением. Значения Null игнорируются.
  • Аргумент delimiter_expr задает строку, которая разделяет значения в результирующей строке. Аргумент не обязательный по умолчанию он NULL.
  • Аргумент order_by_clause определяет порядок, в котором будут возвращены склеенные результирующие значения. Функция будет рассматриваться как deterministic, только в том случае, если выражение ORDER BY определяет уникальную последовательность.

Функция возвращает тип RAW, если объединяемые колонки имеют тип RAW, иначе тип результата - VARCHAR2.

Примеры

В этом примере одиночная агрегатная функция возвращает список сотрудников департамента 30, упорядоченных по дате приема на работу и фамилии:

SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
    MIN(hire_date) "Earliest"
    FROM employees
    WHERE department_id = 30;

Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

В этом примере групповая агрегатная функция возвращает список сотрудников, сгруппированных по департаментам и упорядоченных по дате приема на работу: 

SELECT department_id "Dept.",
  LISTAGG(last_name, '; ')
   WITHIN GROUP (ORDER BY hire_date) "Employees"
   FROM employees
   GROUP BY department_id;

Dept. Employees
------ ------------------------------------------------------------
    10 Whalen
    20 Hartstein; Fay
    30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
    40 Mavris
    50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
       s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
       ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat
       el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;
        Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv
       an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle
    60 Austin; Pataballa; Lorentz; Ernst; Hunold
    70 Baer
...

А в этом примере демонстрируется аналитическая функция. Выводится список сотрудников, нанятых до 1 сентября 2003г., сотрудники группируются по департаментам, причем для каждого сотрудника выводится список других сотрудников, которые тоже были наняты в этот департамент до до 1 сентября 2003г.: 

SELECT department_id "Dept", hire_date "Date", last_name "Name",
  LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
  OVER (PARTITION BY department_id) as "Emp_list"
  FROM employees
  WHERE hire_date < '01-SEP-2003';

 Dept Date      Name            Emp_list
----- --------- --------------- ---------------------------------------------
   30 07-DEC-02 Raphaely        Raphaely; Khoo
   30 18-MAY-03 Khoo            Raphaely; Khoo
   40 07-JUN-02 Mavris          Mavris
   50 01-MAY-03 Kaufling        Kaufling; Ladwig
   50 14-JUL-03 Ladwig          Kaufling; Ladwig
   70 07-JUN-02 Baer            Baer
   90 13-JAN-01 De Haan         De Haan; King
   90 17-JUN-03 King            De Haan; King
  100 16-AUG-02 Faviet          Faviet; Greenberg
  100 17-AUG-02 Greenberg       Faviet; Greenberg
  110 07-JUN-02 Gietz           Gietz; Higgins
  110 07-JUN-02 Higgins         Gietz; Higgins

Интересные ссылки по этой теме

  1. Практический пример использования
  2. Еще способы агрегирования строк

Метки: SQL  

Комментарии.

Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: <a>, <b>, <i>, <br>.

Пожалуйста авторизуйтесь или зарегистрируйтесь.