Агрегация данных

Подключение к базе данных

Для отработки запросов используйте тренажер

ссылка: http://mysqldognition.online/phpmyadmin/
логин: student
пароль: n123456789

В этом уроке мы узнаем, как использовать SQL для выполнения вычислений, которые суммируют ваши данные, без необходимости выводить все необработанные строки или записи. Эти расчеты послужат строительными блоками для запросов, которые будут отвечать на наши бизнес-вопросы о том, как улучшить показатели завершения тестов распознавания.

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

Вы будете использовать COUNT и SUM очень часто.

COUNT - это единственная агрегатная функция, которая может работать с переменными любого типа. Остальные четыре агрегатные функции подходят только для числовых данных.

Все агрегатные функции требуют, чтобы вы ввели либо имя столбца, либо "*" в круглых скобках после слова функции.

Давайте начнем с изучения функции COUNT.

COUNT для подсчета строк

Мы можем запросить, сколько строк содержится в выходных данных нашего запроса, поэтому мы можем сравнить результаты функции COUNT с результатами нашей функции SELECT. Если ты убежишь:

SELECT breed
FROM dogs

Получено 35050 строк, что означает наличие 350 строк в выходных данных запроса (хотя, конечно, мы ограничили отображение только 1000 строк за раз)

SELECT COUNT(breed)
FROM dogs

COUNT сообщает, сколько всего строк содержится в столбце breed. COUNT должен выдать вам те же выходные данные, что и Jupiter, без отображения фактических строк данных, которые агрегируются.

Вы можете использовать DISTINCT (о котором вы узнали в упражнении MySQL 3) с COUNT для подсчета всех уникальных значений в столбце, но оно должно быть заключено в круглые скобки непосредственно перед подсчитываемым столбцом. Например, чтобы подсчитать количество различных названий пород, содержащихся во всех записях в столбце порода, вы могли бы запросить:

SELECT COUNT(DISTINCT breed)
  FROM dogs

Что, если бы вы захотели узнать, сколько отдельных собак успешно прошли хотя бы один тест?

Поскольку каждая строка в таблице complete_tests представляет завершенный тест, и ранее мы узнали, что в столбце created_at таблицы complete_tests нет нулевых значений, любой ненулевой Dog_Guid в таблице complete_tests завершит по крайней мере один тест. Когда столбец заключен в круглые скобки, значения null автоматически игнорируются. Следовательно, вы могли бы использовать:

SELECT COUNT(DISTINCT Dog_Guid)
  FROM complete_tests

Вопрос 1

Сколько питомцев (dog_guid) из таблицы complete_tests прошли тесты после 1 марта 2014 года (created_at)?

Вы можете использовать "*" в круглых скобках функции ПОДСЧЕТА, чтобы подсчитать, сколько строк содержится во всей таблице (или подтаблице). Однако есть два фундаментальных различия между COUNT(*) и COUNT(имя_колонны).

Первое отличие заключается в том, что вы не можете использовать DISTINCT с COUNT(*).

Вопрос 2

Сколько всего строк в таблице dogs?

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

Когда столбец включается в функцию подсчета, нулевые значения игнорируются при подсчете. Когда звездочка включена в функцию подсчета, в счетчик включаются нулевые значения.

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

Вопрос 3

Сколько всего исключенных питомцев в таблице dogs (значение в колонке exclude 1)?

2. The SUM Function

Тот факт, что вывод из данного запроса:

SELECT COUNT(exclude) 
FROM dogs

был намного меньше, чем:

SELECT COUNT(*)
FROM dogs

предполагает, что в столбце exclude должно быть много нулевых значений. Удобно, что мы можем объединить функцию SUM с ISNULL, чтобы точно подсчитать, сколько существует нулевых значений.

ISNULL - это логическая функция, которая возвращает 1 для каждой строки, имеющей нулевое значение в указанном столбце, и 0 для всего остального. Если мы суммируем количество единиц, выведенных ISNULL(исключить), то мы должны получить общее количество нулевых значений в столбце. Вот как выглядел бы этот запрос:

