DML. Агрегатные функции и GROUP BY

На данном этапе у нас уже есть хороший набор знаний для поиска данных в реляционных БД. Но до этого момента мы работали только с исходными записями, помещенными в таблицу.

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

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

PostgreSQL (как и любая другая реляционная СУБД) предоставляет ряд агрегатных функций. Они достаточно разнообразны, но мы рассмотрим только несколько наиболее популярных и востребованных в типовых задачах.

Для большей наглядности предлагаю начать с пары примеров:

select count(*) from passenger;

Данный запрос вернет таблицу с единственной ячейкой. В ней будет содержаться число записей в таблице passenger.

select count(*) from passenger where male = true;

Данный запрос вернет число мужчин в таблице. Таким образом, сначала запрос сделает выборку по WHERE, а лишь потом – произведет агрегацию данных.

Также стоит отметить, что «*», в данном случае, не относится к SELECT, она, скорее, является специфическим параметром функции. Альтернативу рассмотрим ниже.

Теперь предлагаю ознакомиться с наиболее популярными агрегатными функциями:

·      count(*).  Как уже было сказано выше – производит подсчет строк, попавших в выборку;

·      count(выражение). Модификация count(*) с дополнительной фильтрацией на null. Под выражением, как обычно, может подразумеваться колонка таблицы, вызов функции и т.д:

select count(last_purchase) from passenger;

По сути, выражение можно рассматривать как дополнительное WHERE-условие вида «выражение is not null». В целом, для улучшения производительности рекомендую использовать явное WHERE-условие, вместо параметра count(), если это возможно;

·      avg(выражение). Для выражения с числовым результатом возвращает среднее значение результата. Если выражение состоит из одной колонки (как в примере выше) – возьмет из выборки все значения в этой колонке и посчитает их среднее значение. NULL-значения будут проигнорированы – т.е. не просто приняты за 0 (нуль), а полностью исключены из расчета;

·      sum(выражение). Считает сумму значений. Все остальное – как и в функции выше;

·      min(выражение) и max(выражение). Находит меньшее и большее значения в выборке для заданного выражения. Опять же, кроме NULL.

С более подробным описанием и другими агрегатными функциями можно познакомиться по ссылке ниже. Рекомендую пока ограничиться первой таблицей: https://postgrespro.ru/docs/postgrespro/10/functions-aggregate

Оператор GROUP BY

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

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

Итак, группировка:

select count(*), male from passenger group by male;

Данный запрос возвращает две строки (да и в таблице уже 2 столбца): число женщин и число мужчин.

Таким образом, в рамках запроса была получена выборка (WHERE нет => вся таблица), разбита на части по совпадающим значениям поля male (group by male) и далее была произведена агрегация для каждой из полученных групп: male = false и male = true соответственно.

Как и в случае с ORDER BY, нам нет необходимости включать в SELECT те же колонки, что и в GROUP BY:

select count(*) from passenger group by male;

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

Однако ВСЕ колонки, которые указаны в SELECT, за исключением переданных параметром агрегатной функции, должны быть указаны в GROUP BY – иначе СУБД не сможет произвести агрегацию и запрос упадет с ошибкой.

Также стоит отметить, что в одном запросе можно вызывать несколько агрегатных функций, в этом нет никаких ограничений:

select avg(id), count(*), male from passenger group by male;

Если колонок в GROUP BY несколько – они записываются через запятую. Порядок значения не имеет.

И, наконец, сортировка может быть применена только к одному из выражений из group by или к колонке, в которую будет записан результат агрегатной функции. Причины те же, что и в случае с SELECT.

Бонусом, запрос с демонстрацией расположения блоков с учетом GROUP BY:

select count(*), male from passenger where last_purchase is not null group by male order by male limit 1 offset 0;

С теорией на сегодня все!

Переходим к практике:

Задача

Верните в одном запросе: количество совершеннолетних пассажиров, наибольшее и наименьшее значение последней покупки. Сделайте группировку по полу 

Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)

Канал: https://t.me/ViamSupervadetVadens

Мой тг: https://t.me/ironicMotherfucker

Дорогу осилит идущий!