Почему следует использовать функцию MySQL GROUP CONCAT

0 ∞

Речь идет о функции MySQL GROUP CONCAT и как она может изменить работу с результатами запросов. Особенно если база данных является источником данных для приложения.

Содержание

База данных

В качестве образца я буду использовать базу данных Sakila sample database. База данных включает в себя ряд связанных таблиц на тему кино: от актеров и киностудий до пунктов видеопроката. Полную структуру этой базы данных можно увидеть на сайте разработки MySQL.

Устаревший способ группировки

Оператор GROUP BY является великолепным инструментом для выборки связанных данных. Но он не подходит для точной сортировки данных.

Представим, что мы являемся владельцами пункта проката фильмов и желаем вознаградить тех клиентов, которые брали много ужастиков. Для этого нам нужно узнать, какие фильмы брал в прокате каждый клиент. Один из способов сделать это — переместить инструкцию GROUP BY SELECT во вложенный запрос, который возвращает идентификаторы пользователей, отвечающие всем требованиям. Затем можно ограничить результаты внешнего запроса теми клиентами, чьи идентификаторы являются частью внутреннего результирующего набора.

Ниже приводится код SQL, который выполнит эту работу без MySQL GROUP CONCAT SEPARATOR:

SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer,
        A.phone, 
        F.title, 
        date(R.rental_date) AS rental_date
 FROM sakila.rental R 
     LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id 
     LEFT JOIN sakila.film F ON I.film_id = F.film_id 
     LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
     LEFT JOIN sakila.category C ON FC.category_id = C.category_id 
     LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
     LEFT JOIN sakila.address A ON CU.address_id = A.address_id
 WHERE CU.customer_id in 
       (SELECT CU.customer_id
        FROM rental R
        LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
        LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id 
        LEFT JOIN sakila.film F ON I.film_id = F.film_id 
        LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
        LEFT JOIN sakila.category C ON FC.category_id = C.category_id 
        WHERE C.name = "Horror"
        GROUP BY CU.customer_id
        HAVING COUNT(CU.customer_id) >= 3)
 AND C.name = "Horror"
 ORDER BY customer, title, rental_date DESC;
Как использовать уникальные индексы в MySQL и других базах данных

Получаем трех первых клиентов с названиями фильмов, взятых напрокат, и датами:

customer phone title rental_date
----------------------------------------------------------------
ADAM, NATHANIEL 111177206479 ANALYZE HOOSIERS 2005-08-19
ADAM, NATHANIEL 111177206479 FREDDY STORM 2005-08-22
ADAM, NATHANIEL 111177206479 STRANGERS GRAFFITI 2005-08-23
ANDREW, JOSE 961370847344 EGYPT TENENBAUMS 2005-07-31
ANDREW, JOSE 961370847344 FIDELITY DEVIL 2005-05-30
ANDREW, JOSE 961370847344 HIGH ENCINO 2005-07-07
ANDREW, JOSE 961370847344 LOLA AGENT 2005-08-02
AQUINO, OSCAR 474047727727 AFFAIR PREJUDICE 2005-07-28
AQUINO, OSCAR 474047727727 DRUMS DYNAMITE 2005-06-20
AQUINO, OSCAR 474047727727 EGYPT TENENBAUMS 2005-07-28
AQUINO, OSCAR 474047727727 STREETCAR INTENTIONS 2005-08-01
и т. д…

Работает, даже несмотря на то, что внутренние и внешние операторы SQL WHERE повторяются. Но не это главное — приложение, которое получает результаты запросов, должно отслеживать имена клиентов, чтобы знать, когда перейти к следующему. Я проделывал это много раз, и в результатах всегда присутствовала путаница.

Способ создания группированного списка с помощью функции GROUP_CONCAT

Функция MySQL GROUP CONCAT не является новой. Она объединяет все ненулевые значения из группы и возвращает их в виде строки с разделителями-запятыми. В сочетании с оператором GROUP BY она позволяет поместить сгруппированные данные в одну строку.

Перепишем наш код, применив функцию GROUP_CONCAT:

SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer,
        A.phone, 
        date(R.rental_date) AS rental_date,
        GROUP_CONCAT(F.title) AS titles,
        COUNT(*) AS rentals_count
 FROM sakila.rental R 
       LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id 
       LEFT JOIN sakila.film F ON I.film_id = F.film_id 
       LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id 
       LEFT JOIN sakila.category C ON FC.category_id = C.category_id 
       LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
       LEFT JOIN sakila.address A ON CU.address_id = A.address_id
 WHERE C.name = "Horror" 
 GROUP BY R.customer_id
 HAVING rentals_count >= 3
 ORDER BY customer, title, rental_date DESC;
Команды MySQL

Как видите, c помощью MySQL GROUP CONCAT решена проблема с лишними данными, поскольку больше не нужно отфильтровывать результаты.

Фильмы, взятые напрокат, перечислены в колонке «titles»:

customer phone rental_date titles rentals_count
--------------------------------------------------------------------------------------------------------------------------------
ADAM, NATHANIEL 111177206479 2005-08-22 FREDDY STORM,ANALYZE HOOSIERS,STRANGERS GRAFFITI 3
ANDREW, JOSE 961370847344 2005-07-31 EGYPT TENENBAUMS,LOLA AGENT,FIDELITY DEVIL,HIGH ENCINO 4
AQUINO, OSCAR 474047727727 2005-07-28 EGYPT TENENBAUMS,AFFAIR PREJUDICE,STREETCAR INTENTIONS,DRUMS DYNAMITE 4
ARTIS, CARL 20064292617 2005-08-18 BOWFINGER GABLES,RULES HUMAN,YENTL IDAHO,FIDELITY DEVIL 4
BARBEE, CLAYTON 380077794770 2005-05-26 BEHAVIOR RUNAWAY,LOVE SUICIDES,SWARM GOLD 3
и т. д…

Кроме этого решена еще одна задача — отображение сгруппированных данных в одной строке. Это положительно сказывается на работе приложения, так как доступ к сгруппированным данным осуществляется с помощью одной операции.

Это довольно простой процесс с использованием функции разбиения строк MySQL GROUP CONCAT, который реализуется большинством языков программирования. Например, в PHP эта функция называется «explode». В качестве параметров функция принимает разделитель и строку, и возвращает данные в виде массива. Ниже приведен пример того, как можно получить названия фильмов (titles), используя упомянутый выше запрос:

//извлечение результирующего набора
 $res=$mysqli--->query($select_statement);
 //итерация по каждой строке
 while ($row = $res->fetch_array(MYSQLI_ASSOC)) {
   //эта инструкция разделяет строку titles
   //запятыми в массиве
   $titles_array = explode(',', $row['titles']);
   //работа с массивом названий...
 }

Еще одним преимуществом использования функции GROUP_CONCAT является то, что строковое значение можно применять как часть оператора IN:

$res_films = $mysqli->query("SELECT * FROM sakila.film WHERE title = IN ($titles_array)");
 // работа с $res_films...

Заключение

Не хотите использовать запятые в качестве разделителей? Хотите сортировать элементы? Функция MySQL GROUP CONCAT подходит для решения обеих задач. Мы не будем в это углубляться, но если вам интересно, полный код можно изучить в документации по MySQL.