Массивы в PostgreSQL: ARRAY_AGG, UNNEST, ANY и поиск по тегам

Коротко

• array_agg(x order by …) собирает значения группы в массив в заданном порядке.
• unnest(массив) разворачивает массив обратно в строки, with ordinality добавляет номер элемента.
• Нумерация элементов начинается с единицы: arr[1] — первый элемент, arr[1:3] — первые три.
• Проверки: x = any(arr) — есть ли значение, arr @> array[…] — есть ли все, arr && array[…] — есть ли хотя бы одно.
• array_to_string и string_to_array переводят массив в строку с разделителем и обратно.

Что такое массив и как его записать

Тип массива пишется как тип элемента со скобками: text[], integer[]. Все элементы одного массива — одного типа. Записать массив в запросе можно конструктором array['sql', 'join'] или строкой '{sql,join}'::text[] — так PostgreSQL и показывает массивы в результате.

Главное отличие от большинства языков программирования: индексы начинаются с единицы, а не с нуля. Срез arr[2:3] включает обе границы — второй и третий элементы. Обращение к несуществующему индексу не вызывает ошибку, а возвращает NULL.

ARRAY_AGG: собрать значения группы

array_agg — агрегатная функция, как sum или count, только результат не число, а список. Порядок элементов задаётся внутри скобок: array_agg(plan order by paid_at). Без order by порядок не гарантирован, и два запуска одного запроса могут дать разные списки — для пути пользователя это недопустимо. Если у двух строк одинаковое время, добавьте второй ключ сортировки, например id платежа.

Запрос ниже собирает для каждого плательщика с двумя и более платежами последовательность тарифов и считает, какие последовательности встречаются чаще. Массивы можно группировать как обычные значения: два одинаковых списка попадают в одну группу.

with paths as (
  select
    user_id,
    array_agg(plan order by paid_at, payment_id) as plans
  from payments
  group by user_id
  having count(*) >= 2
)
select
  array_to_string(plans, ' → ') as plans_path,
  count(*)                      as users
from paths
group by plans
order by users desc, plans_path
limit 8;

Находка: в учебном продукте никто не меняет тариф

Все пути в результате выше однородные: basic продлевает basic, pro продлевает pro. Это легко проверить на всех плательщиках сразу: сравнить первый элемент массива с последним. Если тариф хоть раз менялся, они разойдутся.

Из 912 плательщиков 296 хоть раз платили за pro, и ни один не перешёл между тарифами. Для продукта это вопрос, который стоит задать: апгрейда с basic на pro нет вовсе. Возможно, в интерфейсе нет понятного пути к нему, а может быть, данные о смене тарифа пишутся в другую таблицу. Массив здесь не ответ, а быстрый способ заметить, что вопрос есть.

with paths as (
  select
    user_id,
    array_agg(plan order by paid_at, payment_id) as plans
  from payments
  group by user_id
)
select
  count(*)                                                          as payers,
  count(*) filter (where 'pro' = any(plans))                        as ever_pro,
  count(*) filter (where plans[1] <> plans[array_length(plans, 1)]) as changed_plan
from paths;

Первые N элементов: срез массива

Срез [1:3] оставляет первые три элемента — например, первые три действия пользователя в продукте. Это короткий способ увидеть, как начинается знакомство с продуктом, без оконных функций и самосоединений.

Самое частое начало в учебной базе — открыть приложение, создать рабочее пространство и сразу отчёт: так начинали 1 775 из 4 613 пользователей. Второе по частоте — три открытия приложения подряд без действий, 1 189 человек. Эту группу стоит изучить отдельно: люди возвращаются, но ничего не делают.

with firsts as (
  select
    user_id,
    (array_agg(event_name order by event_time, event_id))[1:3] as first_events
  from events
  group by user_id
)
select
  array_to_string(first_events, ' → ') as first_three,
  count(*)                             as users
from firsts
group by first_events
order by users desc, first_three
limit 5;

Топ-N в каждой группе одной строкой

Классическая задача «три первых в каждой группе» обычно решается оконной функцией row_number. Если ответ нужен компактно — одной строкой на группу, — хватает array_agg со срезом. Запрос ниже показывает в каждом канале трёх пользователей с самой дорогой подпиской; при одинаковой цене побеждает меньший id.

Разница с оконной функцией в форме ответа: окно возвращает три строки на группу, с которыми удобно соединять дальше, массив — одну строку для отчёта или письма.

select
  u.channel,
  array_to_string(
    (array_agg(u.user_id order by s.monthly_price desc, u.user_id))[1:3],
    ', '
  ) as top3_users
from users u
join subscriptions s on s.user_id = u.user_id
group by u.channel
order by u.channel;

Проверки: ANY, @> и &&

Когда у каждого пользователя есть набор событий, удобно собрать его в массив без повторов (array_agg(distinct …)) и проверять условия на наборе. Три оператора закрывают почти все вопросы: есть ли конкретное событие, есть ли все из списка, есть ли хотя бы одно из списка.

В учебной базе из 4 613 пользователей отчёт создали 1 775, отчёт и выгрузку — 657, а пригласили коллегу или выгрузили отчёт — 1 877. Такой запрос — быстрый способ посчитать пересечения действий без цепочки соединений.

with sets as (
  select
    user_id,
    array_agg(distinct event_name) as names
  from events
  group by user_id
)
select
  count(*)                                                                    as users,
  count(*) filter (where 'report_created' = any(names))                       as created_report,
  count(*) filter (where names @> array['report_created', 'export_completed']) as created_and_exported,
  count(*) filter (where names && array['invite_sent', 'export_completed'])    as invited_or_exported
from sets;

UNNEST: развернуть массив в строки

unnest делает обратное: превращает каждый элемент массива в отдельную строку. Типичный случай — таблица, где теги или категории хранятся массивом, а нужно посчитать, сколько раз встречается каждый тег. Пишется через cross join unnest(столбец): каждая строка таблицы соединяется со своими элементами.

Учтите: строка с пустым массивом или NULL при cross join пропадает целиком. Если такие строки тоже нужны в результате, используйте left join unnest(tags) as u(tag) on true.

select tag, count(*) as articles
from (
  values
    (1, array['sql', 'join']),
    (2, array['sql', 'window', 'lag']),
    (3, array['excel'])
) as t(article_id, tags)
cross join unnest(tags) as u(tag)
group by tag
order by articles desc, tag;

Номер элемента: WITH ORDINALITY

Иногда важна не только сама величина, но и её место в списке: каким по счёту был платёж. unnest(…) with ordinality добавляет к каждому элементу его номер. Запрос ниже разворачивает пути тарифов обратно и считает, сколько пользователей на каждом шаге платили за каждый тариф.

with paths as (
  select
    user_id,
    array_agg(plan order by paid_at, payment_id) as plans
  from payments
  group by user_id
  having count(*) >= 2
)
select
  step.n     as payment_number,
  step.plan,
  count(*)   as users
from paths
cross join unnest(plans) with ordinality as step(plan, n)
group by step.n, step.plan
order by step.n, users desc;

Строка с разделителями: string_to_array

Выгрузки из CRM и форм часто хранят список в одной текстовой ячейке: «sql,join,lag». string_to_array(s, ',') превращает такую строку в массив, а дальше работают все приёмы выше: unnest для подсчёта, any для фильтра. Обратную операцию делает array_to_string.

Перед разбором проверьте пробелы: строка «sql, join» с пробелом после запятой даст элемент « join» с ведущим пробелом, и он не совпадёт с «join». Уберите пробелы через trim после unnest или разделяйте по «, ».

select
  array_to_string(string_to_array('sql,join,lag', ','), ' / ') as tags;

Ловушки с NULL и пустыми массивами

Хранить ли массивы в таблице

Массив в столбце удобен для коротких списков, которые читают целиком и редко меняют поэлементно: теги, роли, список выбранных опций. Поиск по такому столбцу через @> и && ускоряет индекс GIN: create index on articles using gin (tags).

Если элементы массива нужно соединять с другими таблицами, менять по одному или проверять ссылочную целостность, лучше отдельная таблица «один элемент — одна строка». Внешний ключ на элемент массива PostgreSQL не поддерживает, и вычистить удалённый тег из тысяч массивов дороже, чем удалить строки.

Массивы в других СУБД

Идея есть почти везде, отличаются названия. В DuckDB массивы называются списками, но основные функции из статьи — array_agg, unnest, срезы, any, @>, &&, array_to_string — работают так же, поэтому запросы выполняются в песочнице без изменений.

Частые вопросы

Группировки, срезы и проверки из статьи можно выполнить в песочнице на учебной базе, а сложную агрегацию — отработать в курсе «Продвинутый SQL».