Массивы и string_agg: список вместо строк

Мира · маркетолог магазина настолок «Кубик»

В выгрузке каждая игра заказа отдельной строкой, читать такое невозможно. Хочу заказ одной строкой, как чек. И ещё: кто купил дополнение, а саму игру у нас не брал? Предложим им базу со скидкой.

Обе просьбы Миры про одно и то же: собрать значения группы в список. До сих пор агрегаты схлопывали группу в одно число: count, sum, avg. Сегодня группа схлопнется в строку текста или в массив, с которым можно работать дальше.

Чек вместо выгрузки

Заказ 6341 в базе лежит пятью строками order_items, по строке на игру. Чек склеивает их в одну строку, и делает это string_agg:

SELECT o.id,
       string_agg(p.title || ' ×' || i.qty, ', ' ORDER BY p.title) AS items
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
WHERE o.id = 6341
GROUP BY o.id;

  id  | items
------+------------------------------------------------------------------
 6341 | Ведьмы Империи ×1, Крепость Подземелья ×1, Острова Империи ×1,
      | Острова Империи: Пятый игрок ×1, Острова Океана ×1

Первый аргумент это что склеивать, второй это разделитель. ORDER BY стоит внутри скобок агрегата и сортирует только склеиваемые значения. Без него порядок такой, в каком база прочитала строки, и в двух соседних заказах одна и та же игра окажется то первой, то последней.

Массив: список, который можно спросить

Строку легко читать человеку, а запросу с ней тяжело: чтобы узнать, есть ли в чеке «Крепость», пришлось бы искать подстроку. Для работы нужен массив: одно значение, внутри которого лежит список. Собирает его array_agg:

SELECT o.customer_id,
       array_agg(p.genre ORDER BY o.created_at) AS bought,
       array_agg(DISTINCT p.genre ORDER BY p.genre) AS genres
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id;

 customer_id | bought                                       | genres
-------------+----------------------------------------------+------------------------------
          26 | {strategy,puzzle,party,kids}                 | {kids,party,puzzle,strategy}
          37 | {wargame,puzzle,wargame,strategy,wargame,…}  | {puzzle,strategy,wargame}

DISTINCT внутри агрегата убирает повторы, как в count(DISTINCT …). С ним есть ограничение: сортировать можно только по тому же, что собираешь. array_agg(DISTINCT p.genre ORDER BY o.created_at) упадёт с ошибкой in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list. Причина: если жанр встречался в трёх заказах, базе непонятно, по какой из трёх дат его ставить.

С готовым массивом можно делать то, чего со строкой не сделать:

• genres[1] это первый элемент. Нумерация с единицы, а не с нуля;
• genres[1:3] это срез, первые три элемента. Частый приём: (array_agg(p.title ORDER BY o.created_at))[1:3] даёт первые три игры клиента. Скобки вокруг агрегата обязательны;
• cardinality(genres) это число элементов. У клиентов «Кубика» в среднем 2,55 разных жанра, у самого разностороннего все 8.