Как использовать GROUP BY и ORDER BY в SQL: Полное руководство

---

Введение

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

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

Эта статья предоставляет практическое руководство по мастерству сортировки и агрегации данных в SQL, сосредотачиваясь на предложениях GROUP BY и ORDER BY. Мы проведем вас через создание образца базы данных MySQL и заполнение ее данными. Затем мы углубимся в детализированные, реальные примеры того, как использовать GROUP BY с различными агрегатными функциями, такими как SUM, AVG и COUNT, чтобы эффективно обобщать данные. Вы также узнаете, как комбинировать их с предложением WHERE для предварительной фильтрации и предложением HAVING для фильтрации после агрегации. Мы также объясним предложение ORDER BY для сортировки результатов и продемонстрируем, как комбинировать все эти операторы для создания мощных, организованных отчетов. Чтобы обеспечить полное понимание, статья завершится важным разделом о распространенных ошибках и отладке, а также подробным разделом с часто задаваемыми вопросами, что сделает ее полным ресурсом как для новичков, так и для тех, кто хочет укрепить свои навыки SQL.

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

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

• Сервер под управлением Ubuntu 20.04 с пользователем, не обладающим правами root, но имеющим административные права sudo, и включенным файрволом. Следуйте нашему Руководству по начальной настройке сервера с Ubuntu 20.04, чтобы начать.
• MySQL установлен и защищен на сервере. Следуйте нашему руководству по установке MySQL на Ubuntu 20.04, чтобы настроить его. Это руководство предполагает, что вы также настроили не-root пользователя MySQL, как описано в Шаге 3 этого руководства.

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

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

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

ssh sammy@your_server_ip 

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

mysql -u sammy -p 

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

CREATE DATABASE movieDB; 
 

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

Query OK, 1 row affected (0.01 sec) 

Чтобы выбрать базу данных movieDB, выполните следующую команду USE:

USE movieDB; 
 

Database changed 

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

• theater_id: хранит значения типа int для каждого зала театра, и будет служить первичным ключом таблицы, что означает, что каждое значение в этом столбце будет функционировать как уникальный идентификатор для соответствующей строки.
• date: использует тип данных DATE для хранения конкретной даты по году, месяцу и дню, когда фильм был показан. Этот тип данных соответствует следующим параметрам: четыре цифры для года и максимум две цифры для месяца и дня (YYYY-MM-DD).
• время: представляет собой запланированное время показа фильма с использованием типа данных TIME, выраженного в часах, минутах и секундах (HH:MM:SS).
• 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); 
 

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; 
 

+------------+------------+----------+-------------------------+-------------+-------------+-------------+ | 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) 

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

SQL GROUP BY с функцией AVG

Поскольку вы работали над фильмами в нескольких разных жанрах, вам интересно узнать, как они были восприняты зрителями. В частности, вы хотите знать среднее количество людей, которые посмотрели каждый жанр фильма. Используйте 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; 
 

+-------------+----------+ | 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 привлекли наибольшее среднее количество зрителей на сеанс.

SQL GROUP BY с функцией COUNT

Теперь вы, возможно, захотите узнать, сколько раз каждый фильм показывался в выходные. Это полезно, если вы планируете количество будущих показов на основе прошлой частоты.

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

Следующий запрос выбирает столбец movie_name, подсчитывает количество записей для каждого фильма и группирует результаты по movie_name:

SELECT movie_name, COUNT(*) AS showings FROM movie_theater GROUP BY movie_name; 

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

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

Группируя ваши результаты и подсчитывая их, вы можете легко свести большие объемы данных к читаемому и практическому формату.

SQL GROUP BY с функцией SUM

Далее, скажем, вы хотите измерить доходы театра за два отдельных дня. Следующий запрос возвращает значения из столбца 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; 
 

+------------+---------------+ | 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 мая.

SQL GROUP BY с WHERE условием и MAX функцией

Теперь представьте, что вы хотите сосредоточиться и проанализировать один фильм: Плохие парни. В этом сценарии вы хотите выяснить, как время и ценовые категории влияют на выбор семьи посмотреть анимационный фильм. Для этого запроса используйте агрегатную функцию MAX, чтобы получить максимальную стоимость ticket_cost, обязательно включив AS, чтобы создать псевдоним для столбца price_data. Затем используйте WHERE, чтобы сузить результаты только до “Плохие парни”, и используйте 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 | +----------+------------+ 2 rows in set (0.00 sec) 

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

