Группировка данных в запросе
Подключение к базе данных
Для отработки запросов используйте тренажер
ссылка: http://mysqldognition.online/phpmyadmin/
логин: student
пароль: n123456789
На данный момент вы узнали, как выбирать, переформатировать, манипулировать, упорядочивать и суммировать данные из одной таблицы в базе данных. На этом уроке вы узнаете, как суммировать несколько подмножеств ваших данных в одном запросе. Способ сделать это состоит в том, чтобы включить предложение "GROUP BY" в ваши SQL-запросы.
GROUP BY для группировки результата запроса
Предложение GROUP BY легко включить в ваши запросы. На самом деле, это может быть слишком просто включить в запросы MySQL, потому что это может быть неправильно использовано в запросах MySQL, даже если сообщение об ошибке не отображается. Как следствие, я предлагаю вам проявлять разумную дозу осторожности каждый раз, когда вы используете предложение GROUP BY. К концу этого урока вы поймете почему. Однако при правильном использовании функция GROUP BY является одной из наиболее полезных и эффективных частей SQL-запроса, и как только вы освоитесь с ней, вы будете использовать ее очень часто.
Давайте вернемся к вопросу из упражнения 4 по MySQL. Как бы вы запросили среднюю оценку для каждого из 40 тестов в таблице отзывов? Как мы уже обсуждали, одним из очень неэффективных способов сделать это было бы написать 40 отдельных запросов, каждый из которых имел бы другое имя теста в условном предложении WHERE. Затем вы могли бы скопировать или расшифровать результаты всех 40 запросов в одном месте. Но это было бы не очень приятно. Вот как вы могли бы сделать то же самое, используя один запрос, содержащий предложение GROUP BY.
Этот запрос выведет среднюю оценку для каждого теста. Более технически, этот запрос даст указание MySQL усреднить все строки, которые имеют одинаковое значение в столбце test_name.
Обратите внимание, что я включил test_name в инструкцию SELECT. В качестве строгого эмпирического правила, если вы группируете по столбцу, вы также должны включить этот столбец в инструкцию SELECT. Если вы этого не сделаете, вы не будете знать, какой группе соответствует каждая строка вашего вывода. Вы можете формировать группы, используя производные значения, а также исходные столбцы. Чтобы проиллюстрировать это, давайте обратимся к другому вопросу: сколько тестов было выполнено в течение каждого месяца года?
Функция MONTH() вернет число, представляющее месяц ввода данных. Чтобы получить общее количество тестов, выполненных за каждый месяц, вы могли бы поместить функцию MONTH в предложение GROUP BY, в данном случае через псевдоним:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY Month;
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY test_name, Month;
MySQL позволяет вам использовать псевдонимы в предложении GROUP BY, но некоторые системы баз данных этого не делают. Если вы используете систему баз данных, которая не принимает псевдонимы в предложениях GROUP BY, вы все равно можете сгруппировать по производным полям, но вам придется дублировать вычисление для производного поля в предложении GROUP BY в дополнение к включению производного поля в предложение SELECT:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY test_name, MONTH(created_at);
Обратите внимание, что в первом случае первый блок строк имеет одинаковое название test_name, но разбит на отдельные месяцы. Во втором случае первый блок строк имеет один и тот же месяц, но разбит на отдельные тесты.
Различные серверы баз данных могут по умолчанию упорядочивать выходные данные определенным образом, но не стоит полагаться на это. Чтобы гарантировать, что вывод будет упорядочен так, как вы задумали, добавьте к сгруппированному запросу предложение ORDER BY, используя уже известный вам синтаксис, который вы практиковали:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY test_name, Month
ORDER BY test_name ASC, Month ASC;
Вопрос 1
Составьте запрос, который выводит количество питомцев dog_guid девочек и мальчиков (значения в колонке gender female и male) для каждой породы breed таблицы dogs, отсортированных по общему количеству собак в порядке убывания?
Некоторые серверы баз данных, включая MySQL, позволяют использовать числа вместо имен полей в полях GROUP BY или ORDER BY, чтобы сократить общую длину запросов. Я стараюсь избегать этого сокращенного метода написания запросов, поскольку считаю его сложным для устранения неполадок при написании сложных запросов с большим количеством полей, но он позволяет писать запросы быстрее. Чтобы использовать этот метод, присвойте каждому полю в операторе SELECT номер в соответствии с порядком появления поля в операторе SELECT. В следующем выражении:
test_name будет первым, месяц - вторым, а Num_Completed_Tests - третьим. Вы можете переписать запрос выше следующим образом:SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC;
Вопрос 2
Перепишите запрос, который вы написали в вопросе 1, так, чтобы в нем использовались только числа в полях GROUP BY и ORDER BY.
HAVING для условий внутри групп
Точно так же, как вы можете запрашивать подмножества строк с помощью предложения WHERE, вы можете запрашивать подмножества агрегированных групп с помощью предложения HAVING. Однако если выражение, следующее за предложением WHERE, должно быть применимо к каждой строке данных в столбце, то выражение, следующее за предложением HAVING, должно быть применимо или вычислимо к группе данных.
Если бы вы хотели проверить количество тестов, выполненных только в зимние праздничные месяцы - ноябрь и декабрь, вам бы потребовалось использовать предложение WHERE, поскольку месяц, в котором был выполнен тест, записан в каждой строке. Ваш запрос может выглядеть следующим образом:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
WHERE MONTH(created_at)=11 OR MONTH(created_at)=12
GROUP BY 1, 2
ORDER BY 3 DESC;
Если бы вы захотели вывести только те пары тестовых месяцев, в которых было не менее 20 записей, вы бы добавили предложение HAVING, поскольку условие о наличии не менее 20 записей имеет смысл и вычисляется только на уровне агрегированной группы:
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
WHERE MONTH(created_at)=11 OR MONTH(created_at)=12
GROUP BY 1, 2
HAVING COUNT(created_at)>=20
ORDER BY 3 DESC;
Вопрос 3
Переделайте запрос, составленный вами в вопросе 2, так, чтобы он (1) исключал пустые строки в поле breed_group и (2) исключал любые группы, в которых нет по крайней мере 1 000 разных идентификаторов dog_guid:
Мы рассмотрим несколько вопросов, которые могут вызвать затруднения при использовании GROUP BY в запросах, в следующем уроке, но эти вопросы станут более понятными после того, как вы убедитесь, что хорошо знакомы с базовой функциональностью предложений GROUP BY и HAVING.
Напишите запрос для таблицы dogs, который выводит среднее количество выполненных тестов total_tests_completed и средний интервал между тестами mean_iti_days для каждого типа породы breed_type, отсортированный по среднему количеству выполненных тестов в порядке убывания
Практика темы 5
Вопрос 4
Напишите запрос для таблицы dogs, который выводит среднее количество выполненных тестов total_tests_completed и средний интервал между тестами mean_iti_days для каждого типа породы breed_type, отсортированный по среднему количеству выполненных тестов в порядке убывания
Вопрос 5
Напишите запрос, который выводит среднюю разницу во времени в часах между start_time и end_time, которое пользователи тратили на выполнение каждого типа теста test_name. Запрос необходимо ограничить интервалом временной разницы от 0 до 6000 минут.
Вопрос 6
Напишите запрос, который выводит количество уникальных идентификаторов user_guid для каждой комбинации штата state и почтового индекса zip в Соединенных Штатах (значение в state US), отсортированных сначала по названию штата state в алфавитном порядке, а затем по общему количеству уникальных идентификаторов user_guid в порядке убывания.
Вопрос 7
Напишите запрос, который выведет общее количество уникальных идентификаторов user_guid для каждой комбинации штата state и почтового индекса zip в Соединенных Штатах (значение state US), в которых есть не менее 5 зарегистрированных пользователей, отсортированных сначала по названию штата в алфавитном порядке, а затем по количеству уникальных user_guid в порядке убывания.