Как использовать GROUP BY и ORDER BY в SQL

Введение

Структурированные языки запросов (SQL) могут хранить и управлять большим количеством данных в нескольких таблицах. При работе с большими объемами данных важно понимать, как сортировать данные, особенно для анализа результатов или организации данных для отчетов или внешних коммуникаций.

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

В этом руководстве вы узнаете, как сортировать результаты запросов в SQL, используя операторы GROUP BY и ORDER BY. Вы также практически научитесь применять агрегатные функции и оператор WHERE в ваших запросах для дополнительной сортировки результатов.

Предварительные требования

Для выполнения этого руководства вам понадобится компьютер, на котором запущена какая-либо система управления реляционными базами данных (СУБД), использующая SQL. Инструкции и примеры в этом учебнике были проверены в следующей среде:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL установлен и защищен на сервере. Следуйте нашему руководству Как установить MySQL на Ubuntu 20.04, чтобы настроить это. В этом руководстве предполагается, что вы также настроили неадминистративного пользователя MySQL, как описано в Шаге 3 этого руководства.

Для практики сортировки результатов данных в этом учебнике вам потребуется база данных и таблица, загруженные образцовыми данными. Если у вас их нет готовых для вставки, вы можете прочитать следующий раздел Подключение к MySQL и настройка образцовой базы данных, чтобы узнать, как создать базу данных и таблицу. В этом учебнике будет ссылаться на эту образцовую базу данных и таблицу.

Подключение к MySQL и настройка образцовой базы данных

Если ваша база данных SQL работает на удаленном сервере, выполните вход через SSH на свой сервер с локальной машины:

ssh sammy@your_server_ip

Затем откройте приглашение MySQL, заменив sammy на информацию о вашей учетной записи пользователя MySQL:

mysql -u sammy -p

Создайте базу данных с именем movieDB:

CREATE DATABASE movieDB;

Если база данных была успешно создана, вы получите следующий вывод:

Output
Query OK, 1 row affected (0.01 sec)

Для выбора базы данных movieDB выполните следующее выражение USE:

USE movieDB;

Output
Database changed

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

• theater_id: хранит значения типа данных int для каждого зала кинотеатра и будет служить в качестве первичного ключа таблицы, что означает, что каждое значение в этом столбце будет функционировать как уникальный идентификатор соответствующей строки.
• date: использует тип данных DATE для хранения конкретной даты по году, месяцу и дню, когда был показан фильм. Этот тип данных соответствует следующим параметрам: четыре цифры для года и максимум две цифры для месяца и дня (ГГГГ-ММ-ДД).
• time: представляет запланированный сеанс фильма с помощью типа данных TIME по часам, минутам и секундам (ЧЧ:ММ:СС).
• movie_name: хранит название фильма с использованием типа данных varchar с максимальной длиной 40 символов.
• movie_genre: использует тип данных varchar с максимальной длиной 30 символов для хранения информации о жанре каждого фильма.
• guest_total: показывает общее количество гостей, посетивших сеанс фильма, с типом данных int.
• ticket_cost: использует тип данных decimal с точностью четыре и масштабом один, что означает, что значения в этом столбце могут иметь четыре цифры, и две цифры справа от десятичной точки. Этот столбец представляет собой стоимость билета на конкретный сеанс фильма.

Создайте таблицу с названием movie_theater, содержащую каждый из этих столбцов, запустив следующую команду CREATE TABLE:

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
); 

Затем вставьте некоторые образцовые данные в пустую таблицу:

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);

Output
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

После вставки данных вы готовы начать сортировку результатов запроса в SQL.

Использование GROUP BY

Функция оператора GROUP BY состоит в группировке записей с общими значениями. Оператор GROUP BY всегда используется с агрегатной функцией в запросе. Как вы можете помнить, агрегатная функция подводит итоги информации и возвращает один результат. Например, вы можете запросить общее количество или сумму столбца, и это приведет к получению одного значения в вашем результате. С оператором GROUP BY вы можете применить агрегатную функцию, чтобы получить одно значение результата для каждой желаемой группы.

GROUP BY полезно для возврата нескольких желаемых результатов, отсортированных по указанным вами группам, а не только одного столбца. Кроме того, GROUP BY должен всегда идти после оператора FROM и оператора WHERE, если вы решите его использовать. Вот пример того, как структурирован запрос с использованием GROUP BY и агрегатной функции:

SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

