Агрегатные функции в SQL: объяснение с примерами запросов и задачами.

Оператор GROUP BY

Иногда требуется узнать информацию не о самих объектах, а об определенных группах, которые они образуют. Для этого используется оператор GROUP BY и агрегатные функции.

Общая структура запроса с GROUP BY

MySQL

SELECT [константы, агрегатные_функции, поля_группировки] 
FROM имя_таблицы 
GROUP BY поля_группировки;

Пример использования GROUP BY

Для демонстрации работы оператора GROUP BY выведем общую сумму потраченных средств, сгруппированных по членам семьи.

MySQL

SELECT family_member, SUM(unit_price * amount) FROM Payments 
GROUP BY family_member;

Ниже представлен набор данных, находящихся в таблице Payments:

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

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

Как видно, образовались группы записей, объединённых одним family_member. После этого мы можем внутри каждой из этих групп применить формулу суммы, которая умножит количество товара на его стоимость, а потом просуммирует все получившиеся значения:

Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу.

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

Агрегатная функция выполняет вычисление на наборе значений и возвращает одиночное значение.

Агрегатные функции применяются для значений, не равных NULL. Исключением является функция COUNT().

Для фильтрации строк по значениям агрегатных функций используется оператор HAVING.

Общая структура запроса с оператором HAVING

MySQL

SELECT [константы, агрегатные_функции, поля_группировки] 
FROM имя_таблицы 
GROUP BY поля_группировки
HAVING условие_на_ограничение_строк_после_группировки

Отличие HAVING от WHERE

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

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

Выведем общую сумму, потраченную на покупки, для каждого члена семьи, где общая сумма покупки меньше, чем 5000 рублей:

MySQL

SELECT family_member, SUM(unit_price * amount) AS sum 
FROM Payments GROUP BY family_member
HAVING sum < 5000;

Задача:

Найти максимальную задолженность в каждом банке.

Для чего тут оконные функции? Можно же просто написать:

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

Собрать дэшборд, в котором содержится информация о максимальной задолженности в каждом банке, а также средний размер процентной ставки в каждом банке в зависимости от сегмента, плюс еще количество договоров всего всем банкам (в голове рисуются множественные джойны из подзапросов и как-то сразу тяжело на душе). Однако, как я говорил выше, в одном select можно использовать много OVER, а также еще один прекрасный факт: набор строк в окне, связывается с текущей строкой, а не с группой агрегированных. Таким образом:

На примере AVG(procent_RATE) OVER (partition BY TB, segment) подробнее:

• Мы применяем AVG – агрегатную функцию по подсчету среднего значения к столбцу procent_RATE.
• Затем предложением OVER определяем, что будем работать с некоторым набором строк. По умолчанию, если указать OVER() с пустыми строками, то этот набор строк равен всей таблице.
• Предложением partition BY выделяем разделы в наборе строк по заданному условию, в нашем случае, в разбивке на Территориальные банки и Сегмент.
• В итоге, к каждой строке базовой таблицы применится функция по подсчету среднего из набора строк, разбитых на разделы (по Территориальным Банкам и Сегменту).

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

Базовый набор данных: банки, отделы и количество ревизий.

Сами ранжирующие функции:

ROW_number – нумерует строки в результирующем наборе.

RANK -присваивает ранг для каждой строки, если найдутся одинаковые значения, то следующий ранг присваивается с пропуском.

DENSE_RANK -присваивает ранг для каждой строки, если найдутся одинаковые значения, то следующий ранг присваивается без пропуска.

NTILE – помогает разделить результирующий набор на группы.

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

ROW_number – пронумеровал столбцы в порядке убывания количества ревизий.

RANK – проранжировал отделы во всех банках в порядке убывания количества ревизий, но как только встретились одинаковые значения (количество ревизий 95), функция присвоила им ранг 4, а следующее значение получило ранг 6.

DENSE_RANK – аналогично RANK, но как только встретились одинаковые значения, следующее значение получило ранг 5.

NTILE – функция помогла разбить таблицу на 3 группы (указал в аргументе). Так как в таблице 18 значений, в каждую группу попало по 6.

Задача:

Найти второй отдел во всех банках по количеству ревизий.

Можно, конечно, воспользоваться чем-то вроде:

Но если речь идет не про второй отдел, а про трети? .. уже сложнее. Действительно, никто не списывает со счетов offset, но в этой статье говорится об оконных функциях, так почему бы не написать так:

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

Получаем:

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

LAG — смещение назад.

LEAD — смещение вперед.

FIRST_VALUE — найти первое значение набора данных.

LAST_VALUE — найти последнее значение набора данных.

LAG и LEAD имеют следующие аргументы:

• Столбец, значение которого необходимо вернуть
• На сколько строк выполнить смешение (дефолт =1)
• Что вставить, если вернулся NULL

Как обычно, на практике проще:

Базовый набор данных, содержит id задачи, события внутри нее и их дату:

Применяя конструкцию:

Получаем набор данных, который хоть сейчас в graphviz (нет).