Как вернуть top-N из каждой группы с GROUP_CONCAT()

В сфере искусственного интеллекта произошло заметное событие. ManticoreSearch 22 минуты назад Как вернуть top-N из каждой группы с GROUP_CONCAT() 6 мин 653 Поисковые технологии * Sphinx * SQL * Базы данных * Перевод Автор оригинала: Stanislav Klinov Как вернуть top-N из каждой группы с GROUP_CONCAT()Как вернуть top-N из каждой группы с GROUP_CONCAT()Разбираем на практическом примере, как в Manticore Search выбрать несколько последних элементов из каждой группы и собрать их в одну строку. Представим экран службы поддержки. Оператор ищет события, связанные с возвратами, и вместо длинного журнала хочет видеть короткую сводку: одна строка на пользователя, общее число совпадений и пять последних событий.

Если понадобятся подробности, приложение сможет загрузить их по ID. Два очевидных варианта дают не совсем то, что нужно:обычный GROUP_CONCAT() собирает все значения группы вместе,а GROUP N BY возвращает несколько строк на пользователя. Начиная с Manticore Search 28.

Технические детали

6 значения внутри GROUP_CONCAT() можно отсортировать и оставить только нужное количество:GROUP_CONCAT(id ORDER BY event_ts DESC, id DESC LIMIT 5) Посмотрим, как это работает. Будем использовать SphinxQL и явный GROUP BY - без него новая форма GROUP_CONCAT() не работает. События, с которыми будем работатьСоздадим таблицу activity, где каждый документ - это отдельное событие.

Текст события хранится в body, пользователь - в user_id, а время - в event_ts. 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().

Событие, по словам экспертов, усилит конкуренцию в сфере ИИ.