Перевод статьи.

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

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

Подключаемся:

mysql -u nevvad -p

И создадим базу данных:

CREATE DATABASE movieDB;
USE movieDB;

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

• theater_id – хранит значения данных типа int для каждого зала кинотеатра и будет служить primary key таблицы, то есть каждое значение в этом столбце будет функционировать как уникальный идентификатор для соответствующей строки.
• date – использует тип данных DATE для хранения конкретной даты по году, месяцу и дню показа фильма. Этот тип данных придерживается следующих параметров: четыре цифры для года и максимум две цифры для месяца и дня (YYYY-MM-DD).
• time – представляет запланированный показ фильма с типом данных TIME в часах, минутах и секундах (HH:MM:SS).
• movie_name – хранит название фильма, используя тип данных varchar с максимальным количеством символов 40.
• movie_genre – использует тип данных varchar с максимальным количеством символов 30 для хранения информации о соответствующем жанре каждого фильма.
• guest_total – показывает общее количество зрителей, которые посетили показ фильма. Тип данных int.
• ticket_cost – использует десятичный тип данных с точностью четыре и масштабом один, то есть значения в этом столбце могут иметь четыре цифры и две цифры справа от десятичной точки. В этом столбце содержится стоимость билета на конкретный киносеанс.

Создадим таблицу с именем 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);

Функция оператора 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;
 
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| 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 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+

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

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;
+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+

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

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

SELECT DATE, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY DATE;
+------------+---------------+
| DATE       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+

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

Теперь представим, что мы хотим проанализировать конкретный фильм: «The Bad Guys». В этом сценарии мы хотим выяснить, как время и цена влияют на выбор семьи для просмотра анимационного фильма. Для этого запроса используем агрегатную функцию MAX для получения максимальной стоимости билета (ticket_cost), обязательно включим AS для создания псевдонима столбца price_data. После этого используем условие WHERE, чтобы сузить результаты по movie_name (названию фильма), а также используйте 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;
+----------+------------+
| TIME     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+

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

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

SELECT DISTINCT movie_name FROM movie_theater;
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A NEW Era |
| Men                     |
| The Bad Guys            |
+-------------------------+

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

SELECT movie_name FROM movie_theater GROUP BY movie_name;

Функция оператора 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;
 
+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+

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

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

SELECT movie_name FROM movie_theater
WHERE TIME = '10:00:00' 
ORDER BY movie_name DESC;
 
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A NEW Era |
+-------------------------+

В этом отчете, перечислены четыре различных киносеанса в 10:00 вечера в нисходящем алфавитном порядке, начиная с Top Gun Maverick и заканчивая Downtown Abbey A New Era.

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

В этом запросе используем SUM для включения 12 дополнительных зрителей на каждом показе фильма, применяем оператор сложения +, а затем добавив 12 к guest_total. Не забиваем про круглые скобки. Затем умножьте эту сумму на стоимость билета с помощью оператора * и завершите математическое уравнение, закрыв скобки в конце. Добавим оператор 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;
 
+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A NEW Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+

Данный результат, показывает нам общий доход для каждого фильма (с учетом дополнительных 12 ранее проданных билетов) и упорядочивает общие продажи билетов в порядке возрастания от наименьшего к наибольшему. Из него мы узнаем, что на картину “Top Gun Maverick” пришлось наибольшее количество продаж билетов, а “Men” – наименьшее. Между тем, фильмы “The Bad Guys” и “Downton Abbey A New Era” идут нос к носу по купленным билетам.