# Как вернуть top-N из каждой группы с GROUP_CONCAT() Разбираем на практическом примере, как в Manticore Search выбрать несколько последних элементов из каждой группы и собрать их в одну строку. Представим экран службы поддержки. Оператор ищет события, связанные с возвратами, и вместо длинного журнала хочет видеть короткую сводку: одна строка на пользователя, общее число совпадений и **пять** последних событий. Если понадобятся подробности, приложение сможет загрузить их по ID. Два очевидных варианта дают не совсем то, что нужно: - обычный `GROUP_CONCAT()` собирает все значения группы вместе, - а `GROUP N BY` возвращает несколько строк на пользователя. Начиная с [Manticore Search 28.6.6](/blog/manticore-search-28-6-6/) значения внутри `GROUP_CONCAT()` можно отсортировать и оставить только нужное количество: ```sql GROUP_CONCAT(id ORDER BY event_ts DESC, id DESC LIMIT 5) ``` Посмотрим, как это работает. Будем использовать SphinxQL и явный `GROUP BY` - без него новая форма `GROUP_CONCAT()` не работает. ## События, с которыми будем работать Создадим таблицу `activity`, где каждый документ - это отдельное событие. Текст события хранится в `body`, пользователь - в `user_id`, а время - в `event_ts`. ```sql CREATE TABLE activity ( body text, user_id int, event_ts bigint, event_type string ); INSERT INTO activity (id, body, user_id, event_ts, event_type) VALUES (1001, 'refund requested for order 501', 101, 1770000010, 'requested'), (1002, 'user logged in', 101, 1770000020, 'login'), (1003, 'refund approved for order 501', 101, 1770000030, 'approved'), (1004, 'refund email sent for order 501', 101, 1770000040, 'email'), (1005, 'refund status checked for order 501', 101, 1770000050, 'checked'), (1006, 'refund payout queued for order 501', 101, 1770000060, 'queued'), (1007, 'refund webhook retried for order 501', 101, 1770000060, 'retried'), (2001, 'refund requested for order 601', 202, 1770000015, 'requested'), (2002, 'refund approved for order 601', 202, 1770000025, 'approved'), (2003, 'shipping address changed', 202, 1770000035, 'shipping'), (2004, 'refund payout queued for order 601', 202, 1770000045, 'queued'), (2005, 'refund completed for order 601', 202, 1770000055, 'completed'), (3001, 'refund requested for order 701', 303, 1770000012, 'requested'), (3002, 'invoice downloaded', 303, 1770000022, 'invoice'), (3003, 'refund rejected for order 701', 303, 1770000032, 'rejected'); ``` Поиск по слову `refund` найдёт шесть событий пользователя 101, четыре - пользователя 202 и два - пользователя 303. Мы специально задали событиям 1006 и 1007 одинаковое время: дальше будет видно, зачем при сортировке нужен ещё и `id`. ## В чём проблема старых вариантов Начнём с обычного `GROUP_CONCAT()`. Формат ответа нам подходит - одна строка на пользователя: ```sql SELECT user_id, COUNT(*) AS matched_events, GROUP_CONCAT(id) AS all_event_ids FROM activity WHERE MATCH('refund') GROUP BY user_id ORDER BY matched_events DESC, user_id ASC; ``` Но для пользователя 101 в строку попадут все шесть ID, например `1001,1003,1004,1005,1006,1007`, а нам нужны только пять последних. Кроме того, без внутренней сортировки порядок значений не гарантирован. ```text +---------+----------------+-------------------------------+ | user_id | matched_events | all_event_ids | +---------+----------------+-------------------------------+ | 101 | 6 | 1001,1003,1004,1005,1006,1007 | | 202 | 4 | 2001,2002,2004,2005 | | 303 | 2 | 3001,3003 | +---------+----------------+-------------------------------+ ``` Можно пойти другим путём и попросить `GROUP N BY` выбрать пять последних документов из каждой группы: ```sql SELECT id, user_id, event_ts FROM activity WHERE MATCH('refund') GROUP 5 BY user_id WITHIN GROUP ORDER BY event_ts DESC, id DESC ORDER BY user_id ASC; ``` Самое старое событие пользователя 101 исчезнет, но каждый из оставшихся документов придёт отдельной строкой. В итоге вместо трёх строк мы получим одиннадцать: 5 + 4 + 2. Это удобно, когда клиенту нужны сами документы, но не подходит для нашей компактной сводки. ```text +------+---------+------------+ | id | user_id | event_ts | +------+---------+------------+ | 1007 | 101 | 1770000060 | | 1006 | 101 | 1770000060 | | 1005 | 101 | 1770000050 | | 1004 | 101 | 1770000040 | | 1003 | 101 | 1770000030 | | 2005 | 202 | 1770000055 | | 2004 | 202 | 1770000045 | | 2002 | 202 | 1770000025 | | 2001 | 202 | 1770000015 | | 3003 | 303 | 1770000032 | | 3001 | 303 | 1770000012 | +------+---------+------------+ ``` ## Собираем только последние пять ID вместе Теперь объединим оба действия: отсортируем документы прямо внутри `GROUP_CONCAT()` и там же ограничим список пятью значениями. ```sql SELECT user_id, COUNT(*) AS matched_events, GROUP_CONCAT( id ORDER BY event_ts DESC, id DESC LIMIT 5 ) AS recent_event_ids FROM activity WHERE MATCH('refund') GROUP BY user_id ORDER BY matched_events DESC, user_id ASC; ``` ```text +---------+----------------+--------------------------+ | user_id | matched_events | recent_event_ids | +---------+----------------+--------------------------+ | 101 | 6 | 1007,1006,1005,1004,1003 | | 202 | 4 | 2005,2004,2002,2001 | | 303 | 2 | 3003,3001 | +---------+----------------+--------------------------+ ``` Сначала `MATCH('refund')` отбирает события о возвратах, затем `GROUP BY user_id` группирует их по пользователям. `COUNT(*)` считает все найденные события, а `GROUP_CONCAT()` берёт из каждой группы только первые пять после сортировки. Здесь особенно важен второй ключ сортировки - `id DESC`. У событий 1006 и 1007 одинаковый `event_ts`, поэтому без него их взаимный порядок был бы неопределённым. Благодаря сортировке по ID событие 1007 всегда идёт первым. Обратите внимание, что в запросе два `ORDER BY`. Тот, что находится внутри `GROUP_CONCAT()`, задаёт порядок ID в строке. Последний `ORDER BY` сортирует уже готовые строки: сначала по числу совпадений, затем по `user_id`. Внутренний `LIMIT` не меняет `COUNT(*)` и не влияет на пагинацию всего результата. Поэтому у пользователя 101 по-прежнему шесть совпадений, хотя рядом показаны только пять ID. Для нашей сводки это именно то, что нужно. Есть ещё одна деталь: `GROUP_CONCAT()` всегда возвращает строку, даже если внутри находятся числовые ID. Если API должен вернуть массив чисел или объекты с несколькими полями, строку придётся разбирать на стороне клиента или использовать другой формат ответа. С распределёнными таблицами запрос работает так же. Manticore собирает кандидатов со всех локальных и удалённых таблиц, а затем выбирает общий top-N для каждой группы. ## Если запятая не подходит По умолчанию значения разделяются запятыми. Иногда удобнее получить более наглядную строку - например, вывести рядом тип события и его ID. Для этого можно задать свой разделитель через `SEPARATOR`: ```sql SELECT user_id, GROUP_CONCAT( CONCAT(event_type, ':', TO_STRING(id)) ORDER BY event_ts DESC, id DESC SEPARATOR ' / ' LIMIT 3 ) AS recent_events FROM activity WHERE MATCH('refund') GROUP BY user_id ORDER BY user_id ASC; ``` ```text +---------+----------------------------------------------+ | user_id | recent_events | +---------+----------------------------------------------+ | 101 | retried:1007 / queued:1006 / checked:1005 | | 202 | completed:2005 / queued:2004 / approved:2002 | | 303 | rejected:3003 / requested:3001 | +---------+----------------------------------------------+ ``` В этом синтаксисе `SEPARATOR` ставится перед `LIMIT`. Manticore ничего не экранирует и не добавляет кавычки: результатом будет обычная строка, а не JSON-массив. Для `event_type` такой вариант подходит, потому что эти значения задаёт само приложение. С произвольным текстом лучше быть осторожнее: если разделитель встретится в самих данных, надёжно разобрать результат уже не получится. В таком случае лучше вернуть отдельные строки или использовать структурированный формат. ## Где новый способ не подойдёт У этой формы `GROUP_CONCAT()` есть несколько ограничений. Она работает только в SQL-запросах с явным `GROUP BY` и не поддерживает: * `DISTINCT`, `OFFSET` и объединение сразу нескольких выражений; * `JOIN`, `FACET`, внешние `SELECT` и табличные функции; * KNN- и hybrid-запросы, а также scroll; * неявную группировку и аналогичный синтаксис агрегации в JSON API. Её алиас нельзя использовать в `HAVING` или финальном `ORDER BY`. При этом сами группы можно сортировать по ключу группировки и обычным агрегатам - например, по `user_id` и `COUNT(*)`, как в запросе выше. Стоит учитывать и расход памяти. Для каждого такого выражения Manticore хранит отдельный top-N для каждой группы, оставшейся в результате. Чем больше групп, выше `N` и больше самих выражений, тем больше памяти понадобится. Размер значений и ключей сортировки тоже влияет на расход памяти, поэтому завышать лимит «на всякий случай» не стоит. Полная документация к новой функциональности [находится здесь](https://manual.manticoresearch.com/Searching/Grouping#GROUP_CONCAT%28field%29). ## Другие примеры События поддержки - только один из возможных сценариев. Новый режим может быть полезен везде, где в документе уже есть ключ группы, нужное значение и поле для сортировки: * **Изображения товара:** собрать несколько первых ID или путей для каждого `product_id`, отсортировав их по `display_order`. * **Приоритетные задачи:** вернуть до шести ID задач для каждого `assignee_id`, отсортировав их по заранее рассчитанному `priority`. * **Серверные ошибки:** показать последние N ошибок каждого сервера, сохранив отдельно их общее количество. Если нужен короткий список ID, названий или путей, `GROUP_CONCAT(... ORDER BY ... LIMIT N)` теперь позволяет получить его одним запросом. Надеемся, что это будет вам полезно.