Агрегирование данных Групповые функции

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

COUNT

Функция возвращает количество записей в группе. Возможно три варианта использования функции COUNT:

COUNT(*) – подсчет количества записей в группе;

COUNT(поле) – подсчет количества отличных от NULL значений в указанном поле записей группы;

COUNT(DISTINCT поле) – подсчет количества уникальных отличных от NULL значений в указанном поле записей группы.

Примеры использования функции COUNT:

-- подсчет количества строк в таблице Students

SELECT COUNT(*) FROM Student s;

-- подсчет количества всех различных фамилий студентов

SELECT COUNT(DISTINCT L_name) FROM Students;

SUM

Функция SUM возвращает суммарное значение для группы.

-- подсчет суммарного значения риска

SELECT SUM(Risk) FROM Hobby;

MAX

Функция MAX возвращает максимальное значение для группы.

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

SELECT MAX(B_Data) FROM Students;

MIN

Функция MIN возвращает минимальное значение для группы.

• поиск самого “старого” студента

SELECT MIN(B_Data) FROM Students;

AVG

Функция AVG возвращает среднее значение для группы.

-- подсчет средней степени риска для хобби, названия которых заканчиваются на «ов»

SELECT AVG(Risk) FROM Hobbys WHERE Hobby_name Like ‘%ов’;

Использование параметров group by и having

В предыдущих примерах в качестве группы рассматривался весь набор записей, полученный в результате выполнения запроса. При помощи параметра GROUP BY оператора SELECT можно указать способ разбиения полученного в результате выполнения запроса набора записей на группы. В параметре GROUP BY задается столбец (или столбцы), по значениям которого будет производиться группировка. При выполнении оператора SELECT, в котором присутствует параметр GROUP BY, СУБД проанализирует значение указанного столбца во всех строках, отобранных в результате выполнения запроса. Все строки, где значение указанного в параметре GROUP BY столбца одно и тоже, попадут в одну группу. После этого для каждой из групп будет вычислена указанная в параметре SELECT групповая функция. Например:

-- вывод номеров групп и количества студентов в каждой группе

SELECT N_gr, COUNT(N_gr) as количество_студентов

FROM Students

GROUP BY N_gr

ORDER BY N_gr DESC;

В данном запросе СУБД сначала выделит группы записей, относящиеся к разным группам (в зависимости от значения столбца N_gr) и для каждой получившейся группы посчитает количество записей.

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

-- отбор групп, в которых количество студентов более 12.

SELECT N_gr, COUNT(N_gr) as количество_студентов

FROM Students

GROUP BY N_gr

HAVING COUNT(N_gr) > 12;