GROUP BY против DISTINCT

Хотя 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            | +-------------------------+ 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; 
 

+-------------+ | 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; 
 

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

Этот набор результатов перечисляет четыре различных показа фильмов в 22:00 в порядке убывания алфавита, начиная с «Топ Ган: Maverick» и заканчивая «Даунтон Абби: Новая эра».

Комбинирование GROUP BY с ORDER BY

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

В этом запросе используйте SUM и включите дополнительных 12 гостей на каждое showing фильма, реализуя оператор сложения + и затем добавляя 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; 
 

+-------------------------+---------------+ | 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 продаж билетов для гостей и организует общие продажи билетов в порядке возрастания от наименьших к наибольшим. Из этого мы узнаем, что фильм ‘Топ Ган: MAVERICK’ получил наибольшее количество проданных билетов, в то время как ‘Мужчины’ — наименьшее. Тем временем, фильмы ‘Плохие парни’ и ‘Дауонтон Эбби: Новая эра’ были очень близки по общим продажам билетов.

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

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

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

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

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

В этом случае запрос группирует все строки по movie_genre и затем вычисляет среднее количество гостей для каждой группы.

С другой стороны, используйте ORDER BY, когда вы хотите отсортировать окончательные результаты вашего запроса. Это полезно, когда вы отображаете данные и хотите организовать их в определенной последовательности: по алфавиту, числовым значением, по дате или по пользовательскому приоритету. Если вы хотите узнать, какой фильм имел наибольшее количество гостей, вы можете использовать ORDER BY, чтобы отсортировать ваши результаты по guest_total в порядке убывания.

SELECT movie_name, guest_total FROM movie_theater ORDER BY guest_total DESC; 

Здесь ORDER BY не группирует данные, он просто сортирует строки на основе значений в колонке guest_total.

Во многих случаях вы будете использовать оба вместе. Например, вы можете сгруппировать данные, чтобы вычислить итоги или средние значения, а затем отсортировать эти сгруппированные результаты, чтобы выявить тенденции:

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

Этот объединённый запрос даёт вам общий доход по фильмам и распорядок по убыванию, предлагая как анализ, так и ясное представление.

В заключение:

• Используйте GROUP BY, чтобы вычислять и анализировать сгруппированные данные.
• Используйте ORDER BY, чтобы организовать результаты запроса для удобства чтения или отчетности.
• Используйте оба, когда вам нужно агрегировать и затем сортировать результаты.

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

Комбинирование GROUP BY с HAVING

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

Например, скажем, вы хотите выяснить, какие жанры фильмов привлекали в среднем более 100 зрителей за показ. Поскольку вы работаете с группированными результатами, используя агрегатную функцию (AVG()), вам необходимо использовать оператор HAVING, а не WHERE.

Вот как вы можете написать этот запрос:

SELECT movie_genre, AVG(guest_total) AS avg_guests FROM movie_theater GROUP BY movie_genre HAVING AVG(guest_total) > 100; 

+-------------+-------------+ | movie_genre | avg_guests | +-------------+-------------+ | Action      | 131.0000    | | Drama       | 115.0000    | | Animation   | 118.3333    | +-------------+-------------+ 3 rows in set (0.00 sec) 

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

Теперь вы можете задаться вопросом, когда использовать HAVING? Используйте HAVING, когда:

• Вы фильтруете результаты на основе агрегированных значений, таких как SUM(), AVG(), COUNT(), MAX() или MIN().
• Вы должны применить условия после того, как строки будут сгруппированы.

Давайте рассмотрим другой пример. Допустим, вы хотите узнать, какие фильмы показывались больше двух раз. Вы бы использовали функцию COUNT(), чтобы подсчитать количество показов, сгруппировать результаты по названию фильма, а затем отфильтровать группы с помощью HAVING:

SELECT movie_name, COUNT(*) AS total_showings FROM movie_theater GROUP BY movie_name HAVING COUNT(*) > 2; 

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

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

Использование HAVING в комбинации с GROUP BY дает вам больше контроля и понимания агрегированных данных, помогая улучшить ваши отчеты, панели мониторинга и решения на основе данных.

Распространенные ошибки и отладка

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

Неправильное использование GROUP BY с неагрегированными столбцами

Одной из самых распространенных ошибок, с которыми сталкиваются разработчики при использовании GROUP BY, является попытка включить столбцы в оператор SELECT, которые не являются частью оператора GROUP BY и не агрегируются с использованием агрегатной функции. Большинство систем управления реляционными базами данных (RDBMS), таких как MySQL (в строгом режиме), PostgreSQL и SQL Server, вернут ошибку, когда это произойдет.

