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

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

В стандарте SQL1 определены следующие правила обработки значений NULL в агрегатных функциях:

1) если какие-либо из значений, содержащихся в столбце, равны NULL, то при вычислении результата функции они исключаются;

2) если все значения в столбце равны NULL или в столбце нет значений (т.е. столбец пустой), то функции SUM( ), AVG( ), MIN( ), MAX( ) возвращают значение NULL; функция COUNT( ) возвращает нулевое значение;

3) функция COUNT(*) подсчитывает количество строк и не зависит от наличия или отсутствия в столбце значений NULL; если строк в таблице нет, то функция возвращает нулевое значение.

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

Удаление повторяющихся строк (distinct)

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

Предложения group by и having (запросы с группировкой)

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

SELECTAVG(SUMD)FROMPERSON

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

Наряду с такими «интегральными» итоговыми запросами большой интерес представляют итоговые запросы, в которых агрегатные функции применяются к определенным группам строк, а не ко всей таблице. Эту возможность предоставляет предложение 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