STRING_AGG в SQL: собрать строки в одну и увидеть путь пользователя

Коротко

string_agg(выражение, разделитель ORDER BY …) — это GROUP BY, который вместо числа возвращает текст. Всё, что верно для агрегатов, верно и для него: одна строка на группу, NULL пропускаются, фильтр по результату — в HAVING.

• PostgreSQL и SQL Server: STRING_AGG. MySQL: GROUP_CONCAT. Oracle и Snowflake: LISTAGG. DuckDB понимает все три названия.
• Порядок внутри строки задаёт только ORDER BY внутри агрегата. Без него PostgreSQL и DuckDB на одних данных собрали четыре канала в разном порядке.
• Путь пользователя — string_agg(event_name, ' → ' ORDER BY event_time, event_id). Самые частые пути — GROUP BY по этой строке.
• В учебной базе 30,5% пользователей за всё время не сделали ничего, кроме открытия продукта. В paid_search таких 48,4%, в referral — 18,0%.
• Обратная операция — unnest(string_to_array(…)): строка снова становится строками таблицы.

Как собрать несколько строк в одну: STRING_AGG

Задача: в отчёте по каналам нужна колонка со странами пользователей и их числом, одной строкой: «RU 1015, KZ 350…». Сначала запрос считает пользователей в разрезе «канал × страна», потом string_agg склеивает строки каждого канала в одну. Разделитель — второй аргумент, порядок — ORDER BY внутри скобок.

Две детали видны уже здесь. Страна у 55 пользователей не указана, и без coalesce её строка превратилась бы в NULL: конкатенация NULL || ' ' || 22 даёт NULL, а NULL string_agg пропускает. Такие пользователи пропали бы из сводки без следа. И users — число, но к тексту оно приклеивается через || без явного приведения в обеих базах.

WITH by_country AS (
  SELECT channel, coalesce(country, '—') AS country, count(*) AS users
  FROM users
  GROUP BY channel, country
)
SELECT
  channel,
  string_agg(country || ' ' || users, ', ' ORDER BY users DESC) AS countries
FROM by_country
GROUP BY channel
ORDER BY channel;

STRING_AGG, GROUP_CONCAT, LISTAGG: как это пишется в разных базах

Функция есть почти везде, но называется и пишется по-разному. Главное различие — где стоит порядок. В PostgreSQL, DuckDB и MySQL ORDER BY пишется внутри скобок. В SQL Server, Oracle и Snowflake — после них, в WITHIN GROUP (ORDER BY …). В ClickHouse отдельной функции нет: сначала собирают массив, потом склеивают его в строку.

Есть и различия в типах. PostgreSQL принимает в string_agg только текст: string_agg(user_id, ',') падает с ошибкой function string_agg(integer, unknown) does not exist, нужно user_id::text. DuckDB приведёт число к тексту сам. Разделитель в PostgreSQL обязателен, в DuckDB по умолчанию — запятая.

Почему без ORDER BY порядок внутри строки случаен

Агрегат получает строки группы в том порядке, в каком их отдал план выполнения. Этот порядок зависит от базы, версии, параллельности и того, как данные легли на диск. Внешний ORDER BY запроса сортирует готовые строки результата и на порядок внутри агрегата не влияет.

Проверка на учебной базе. Запрос склеивает четыре уникальных канала без ORDER BY в агрегате. DuckDB вернул «partner, referral, organic, paid_search», PostgreSQL на тех же данных — «organic, paid_search, partner, referral». Ни один ответ не ошибочен, просто порядок не был задан. Если по такой строке потом группировать пути или сравнивать отчёты, одинаковые наборы превратятся в разные строки.

Для пути пользователя порядок и есть смысл. Сортируйте по времени и добавляйте уникальный ключ последним, как у ROW_NUMBER: если у двух событий одинаковое время, event_id решит, какое идёт первым. В учебной базе совпадений времени у одного пользователя нет, но в рабочих логах с точностью до секунды они встречаются постоянно.

SELECT string_agg(channel, ', ') AS channels
FROM (SELECT DISTINCT channel FROM users) AS c;

-- DuckDB:     partner, referral, organic, paid_search
-- PostgreSQL: organic, paid_search, partner, referral
-- с string_agg(channel, ', ' ORDER BY channel) обе базы вернут второй вариант

Путь пользователя одной строкой

Путь пользователя — все его события по времени. В таблице events это несколько строк на человека, и глазами их читать неудобно. string_agg сворачивает их в одну строку, которую можно показать в отчёте, отфильтровать или сгруппировать.

Пользователь 537 пришёл из referral 17 июня. За 35 минут он создал workspace, ещё через полтора часа — отчёт, на следующий день вернулся, через день выгрузил отчёт и дальше заходил ещё дважды. Семь строк таблицы превращаются в одну.

