Группировка по полю, содержащему число, используется значительно чаще, чем группировка по текстовому полю. Я думаю, вы использовали функции вроде AVG(), SUM(), MIN() или MAX(). А вот использование функций GROUP_CONCAT, JSON_ARRAYAGG и JSON_OBJECTAGG встречается на столько редко, что разработчики забывают о том, что группировка по текстовому полю вообще возможна.
Чтобы понять зачем это может быть нужно, давайте попробуем рассмотреть примеры.
Группировка значения признака в случае отношения один ко многим (или многие ко многим)
По русски это значит, что какой то признак имеет несколько значений для одной и той же сущности, которую он описывает.
К примеру, у нас есть база данных с национальными флагами, которая содержит три таблицы —
- флаги — основная таблица (flags);
- цвета, используемые флагами (flag_color);
- таблица связей между цветами и флагами (flag_color_link).
Пример контента в таблицах:
Всего пара флагов (России и Японии),
которые используют три цвета (белый, красный, синий),
что отражено в таблице связей:
Если вы хотим узнать какие цвета использованы на национальных флагах, то запрос
|
1 2 3 |
SELECT flags.name, flag_color.color FROM flags INNER JOIN flag_color_link ON flag_color_link.flag_id = flags.ID INNER JOIN flag_color ON flag_color_link.color_id = flag_color.ID |
Выдаст нам следующий список
Из списка можно понять, какие цвета содержат флаги разных стран, но хотелось бы сгруппировать список по конкретной стране, сформировав цвета в одну строку. При этом кол-во строк будет соответствовать количеству стран.
Вот здесь нам поможет группировка GROUP_CONCAT.
|
1 2 3 4 5 6 7 |
SELECT flags.name, GROUP_CONCAT(flag_color.color ORDER BY flag_color.color DESC SEPARATOR ', ') as color FROM flags INNER JOIN flag_color_link ON flag_color_link.flag_id = flags.ID INNER JOIN flag_color ON flag_color_link.color_id = flag_color.ID GROUP BY flags.name |
которая выведет следующий результат:
Как видите, можно выбрать разделитель (SEPARATOR) и произвести сортировку свойств (ORDER BY).
Применение данной группировки позволит вам избежать рутинных операций по программной пост-обработке списка.
JSON_ARRAYAGG / JSON_OBJECTAGG
Функции появляются в версии mysql 5.7.22. В отличии от GROUP_CONCAT, эти группирующие функции используются не для вывода результирующего значения на фронт-енд, а для подготовки данных для последующей обработки.
Применительно к предыдущему примеру JSON_ARRAYAGG
|
1 2 3 4 5 6 7 |
SELECT flags.name, JSON_ARRAYAGG(flag_color.color) as color FROM flags INNER JOIN flag_color_link ON flag_color_link.flag_id = flags.ID INNER JOIN flag_color ON flag_color_link.color_id = flag_color.ID GROUP BY flags.name |
выдаст нам в столбце color валидный JSON массив цветов (например, [«red», «blue», «white»] для России).
JSON_OBJECTAGG имеет несколько другое назначение, потому я обращусь к другому примеру для демонстрации его возможностей.
Ниже приведен небольшой дамп из таблицы мета данных публикаций сайта (wp_postmeta) на движке CMS WordPress.
Есть суррогатный ключ (meta_id), идентификатор публикации (post_id) и пары «ключ — значение» (meta_key, meta_value).
Запрос
|
1 2 3 |
SELECT post_id, JSON_OBJECTAGG(meta_key, meta_value) as data FROM wp_postmeta GROUP BY post_id |
соберет JSON объекты для каждой публикации (post_id) в значения вида:
|
1 |
{"City": Москва", "Lat": "55.702063777378136", "Lng": "37.81433949999996"} |
При использовании описанных выше функций, надо учитывать особенности:
- Если одинаковый ключ в JSON_OBJECTAGG встречается многократно, то будет использовано последнее значение для ключа; т.е. к примеру из нескольких значений мета поля «Tel» будет выбран последний сохраненный в базе номер телефона.
- Длина строки для GROUP_CONCAT ограничена настройками mysql — задаётся в переменной — group_concat_max_len (по умолчанию — 1024).
А как сделать связку дня 5-7 таблиц как в первом примере?