Группировка строковых значений в выборке из базы данных

Группировка по полю, содержащему число, используется значительно чаще, чем группировка по текстовому полю. Я думаю, вы использовали функции вроде AVG(), SUM(), MIN() или MAX(). А вот использование функций GROUP_CONCAT, JSON_ARRAYAGG и JSON_OBJECTAGG встречается на столько редко, что разработчики забывают о том, что группировка по текстовому полю вообще возможна.

Чтобы понять зачем это может быть нужно, давайте попробуем рассмотреть примеры.

Группировка значения признака в случае отношения один ко многим (или многие ко многим)

По русски это значит, что какой то признак имеет несколько значений для одной и той же сущности, которую он описывает.

К примеру, у нас есть база данных с национальными флагами, которая содержит три таблицы —

  • флаги — основная таблица (flags);
  • цвета, используемые флагами (flag_color);
  • таблица связей между цветами и флагами (flag_color_link).

Пример контента в таблицах:

Всего пара флагов (России и Японии),

которые используют три цвета (белый, красный, синий),

что отражено в таблице связей:

Если вы хотим узнать какие цвета использованы на национальных флагах, то запрос

Выдаст нам следующий список

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

Вот здесь нам поможет группировка GROUP_CONCAT.

которая выведет следующий результат:

Как видите, можно выбрать разделитель (SEPARATOR) и произвести сортировку свойств (ORDER BY).

Применение данной группировки позволит вам избежать рутинных операций по программной пост-обработке списка.

JSON_ARRAYAGG / JSON_OBJECTAGG

Функции появляются в версии mysql 5.7.22. В отличии от GROUP_CONCAT, эти группирующие функции используются не для вывода результирующего значения на фронт-енд, а для подготовки данных для последующей обработки.

Применительно к предыдущему примеру JSON_ARRAYAGG

выдаст нам в столбце color валидный JSON массив цветов (например, [«red», «blue», «white»] для России).

JSON_OBJECTAGG имеет несколько другое назначение, потому я обращусь к другому примеру для демонстрации его возможностей.

Ниже приведен небольшой дамп из таблицы мета данных публикаций сайта (wp_postmeta) на движке CMS WordPress.

Есть суррогатный ключ (meta_id),  идентификатор публикации (post_id) и пары «ключ — значение» (meta_key, meta_value).

Запрос

соберет JSON объекты для каждой публикации (post_id) в значения вида:

При использовании описанных выше функций, надо учитывать особенности:

  • Если одинаковый ключ в JSON_OBJECTAGG встречается многократно, то будет использовано последнее значение для ключа; т.е. к примеру из нескольких значений мета поля «Tel» будет выбран последний сохраненный в базе номер телефона.
  • Длина строки для GROUP_CONCAT ограничена настройками mysql — задаётся в переменной — group_concat_max_len (по умолчанию — 1024).
Написать комментарий

Мало букафф? Читайте есчо !

Как получить номер строки в MySql?

Ноябрь 5, 2016 г.

Что делать, если нужно пронумеровать строки прямо в запросе? Полистав руководство MySql 5.5, нужной функции я не нашел. Мне нужно было сохранять позиции пользователей в рейтинге, т.е. пронумеровать пользователей в зависимости от значения их рейтинга. ...

Читать

Считаем полное число строк в запросе MySQL, ограниченного LIMIT

Ноябрь 2, 2012 г.

О полезном операторе - SQL_CALC_FOUND_ROWS. Это конструкция просто незаменима для организации постраничного вывода каких либо данных. Все время забываю синтаксис, приходится каждый раз искать. Решил записать в блоге, чтобы уж не терять :). Во всех ...

Читать

 

Комментарии к «Группировка строковых значений в выборке из базы данных»

Понравилась статья? Есть вопросы? - пишите в комментариях.



Комментарий:

Один комментарий в “Группировка строковых значений в выборке из базы данных”

  1. Павел:

    А как сделать связку дня 5-7 таблиц как в первом примере?