Оконные функции

Собираем строки через разделитель - STRING_AGG

Часто стоит задача по набору записей в таблице собрать строчку, перечислив значения через разделитель. Ярким примером может служить список адресов электронной почты для рассылки: 'ivanov_ii@gmail.com; petrov_pp@mail.ru; sidorov_ss@yandex.ru'.

Решить подобную задачу с помощью SQL легко. Достаточно воспользоваться функцией STRING_AGG. Она доступна и как агрегатная функция, и как оконная.

Синтаксис:

string_agg (выражение, разделитель [ORDER BY порядок сортировки значений])

Агрегатная функция

Сформируем список товаров в каждом каталоге:

SELECT c.name AS category_name,
       string_agg(p.name, ', ' ORDER BY p.name) AS products
  FROM category c,
       product p
 WHERE p.category_id = c.category_id
 GROUP BY c.category_id,
          c.name
#category_nameproducts
1Бытовая техникаПылесос S6, Холодильник A2
2ФотоаппаратыLord Nikon 95, Nikon D750
3Игровые консолиNintendo, PlayStation, Xbox
4АудиотехникаНаушники S3
5Сотовые телефоныМоноблок C4, Слайдер B3
6НоутбукиУльтрабук X5
7РюкзакиDeepbox

Как получается такой результат?

Давай разбираться по порядку. Во-первых, посмотрим что получается после соединения таблиц

SELECT c.category_id,
       c.name AS category_name,
       p.name AS product_name
  FROM category c,
       product p
 WHERE p.category_id = c.category_id
 ORDER BY c.category_id,
          c.name,
          p.name
#category_idcategory_nameproduct_name
13Бытовая техникаПылесос S6
23Бытовая техникаХолодильник A2
35ФотоаппаратыLord Nikon 95
45ФотоаппаратыNikon D750
56Игровые консолиNintendo
66Игровые консолиPlayStation
76Игровые консолиXbox
87АудиотехникаНаушники S3
98Сотовые телефоныМоноблок C4
108Сотовые телефоныСлайдер B3
119НоутбукиУльтрабук X5
1210РюкзакиDeepbox

В запросе после WHERE написан GROUP BY. Значит в результате запроса будет столько строк, сколько встретилось уникальных значений перечисленных столбцов:

#category_idcategory_name
13Бытовая техника
25Фотоаппараты
36Игровые консоли
47Аудиотехника
58Сотовые телефоны
69Ноутбуки
710Рюкзаки

Осталось разобраться с

SELECT c.name AS category_name,
       string_agg(p.name, ', ' ORDER BY p.name) AS products

С "c.name AS category_name" разбираться нечего. Это поле у нас есть в GROUP BY.

Смотрим на "string_agg(p.name, ', ' ORDER BY p.name) AS products". Так как over отсутсвует, значит это не оконная функция. Значит значение будет вычисляться в процессе группировки строк.

Внутри string_agg написано: "p.name, ', ' ORDER BY p.name". В переводе на русский это звучит так: для каждой группы из GROUP BY возьми все строки, отсортируй их по p.name и соедини названия продуктов p.name через запятую с пробелом ', '.

Так из

| # | category_id | category_name | product_name |
| -: | ---------: | :------------ | :---------- |
| 1 | 3 | Бытовая техника | Пылесос S6 |
| 2 | 3 | Бытовая техника | Холодильник A2 |

получается

#category_nameproducts
1Бытовая техникаПылесос S6, Холодильник A2

Оконная агрегатная функция

В виде оконной функции STRING_AGG используют довольно редко (кому она такая вообще нужна?). Но мы попробуем)

SELECT c.category_id,
       c.name AS category_name,
       p.name AS product_name,
       string_agg(p.name, ', ') over (PARTITION BY c.category_id) AS product_list
  FROM category c,
       product p
 WHERE p.category_id = c.category_id
 ORDER BY c.category_id,
          c.name,
          p.name
#category_idcategory_nameproduct_nameproduct_list
13Бытовая техникаПылесос S6Пылесос S6, Холодильник A2
23Бытовая техникаХолодильник A2Пылесос S6, Холодильник A2
35ФотоаппаратыLord Nikon 95Nikon D750, Lord Nikon 95
45ФотоаппаратыNikon D750Nikon D750, Lord Nikon 95
56Игровые консолиNintendoXbox, Nintendo, PlayStation
66Игровые консолиPlayStationXbox, Nintendo, PlayStation
76Игровые консолиXboxXbox, Nintendo, PlayStation
87АудиотехникаНаушники S3Наушники S3
98Сотовые телефоныМоноблок C4Слайдер B3, Моноблок C4
108Сотовые телефоныСлайдер B3Слайдер B3, Моноблок C4
119НоутбукиУльтрабук X5Ультрабук X5
1210РюкзакиDeepboxDeepbox

В целом, результат получился весьма ожидаемый. Но есть одно но... мы не указали сортировку при формировании product_list.

Сделаем глупость, добавим ее в over:

SELECT c.category_id,
       c.name AS category_name,
       p.name AS product_name,
       string_agg(p.name, ', ') over (PARTITION BY c.category_id ORDER BY p.name) AS product_list
  FROM category c,
       product p
 WHERE p.category_id = c.category_id
 ORDER BY c.category_id,
          c.name,
          p.name
#category_idcategory_nameproduct_nameproduct_list
13Бытовая техникаПылесос S6Пылесос S6
23Бытовая техникаХолодильник A2Пылесос S6, Холодильник A2
35ФотоаппаратыLord Nikon 95Lord Nikon 95
45ФотоаппаратыNikon D750Lord Nikon 95, Nikon D750
56Игровые консолиNintendoNintendo
66Игровые консолиPlayStationNintendo, PlayStation
76Игровые консолиXboxNintendo, PlayStation, Xbox
87АудиотехникаНаушники S3Наушники S3
98Сотовые телефоныМоноблок C4Моноблок C4
108Сотовые телефоныСлайдер B3Моноблок C4, Слайдер B3
119НоутбукиУльтрабук X5Ультрабук X5
1210РюкзакиDeepboxDeepbox

Как и ожидалось, результат получился не таким, какой хотели получить. В предыдущих заданиях рассказано, почему так.

Правильно указывать сортировка внутри самой string_agg:

SELECT c.category_id,
       c.name AS category_name,
       p.name AS product_name,
       string_agg(p.name, ', ' ORDER BY p.name) over (PARTITION BY c.category_id) AS product_list
  FROM category c,
       product p
 WHERE p.category_id = c.category_id
 ORDER BY c.category_id,
          c.name,
          p.name
error: aggregate ORDER BY is not implemented for window functions

Ну вот... Фича не реализована в PostgreSQL :(

ПредыдущаяСледующая