Пример:

Допустим, вы пытаетесь написать следующий запрос, чтобы получить среднее количество гостей по жанрам фильмов, но также включить столбец movie_name:

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

Ошибка (в большинстве SQL-движков):

ERROR 1055 (42000): 'movie_theater.movie_name' isn't in GROUP BY 

Эта ошибка возникает, потому что movie_name не является частью GROUP BY и не агрегируется. SQL не знает, как обрабатывать несколько названий фильмов в одной группе жанров, если вы не укажете ему точно, что с ними делать, например, применяя агрегатные функции, такие как MIN(), MAX() или GROUP_CONCAT().

Правильное использование:

Чтобы исправить ошибку, либо:

• Добавьте movie_name в оператор GROUP BY (что создаст группу для каждой уникальной комбинации movie_genre и movie_name), или
• Удалите movie_name из SELECT выражения, так как оно не имеет смысла без агрегации в данном случае.

Исправленный запрос:

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

Этот запрос успешно группирует данные по жанрам и возвращает среднее количество гостей по жанрам, как и ожидалось.

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

Некорректный порядок сортировки с использованием ORDER BY

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

• Сортировка по столбцам, которые являются неоднозначными или не существуют в наборе результатов.
• Забывая указать ASC или DESC, когда это необходимо для ясности.
• Попытка использовать ORDER BY до GROUP BY или в неправильном порядке операций SQL.

Пример:

Рассмотрите следующий запрос:

SELECT movie_name, SUM(guest_total) AS total_guests FROM movie_theater GROUP BY movie_name ORDER BY guest_total; 

Этот запрос может вернуть ошибку или неожиданные результаты, потому что guest_total не находится в списке SELECT; он был агрегирован как SUM(guest_total) и переименован как total_guests.

Правильное использование:

Чтобы решить это, убедитесь, что вы заказываете по одному из следующих параметров:

• Правильное агрегатное выражение (SUM(guest_total)), или
• Псевдоним, который вы предоставили (total_guests).

Исправленный запрос:

SELECT movie_name, SUM(guest_total) AS total_guests FROM movie_theater GROUP BY movie_name ORDER BY total_guests DESC; 

Эта версия запроса сортирует фильмы по общему количеству гостей в порядке убывания, что, вероятно, и является вашим намерением.

Избегание этих распространенных ошибок сделает ваши запросы более надежными, поддерживаемыми и свободными от ошибок, особенно при работе с большими и сложными наборами данных.

Использование агрегатных функций без GROUP BY

Вы не можете смешивать агрегатные функции и неагрегированные столбцы в операторе SELECT, если вы не укажете, как группировать данные. Если SQL не знает, как агрегировать неагрегированный столбец, он выдает ошибку.

Неверный пример:

SELECT movie_genre, SUM(guest_total) FROM movie_theater; 

Ошибка (MySQL):

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'movieDB.movie_theater.movie_genre'; this is incompatible with sql_mode=only_full_group_by 

Здесь SQL не знает, как суммировать несколько значений movie_genre без их группировки.

Исправленный пример:

SELECT movie_genre, SUM(guest_total) AS total_guests FROM movie_theater GROUP BY movie_genre; 

Вывод:

+-------------+--------------+ | movie_genre | total_guests | +-------------+--------------+ | Action      |          393 | | Drama       |          345 | | Horror      |          213 | | Animation   |          355 | +-------------+--------------+ 4 rows in set (0.00 sec) 

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

Неправильное использование HAVING без GROUP BY или агрегатов

HAVING предназначен для фильтрации агрегированных результатов, а не сырых строк. Использование его без GROUP BY или агрегатной функции создает путаницу в SQL-движке и приводит к ошибке.

Неверный пример:

SELECT * FROM movie_theater HAVING movie_genre = 'Drama'; 

Ошибка (pgsql):

ERROR:  column "movie_theater.theater_id" must appear in the GROUP BY clause or be used in an aggregate function 

Чтобы отфильтровать сгруппированные строки, используйте WHERE:

SELECT * FROM movie_theater WHERE movie_genre = 'Drama'; 

А если вы хотите отфильтровать сгруппированные результаты:

SELECT movie_genre, AVG(guest_total) AS avg_guests FROM movie_theater GROUP BY movie_genre HAVING avg_guests > 100; 

