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

Речь идет о возможности 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;

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

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;

Как видите, 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.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *