Собираем строки через разделитель - 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

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

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

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

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

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

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 |

получается

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

В виде оконной функции 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

В целом, результат получился весьма ожидаемый. Но есть одно но... мы не указали сортировку при формировании 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

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

Правильно указывать сортировка внутри самой 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 :(