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

Группировка по полю, содержащему число, используется значительно чаще, чем группировка по текстовому полю. Я думаю, вы использовали функции вроде 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

Июнь 15, 2018 г.

Для начала выберем функцию с подходящей гранулярностью. DATEDIFF явно не подходит, т.к. даёт разницу в кол-ве целых дней. К счастью, есть аналогичная функция - TIMEDIFF, которая вычисляет разницу с точностью до микросекунд. Результат работы TIMEDIFF ...

Читать

Выборка случайных строк в mysql

Октябрь 18, 2010 г.

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

Читать

 

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

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



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