10.1.3. Агрегатные функции и значения null

Агрегатные функции (кроме COUNT(*)) в качестве аргумента принимают столбец значений и возвращают в качестве результата одно значение. Если в столбце значений встречаются значения NULL, то они игнорируются агрегатными функциями.

В стандарте SQL1 определены следующие правила обработки значений NULL в агрегатных функциях: 1) если какие-либо из значений, содержащихся в столбце, равны NULL, то при вычислении результата функции они исключаются; 2) если все значения в столбце равны NULL или в столбце нет значений (т.е. столбец пустой), то функции SUM(), AVG(), MIN(), MAX() возвращают значение NULL; функция COUNT() возвращает нулевое значение; 3) функция COUNT(*) подсчитывает количество строк и не зависит от наличия или отсутствия в столбце значений NULL; если строк в таблице нет, то функция возвращает нулевое значение.

Прежде чем полагаться на эти правила, следует проверить свою СУБД, поскольку коммерческие СУБД могут выдавать результаты, отличающиеся от стандарта.

10.2. Запросы с группировкой

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

SELECT AVG(SUMD) FROM PERSON

вычисляет среднедушевой доход жителя.

Наряду с такими "интегральными" итоговыми запросами большой интерес представляют итоговые запросы, в которых агрегатные функции применяются к определенным группам строк, а не ко всей таблице. Эту возможность предоставляет предложение GROUP BY оператора SELECT. Например, определить среднедушевой доход жителей каждой квартиры позволяет такой запрос:

SELECT ADR,AVG(SUMD) FROM PERSON GROUP BY ADR

Этот запрос возвращает несколько итоговых строк - по одной строке для каждой квартиры. Логика выполнения запроса следующая: 1. Сведения о жителях в таблице PERSON делятся на группы, по одной группе на каждую квартиру. В каждой группе все жители имеют одно и то же значение в столбце ADR. 2. Для каждой группы вычисляется среднее значение столбца SUMD по всем строкам, входящим в группу, и генерируется одна итоговая строка в таблице результатов. Эта строка содержит значение столбца ADR для группы и средний общий доход для данной группы.

Запрос, включающий в себя предложение GROUP BY, называется запросом с группировкой. Столбцы, указанные в этом предложении, называются столбцами группировки, поскольку именно они определяют, по какому признаку строки объединяются в группы. Ниже приведен ряд запросов с группировкой:

1) сколько жителей зарегистрировано в каждой квартире?

SELECT ADR,COUNT(*) FROM PERSON GROUP BY ADR

2) сколько источников дохода имеет каждый житель с ненулевым общим доходом?

SELECT NOM,COUNT(ID) FROM HAVE_D GROUP BY NOM

3) сколько различных источников дохода имеют жители каждой квартиры?

SELECT ADR,COUNT(DISTINCT ID) FROM PERSON,HAVE_D WHERE PERSON.NOM=HAVE_D.NOM GROUP BY ADR

Предложение GROUP BY видоизменяет действие агрегатных функций. Это предложение указывает, что результаты запроса следует разделить на группы, применить агрегатную функцию по отдельности к каждой группе и получить для каждой группы одну строку в таблице результатов.

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

SELECT ADR,FIO,SUM(SUMD) FROM PERSON GROUP BY ADR,FIO