SELECT
  user_id,
  count(*) AS events,
  string_agg(event_name, ' → ' ORDER BY event_time, event_id) AS path
FROM events
WHERE user_id = 537
GROUP BY user_id;

-- 537 | 7 | app_open → workspace_created → report_created → app_open
--           → export_completed → app_open → app_open

Первые три действия: какие пути самые частые

Чтобы сравнивать пути, их нужно сделать одинаковой длины. Первые три действия получают номер через row_number(), а string_agg собирает только строки с номером до трёх. Дальше обычный GROUP BY по строке пути и доля от всех.

Самый частый путь — «открыл, создал workspace, создал отчёт», у 38,5% пользователей. На втором месте — три открытия подряд, 25,8%. Это люди, которые возвращаются, но за первые три визита ничего не сделали. На третьем — открыл, создал workspace и вернулся без отчёта.

Видна и проблема метода. Повторные app_open занимают место в пути: три открытия подряд и «открыл, открыл, пригласил коллегу» — разные строки, хотя разница может быть в одном лишнем визите. Для продуктового вопроса «какие действия человек успел сделать» сырые события слишком шумные.

WITH numbered AS (
  SELECT user_id, event_name,
         row_number() OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS step_no
  FROM events
),
paths AS (
  SELECT user_id, string_agg(event_name, ' → ' ORDER BY step_no) AS path
  FROM numbered
  WHERE step_no <= 3
  GROUP BY user_id
)
SELECT path,
       count(*) AS users,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS share_pct
FROM paths
GROUP BY path
ORDER BY users DESC
LIMIT 5;

Путь без повторов: каждое действие один раз

Если важно, какие действия человек сделал и в каком порядке, повторы убирают до склейки. Проще всего взять для каждого пользователя и каждого типа события первый момент, min(event_time), и склеить типы событий в порядке этих моментов. Получится путь, где каждое действие встречается один раз.

Из 4 613 пользователей осталось десять различных путей. Самый частый — просто «app_open»: 1 407 человек, 30,5%. Они открывали продукт, некоторые много раз, но не создали workspace, не пригласили коллегу и ничего не выгрузили. Только 73 из них открыли продукт один раз и не вернулись. Остальные возвращались и уходили ни с чем.

Второй способ — убрать только подряд идущие повторы через lag(event_name) и оставить строки, где событие отличается от предыдущего. Он сохраняет возвраты между действиями, но даёт 39 разных путей вместо 10. Для сравнения: полных путей из сырых событий в базе 295. Какой способ выбрать, зависит от вопроса: «что успел сделать» или «как двигался».

WITH firsts AS (
  SELECT user_id, event_name, min(event_time) AS first_time
  FROM events
  GROUP BY user_id, event_name
),
paths AS (
  SELECT user_id,
         string_agg(event_name, ' → ' ORDER BY first_time, event_name) AS path
  FROM firsts
  GROUP BY user_id
)
SELECT path,
       count(*) AS users,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS share_pct
FROM paths
GROUP BY path
ORDER BY users DESC;

Самые частые пути по каналам

Путь в виде строки — обычная колонка, и по ней работает любой разрез. Присоедините users, сгруппируйте по каналу и пути, пронумеруйте пути внутри канала — и получится топ путей в каждом канале. Это тот же приём «топ-N в группе», только ранжируются строки пути.

Каналы различаются сильно. В referral самый частый путь — полный: «открыл, создал workspace, создал отчёт», 23,4%. В остальных трёх на первом месте «только app_open». В paid_search так заканчивается почти половина пути, 48,4%, а вторым идёт «открыл и пригласил коллегу» без workspace — 14,6%.

Здесь важно не перепутать вывод. «Только app_open» — это не отток: 1 334 из 1 407 таких пользователей возвращались. Это люди, которые не нашли первого действия. Для paid_search это вопрос к обещанию рекламы и первому экрану, а не к удержанию.

WITH firsts AS (
  SELECT user_id, event_name, min(event_time) AS first_time
  FROM events
  GROUP BY user_id, event_name
),
paths AS (
  SELECT user_id,
         string_agg(event_name, ' → ' ORDER BY first_time, event_name) AS path
  FROM firsts
  GROUP BY user_id
)
SELECT
  u.channel,
  count(*) AS users,
  count(*) FILTER (WHERE p.path = 'app_open') AS only_app_open,
  round(100.0 * count(*) FILTER (WHERE p.path = 'app_open') / count(*), 1) AS share_pct
FROM paths p
JOIN users u ON u.user_id = p.user_id
GROUP BY u.channel
ORDER BY share_pct DESC;