Это фильтрует жанры, среднее количество посетителей в которых ниже 100.

Размещение ORDER ПО Перед GROUP BY

Последовательность SQL-операторов имеет значение. ORDER BY всегда должен идти после GROUP BY. Если они будут расположены в обратном порядке, SQL-движок выдаст ошибку синтаксиса или проигнорирует предполагаемое поведение.

Неправильный порядок предложений:

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

Ошибка:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'movie_theater order by movie_genre group by movie_genre' at line 1 

Правильный порядок пунктов:

SELECT movie_genre, AVG(guest_total) AS avg_guests FROM movie_theater GROUP BY movie_genre ORDER BY avg_guests DESC; 

Сначала эта группа, затем сортирует агрегированные результаты.

Часто задаваемые вопросы (FAQ)

1. В чем разница между GROUP BY и ORDER BY в SQL?

GROUP BY и ORDER BY служат разным целям в SQL:

• GROUP BY группирует строки, которые имеют одинаковые значения в указанных столбцах. Обычно он используется с агрегатными функциями, такими как SUM(), AVG(), COUNT() и т.д., чтобы выполнять вычисления над сгруппированными данными.
• ORDER BY используется для сортировки результирующего набора в возрастающем (ASC) или убывающем (DESC) порядке на основе одного или нескольких столбцов.

Пример:

-- GROUP BY groups data by genre and calculates average attendance SELECT movie_genre, AVG(guest_total) AS average_attendance FROM movie_theater GROUP BY movie_genre;  -- ORDER BY sorts the data by average attendance in descending order SELECT movie_genre, AVG(guest_total) AS average_attendance FROM movie_theater GROUP BY movie_genre ORDER BY average_attendance DESC; 

2. Можете ли вы использовать GROUP BY и ORDER BY вместе в SQL?

Да, вы можете использовать как GROUP BY, так и ORDER BY в одном запросе. Предложение GROUP BY группирует данные, а предложение ORDER BY сортирует сгруппированный набор результатов.

Пример:

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

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

3. Требует ли GROUP BY использования агрегатной функции в SQL?

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

4. Какой порядок сортировки по умолчанию у ORDER BY в SQL?

Порядок сортировки по умолчанию для ORDER BY является возрастающим (ASC). Вы можете явно указать убывающий порядок, используя DESC.

Примеры:

-- Ascending order (default) SELECT guest_total FROM movie_theater ORDER BY guest_total;  -- Descending order SELECT guest_total FROM movie_theater ORDER BY guest_total DESC; 

Эти запросы сортируют количество гостей в порядке возрастания и убывания соответственно.

5. Как вы группируете по нескольким столбцам в SQL?

Чтобы сгруппировать по более чем одному столбцу, перечислите каждый столбец в предложении GROUP BY, разделяя их запятыми. Это позволяет создавать агрегаты подразделов.

Пример:

SELECT movie_genre, date, COUNT(*) AS showings FROM movie_theater GROUP BY movie_genre, date ORDER BY date, movie_genre; 

Этот запрос подсчитывает, сколько показов каждого жанра произошло в каждую дату.

6. В чем разница между GROUP BY и DISTINCT в SQL?

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

Пример использования DISTINCT:

SELECT DISTINCT movie_name FROM movie_theater; 

Эквивалент с использованием GROUP BY:

SELECT movie_name FROM movie_theater GROUP BY movie_name; 

Для агрегаций, таких как общий доход или среднее количество гостей, используйте GROUP BY. Используйте DISTINCT только когда хотите уникальные записи без агрегации.

Заключение

В этой статье вы узнали основные навыки SQL сортировки и агрегации. Вы научились использовать ORDER BY для упорядочивания результатов и GROUP BY с агрегатными функциями, такими как SUM(), AVG() и COUNT(), чтобы обобщить данные в значимые группы. Мы рассмотрели критическое различие между фильтрацией строк с помощью WHERE перед группировкой и фильтрацией групп с помощью HAVING после агрегации. Комбинируя эти предложения, теперь вы можете создавать мощные, сложные запросы для преобразования сырых данных в четкие отчеты.

Чтобы продолжать развивать свои навыки, рассмотрите возможность изучения следующих учебников:

• Как использовать подстановочные знаки в SQL
• SQL запрос SELECT с функцией COUNT()
• Как использовать функции в SQL
• Как использовать математические выражения и агрегатные функции в SQL