SQL GROUP BY и агрегатные функции: COUNT, SUM, AVG для тестировщика

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

Чему научишься за этот урок:
— называть основные агрегатные функции: COUNT, SUM, AVG, MAX, MIN;
— объяснять, что делает GROUP BY и чем он отличается от запроса без группировки;
— писать запрос, который считает агрегат ОТДЕЛЬНО для каждой группы;
— проверять SQL-запросом цифру, которую продукт показывает в интерфейсе.

Содержание

Агрегатные функции

Агрегатные функции — это функции, которые считают одно итоговое значение по набору строк, а не возвращают сами строки. Основные:

COUNT() -> количество строк
SUM()   -> сумма значений столбца
AVG()   -> среднее значение столбца
MAX()   -> максимальное значение
MIN()   -> минимальное значение

Например, SELECT COUNT(*) FROM enrollments; посчитает ОДНО общее число — сколько всего строк (записей на курсы) в таблице enrollments, без разбивки по курсам.

Так же со средним: в таблице courses(id, title, price) запрос SELECT AVG(price) FROM courses; вернёт одно число — среднюю цену по всем курсам.

GROUP BY — считаем отдельно по каждой группе

GROUP BY группирует строки по значению столбца, и агрегатная функция считается ОТДЕЛЬНО для каждой группы, а не для всей таблицы сразу. Сравним два запроса к одной и той же таблице enrollments(id, user_id, course_id):

Без GROUP BY:
SELECT COUNT(*) FROM enrollments;
-> одно общее число записей по всей таблице

С GROUP BY:
SELECT course_id, COUNT(*) FROM enrollments GROUP BY course_id;
-> по одной строке РЕЗУЛЬТАТА на каждый course_id,
   и в каждой строке - число записей именно на этот курс

То есть первый запрос скажет «всего 42 записи на курсы», а второй — «на курс 5 записано 30 человек, на курс 7 — 12 человек» (по строке на каждый курс отдельно).

Практическая польза: проверка цифры продукта

На странице курса в интерфейсе продукта может быть написано «На курсе 42 студента». Тестировщик может ПРОВЕРИТЬ это число SQL-запросом: SELECT COUNT(*) FROM enrollments WHERE course_id = 5; — и сравнить результат с тем, что показывает интерфейс. Если числа не совпадают — это баг: либо ошибка в расчёте на бэкенде, либо ошибка в отображении на фронтенде.

Итог:
— агрегатные функции COUNT, SUM, AVG, MAX, MIN считают одно итоговое значение по набору строк;
— GROUP BY группирует строки по столбцу, и агрегат считается ОТДЕЛЬНО для каждой группы;
— без GROUP BY агрегат даёт одно общее число по всей таблице;
— тестировщик может SQL-запросом проверить любую цифру, которую продукт показывает в интерфейсе.

Контрольный вопрос. Что делает агрегатная функция COUNT()?

АСчитает количество строк в наборе данных

БСортирует строки по возрастанию

ВУдаляет повторяющиеся строки

Подсказка: Название функции прямо намекает на то, что она делает — посчитай.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Что делает оператор GROUP BY?

АОграничивает количество возвращаемых строк

БГруппирует строки по значению столбца

ВСоединяет две таблицы по внешнему ключу

Подсказка: Ключевое слово — «группирует»: подумай, что происходит с агрегатной функцией внутри каждой группы.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Что вернёт запрос SELECT COUNT(*) FROM enrollments; (без GROUP BY)?

АОдно общее число записей по всей таблице enrollments

БСписок всех курсов без повторов

ВПо одной строке на каждого пользователя с его количеством записей

Подсказка: В уроке прямо сравниваются запрос без GROUP BY и запрос с ним — вспомни разницу.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Какая функция посчитает среднюю цену по столбцу price?

АCOUNT(price)

БMAX(price)

ВAVG(price)

Подсказка: Нужна именно функция среднего значения — посмотри список агрегатных функций урока.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Запрос SELECT course_id, COUNT(*) FROM enrollments GROUP BY course_id; выполнили на таблице enrollments. Какие утверждения про результат верны (выбери все подходящие)?

Выберите все верные варианты.

АКурсы, на которые никто не записался, тоже попадут в результат со значением 0

БРезультат содержит по одной строке на каждый уникальный course_id

ВВ каждой строке результата — число записей именно на этот курс, а не общее по всей таблице

ГСтроки результата идут по убыванию COUNT(*): самый популярный курс окажется сверху

ДРезультат — это всегда ровно одна строка с одним общим числом

ЕБез GROUP BY этот же SELECT COUNT(*) дал бы одно общее число вместо разбивки по курсам

Подсказка: Сверь каждое утверждение с примером из урока про GROUP BY course_id — одно из четырёх описывает поведение БЕЗ группировки.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Как называется агрегатная функция, которая находит МАКСИМАЛЬНОЕ значение столбца? Ответь одним словом по-английски, как в уроке.

Подсказка: Посмотри список агрегатных функций урока — какая из них про максимум.

Онлайн-проверка ответа появится позже

Задание. В интерфейсе продукта на странице курса с id=5 написано «На курсе 30 студентов». Напиши SQL-запрос к таблице enrollments(id, user_id, course_id), который проверит это число.

Подсказка: Нужна агрегатная функция, которая считает строки, и условие, которое оставит только записи нужного курса.

✅ Готово, если: программа запускается без ошибок и выводит то, что просят в задании.

Онлайн-проверка ответа появится позже

Задание. Выполнили запрос SELECT course_id, COUNT(*) FROM enrollments GROUP BY course_id; и получили две строки результата: course_id=5 с числом 30, course_id=7 с числом 12. Сколько ВСЕГО строк в таблице enrollments (если это единственные два курса)? Ответь числом.

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

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Онлайн-проверка ответа появится позже

Задание. Объясни в 2-3 предложениях: почему запрос SELECT COUNT(*) FROM enrollments; и запрос SELECT course_id, COUNT(*) FROM enrollments GROUP BY course_id; дают разные по смыслу результаты, хотя оба используют COUNT(*) на одной и той же таблице?

Подсказка: Вспомни, что делает GROUP BY с набором строк ДО того, как к ним применяется агрегатная функция.

✅ Готово, если: программа запускается без ошибок и выводит то, что просят в задании.

Онлайн-проверка ответа появится позже

Задание. Тестировщик видит в интерфейсе продукта на странице каталога курсов надпись «Средняя цена курса: 4500 рублей». Опиши план проверки: (а) какой SQL-запрос к таблице courses(id, title, price) выполнить, чтобы проверить эту цифру; (б) что делать, если результат запроса не совпадёт с 4500 — на что в первую очередь обратить внимание при расследовании расхождения (например, может ли расхождение объясняться округлением или курсами без указанной цены).

Подсказка: AVG уже был в списке агрегатных функций урока; расхождение не всегда значит баг — подумай про округление и про курсы без цены.

✅ Готово, если: программа запускается без ошибок и выводит то, что просят в задании.

Онлайн-проверка ответа появится позже

Что дальше

Теперь вы умеете считать агрегаты по группам и проверять ими цифры продукта. Дальше — новые приёмы работы с данными.

← Назад · ↑ В начало урока · ⌂ В начало курса · Вперёд →