Обратная операция: строку разбить на строки

Иногда список уже склеен: путь сохранён в витрине, теги лежат в одной колонке через запятую, выгрузка пришла с перечислением в ячейке. Чтобы снова считать по элементам, строку разбивают на массив и разворачивают массив в строки. В PostgreSQL и DuckDB это unnest(string_to_array(строка, разделитель)), а WITH ORDINALITY добавляет номер элемента.

Такое разбиение — хорошая проверка самой склейки. Если развернуть пути без повторов обратно, число пользователей с каждым действием должно совпасть с числом уникальных пользователей этого события в events. Совпадает: workspace_created — 2 750, report_created — 1 775, invite_sent — 456 + 242 + 437 = 1 135, export_completed — 251 + 571 + 166 = 988.

Заодно видно, на каком шаге пути появляется каждое действие. invite_sent у 456 человек идёт сразу за открытием, то есть раньше создания workspace. Для продукта это вопрос: что именно эти люди отправляют коллегам, если рабочего пространства у них ещё нет.

WITH firsts AS (
  SELECT user_id, event_name, min(event_time) AS first_time
  FROM events
  GROUP BY user_id, event_name
),
paths AS (
  SELECT user_id,
         string_agg(event_name, ' → ' ORDER BY first_time, event_name) AS path
  FROM firsts
  GROUP BY user_id
)
SELECT s.step_no, s.step, count(*) AS users
FROM paths
CROSS JOIN unnest(string_to_array(path, ' → ')) WITH ORDINALITY AS s(step, step_no)
GROUP BY s.step_no, s.step
ORDER BY s.step_no, users DESC;

DISTINCT, NULL и длина строки: ограничения

string_agg(DISTINCT x, …) убирает повторы, но с порядком есть правило: при DISTINCT сортировать можно только по тому же выражению, что склеивается. string_agg(DISTINCT event_name, ', ' ORDER BY event_time) в PostgreSQL падает с ошибкой «in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list», в DuckDB — с похожей. Поэтому путь без повторов собирается через min(event_time) в отдельном шаге, а не через DISTINCT.

NULL агрегат пропускает молча. Если в группе все значения NULL или группа пустая, результат — NULL, а не пустая строка. Если NULL внутри выражения, как в country || ' ' || users, пропадает вся склейка: || с NULL даёт NULL. Для таких случаев есть coalesce или concat_ws, которые NULL пропускают.

Длина в PostgreSQL и DuckDB ограничена только памятью. Самый длинный путь в учебной базе — 22 события и 262 символа. В MySQL GROUP_CONCAT по умолчанию обрезает результат до 1 024 байт и выдаёт только предупреждение: путь активного пользователя за полгода оборвётся на середине, и заметить это можно только по длине строки.

И последнее: строка — формат для людей. Если со списком дальше работает код — считает элементы, ищет вхождение, сравнивает, — удобнее массив: array_agg в PostgreSQL и list в DuckDB. Поиск шага в строке через LIKE '%invite%' зависит от названий событий и разделителей, поиск в массиве — нет.

Частые ошибки со STRING_AGG

Большинство ошибок со склейкой строк не ломают запрос, а незаметно портят группировку по результату. Одинаковые по смыслу пути становятся разными строками, и топ путей начинает врать.

• Нет ORDER BY внутри агрегата: порядок зависит от плана, одинаковые наборы дают разные строки.
• Сортировка только по времени: при равном времени порядок снова случаен, нужен уникальный ключ последним.
• DISTINCT вместе с сортировкой по другой колонке: ошибка в PostgreSQL и DuckDB.
• NULL внутри ||: вся строка группы становится NULL и выпадает из результата.
• Число без приведения в PostgreSQL: string_agg(user_id, ',') не существует, нужно user_id::text.
• Сырые события вместо действий: повторные открытия превращают один сценарий в десятки путей.
• Путь как вывод об оттоке: «только app_open» здесь в 95% случаев — вернувшиеся люди, а не ушедшие.
• GROUP_CONCAT в MySQL без увеличения group_concat_max_len: длинные пути обрезаются.

Итог

STRING_AGG — агрегат, который возвращает текст вместо числа. Он полезен в двух местах: в отчёте, где список должен поместиться в одну ячейку, и в анализе, где строка пути становится ключом для группировки. В обоих случаях решает порядок: задавайте его внутри агрегата и добавляйте уникальный ключ. Для путей сначала решите, что считать шагом, и уберите повторы до склейки.

Агрегация и оконные функции, на которых держатся эти запросы, разобраны в SQL-курсе; задания там проверяются на той же учебной базе. Песочница курса открыта без регистрации, и все запросы статьи можно запустить в ней.