Чтобы проиллюстрировать, как можно использовать операторы GROUP BY, предположим, что вы руководите кампанией нескольких кинопремьер и хотите оценить успешность своих маркетинговых усилий. Вы просите местный кинотеатр предоставить данные, которые они собрали от гостей в пятницу и субботу. Начните с просмотра данных с помощью оператора SELECT и символа *, чтобы выбрать “каждый столбец” из таблицы movie_theater:

SELECT * FROM movie_theater;

Output
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)

Хотя эти данные полезны, вы хотите провести более глубокую оценку и отсортировать результаты по некоторым конкретным столбцам.

Поскольку вы работали над фильмами разных жанров, вас интересует, насколько они были хорошо приняты зрителями. Конкретно вы хотите узнать среднее количество людей, которые смотрели фильм каждого жанра. Используйте оператор SELECT, чтобы извлечь различные типы фильмов из столбца movie_genre. Затем примените агрегатную функцию AVG к столбцу guest_total, используйте AS, чтобы создать псевдоним для столбца с названием average, и включите оператор GROUP BY, чтобы сгруппировать результаты по столбцу movie_genre. Группировка в этом формате позволит вам получить средние результаты для каждого жанра фильма:

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Output
+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

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

Далее, предположим, что вы хотите измерить доходы кинотеатра за два отдельных дня. Следующий запрос возвращает значения из столбца date, а также значения, возвращаемые агрегатной функцией SUM. Конкретно агрегатная функция SUM заключит математическое уравнение в скобки для умножения (с использованием оператора *) общего числа гостей на стоимость билета, представленную как: SUM(guest_total * ticket_cost). В этом запросе используется ключевое слово AS для предоставления псевдонима total_revenue для столбца, возвращаемого агрегатной функцией. Затем завершите запрос с помощью оператора GROUP BY, чтобы сгруппировать результаты запроса по столбцу date:

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Output
+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

Поскольку вы использовали GROUP BY для группировки столбца date, ваш вывод предоставляет результаты общего дохода от продажи билетов за каждый день, в данном случае $7,272 за пятницу, 27 мая, и $9,646 за субботу, 28 мая.

Теперь представьте, что вы хотите сосредоточиться на и проанализировать один фильм: The Bad Guys. В этом сценарии вы хотите выяснить, как время и ценовые точки влияют на выбор семьи по просмотру анимационного фильма. Для этого запроса используйте агрегатную функцию MAX для извлечения максимальной ticket_cost, обязательно включив AS, чтобы создать псевдоним для столбца price_data. Затем используйте WHERE clause, чтобы сузить результаты по movie_name исключительно на “The Bad Guys”, и используйте AND, чтобы также определить самые популярные времена показа на основе числа guest_total, которые были более 100 с оператором сравнения >. Завершите запрос оператором GROUP BY и сгруппируйте его по time:

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Output
+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

Согласно этому выводу, больше всего гостей посетили фильм The Bad Guys на раннем матине в 9:00 утра, который имел более доступную цену билета в $8.00. Однако эти результаты также показывают, что гости фильма заплатили более высокую цену за билет в $13.00 в 17:00, что указывает на то, что семьи предпочитают показы, которые не слишком поздно вечером, и готовы заплатить немного больше за билет. Это кажется справедливой оценкой по сравнению с временем в 22:00, когда на фильм The Bad Guys пришло всего 83 гостя, и цена за билет составила $18.00. Эта информация может быть полезной для менеджера кинотеатра, чтобы предоставить доказательства того, что открытие большего количества ранних и вечерних сеансов может увеличить посещаемость семей, которые принимают решение на основе предпочтительного времени и цены.

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

SELECT DISTINCT movie_name FROM movie_theater;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

Как вы помните из просмотра всех данных в таблице, были дубликаты названий фильмов, так как было несколько сеансов. Поэтому DISTINCT удалил эти дубликаты и эффективно сгруппировал уникальные значения в один столбец movie_name. Это эффективно идентично следующему запросу, который включает оператор GROUP BY:

SELECT movie_name FROM movie_theater GROUP BY movie_name;

Теперь, когда вы практиковались в использовании GROUP BY с агрегатными функциями, следующим шагом будет изучение того, как сортировать результаты вашего запроса с помощью оператора ORDER BY.

С ПОМОЩЬЮ ORDER BY