SELECT SUM(ISNULL(exclude))
FROM dogs

Может возникнуть соблазн использовать SQL как калькулятор и не использовать оператор SELECT, но вы быстро увидите, что это не работает.

Каждый SQL-запрос, который извлекает данные из базы данных, должен содержать инструкцию SELECT.

3. The AVG, MIN, and MAX Functions

Значения AVG, MIN и MAX работают очень похоже на SUM.

Во время теста на распознавание клиентам задавали вопрос: "Насколько неожиданным был выбор [клички вашей собаки]?” после завершения теста. Пользователи могли выбрать любое число от 1 (неудивительно) до 9 (очень удивительно). Мы могли бы получить среднюю, минимальную и максимальную оценку, которую клиенты дали этому вопросу, после завершения "Игры в зрительный контакт" со следующим запросом:

SELECT test_name, 
AVG(rating) AS AVG_Rating, 
MIN(rating) AS MIN_Rating, 
MAX(rating) AS MAX_Rating
FROM reviews
WHERE test_name="Eye Contact Game";

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

Что, если бы вы хотели получить среднюю оценку для каждого из 40 тестов в таблице отзывов? Один из способов сделать это с помощью инструментов, которые вы уже знаете, - написать 40 отдельных запросов, подобных тем, которые вы написали выше, для каждого теста, а затем скопировать или переписать результаты в отдельную таблицу в другой программе, например Excel, чтобы собрать все результаты в одном месте. Это было бы очень утомительным и отнимающим много времени упражнением. К счастью, существует очень простой способ получить желаемые результаты в рамках одного запроса. Это то, что мы научимся делать в упражнении 5 для MySQL. Однако важно, чтобы вы чувствовали себя комфортно с синтаксисом, который мы изучили до сих пор, прежде чем мы начнем использовать преимущества этой функциональности. Практика - это лучший способ почувствовать себя комфортно!

Вопрос 4

Выведите средний, минимальный и максимальный рейтинг raiting из таблицы reviews, который получали питомцы за испытание "Memory versus Pointing"?

TIMESTAMPDIFF для подсчета разницы во времени

SELECT TIMESTAMPDIFF(minute, start_time, end_time) AS Duration
FROM exam_answers
LIMIT 100;

Если вы изучите свои выходные данные, то обнаружите, что некоторые из ваших расчетных длительностей кажутся равными "0". В некоторых случаях вы увидите много записей из одного и того же DOG_ID с одинаковым временем начала и окончания. Это должно быть невозможно. Записи такого типа, вероятно, представляют собой тесты, проводимые командой распознавания, а не реальные данные клиентов. Однако в других случаях в столбец продолжительности вводится "0", даже если start_time и end_time разные. Это связано с тем, что мы проинструктировали функцию выводить разницу во времени в минутах; если вы не измените свои настройки, он будет выводить "0" для любых временных разниц, меньших целого числа 1. Если вы измените свою функцию на вывод разницы во времени в секундах, продолжительность в большинстве этих столбцов будет иметь ненулевое число.

SELECT dog_guid, start_time, end_time, TIMESTAMPDIFF(minute, start_time, end_time) AS Duration
FROM exam_answers
LIMIT 2000;

Вопрос 4

Выведите среднюю разницу по времени между start_time и end_time, которое понадобилось для выполнения испытания "Treat Warm-Up" из таблицы exam_answers?

Практика темы 4

Вопрос 5

Сколько всего тестов test_name в таблице exam_answers. Вывести ответ без дубликатов?

Вопрос 6

Выведите минимальную и максимальную продолжительность испытания между start_time и end_time из таблицы exam_answers?

Вопрос 7

Существуют ли записи с отрицательной продолжительностью между start_time и end_time из таблицыexam_answers? Выведите их количество.

Вопрос 8

Выведите все данные из таблицы exam_answers с отрицательной продолжительностью между start_time и end_time?

Вопрос 9

Выведите среднее время прохождения теста для всех пользователей из таблицы exam_answers, не включая тех, у кого отрицательная разница по времени между start_time и end_time