Функция оператора ORDER BY состоит в сортировке результатов в порядке возрастания или убывания на основе указанных в запросе столбцов. В зависимости от типа данных, хранящихся в столбце, который вы указываете после него, ORDER BY упорядочивает их в алфавитном или числовом порядке. По умолчанию ORDER BY сортирует результаты в порядке возрастания; если вы предпочитаете упорядочение по убыванию, вам нужно включить ключевое слово DESC в ваш запрос. Вы также можете использовать оператор ORDER BY с оператором GROUP BY, но он должен следовать после него, чтобы работать правильно. Аналогично GROUP BY, ORDER BY также должен идти после операторов FROM и WHERE. Общий синтаксис использования ORDER BY следующий:

SELECT column_1, column_2 FROM table ORDER BY column_1;

Давайте продолжим с примером данных для кинотеатра и практикуем сортировку результатов с помощью ORDER BY. Начнем с следующего запроса, который извлекает значения из столбца guest_total и упорядочивает эти числовые значения с помощью оператора ORDER BY:

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Output
+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

Поскольку ваш запрос указал столбец с числовыми значениями, оператор ORDER BY организовал результаты в числовом порядке по возрастанию, начиная с 25 в столбце guest_total.

Если вы предпочитаете упорядочить столбец по убыванию, вы добавите ключевое слово DESC в конце запроса. Кроме того, если вы хотите упорядочить данные по символьным значениям в столбце movie_name, вы укажете это в своем запросе. Давайте выполним такой тип запроса, используя ORDER BY для упорядочивания столбца movie_name по символьным значениям по убыванию. Отсортируем результаты еще более далеко, включив предложение WHERE, чтобы получить данные о фильмах, идущих в 22:00 из столбца time:

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

Этот набор результатов перечисляет четыре различных киносеанса в 22:00 в алфавитном порядке по убыванию, начиная с Top Gun Maverick и заканчивая Downtown Abbey A New Era.

Для следующего запроса объедините операторы ORDER BY и GROUP BY с агрегатной функцией SUM, чтобы сгенерировать результаты по общей выручке за каждый фильм. Однако предположим, что кинотеатр неправильно подсчитал общее количество гостей и забыл включить специальные мероприятия, на которые были предварительно куплены и зарезервированы билеты для группы из 12 человек на каждый сеанс.

В этом запросе используйте SUM и включите дополнительных 12 гостей на каждом показе фильма, реализовав оператор сложения +, а затем добавив 12 к guest_total. Убедитесь, что это заключено в круглые скобки. Затем умножьте эту сумму на ticket_cost с помощью оператора * и завершите математическое уравнение, закрыв скобки в конце. Добавьте ключевое слово AS, чтобы создать псевдоним для нового столбца под названием total_revenue. Затем используйте GROUP BY, чтобы сгруппировать результаты total_revenue для каждого фильма на основе данных, полученных из столбца movie_name. Наконец, используйте ORDER BY, чтобы организовать результаты в новом столбце total_revenue в порядке возрастания:

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Output
+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

Этот набор результатов сообщает нам об общем доходе для каждого фильма с дополнительной продажей 12 билетов для гостей и организует общие продажи билетов в порядке возрастания от наименьшего к наибольшему. Из этого мы узнаем, что фильм “Топ Ган: Мэверик” получил наибольшие продажи билетов, а “Мужчины” – наименьшие. Тем временем фильмы “Плохие парни” и “Аббатство Даунтон: Новая эра” были очень близки по общим продажам билетов.

В этом разделе вы практиковали различные способы реализации оператора ORDER BY и способы указания предпочтительного порядка, таких как возрастающий и убывающий порядок как для символьных, так и для числовых значений данных. Вы также узнали, как использовать ключевое слово WHERE, чтобы сузить ваш результат, и выполнили запрос с использованием как операторов GROUP BY, так и ORDER BY с агрегатной функцией и математическим уравнением.

Заключение

Понимание того, как использовать операторы GROUP BY и ORDER BY, важно для сортировки ваших результатов и данных. Независимо от того, хотите ли вы организовать несколько результатов в одну группу, упорядочить один из ваших столбцов в алфавитном порядке по убыванию или сделать и то, и другое одновременно; это зависит от вас и ваших желаемых результатов. Вы также узнали о других способах дополнительной сортировки ваших результатов с помощью оператора WHERE. Если вы хотите узнать больше, ознакомьтесь с нашим руководством по Как использовать маски в SQL, чтобы практиковать фильтрацию результатов с помощью оператора LIKE.