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

АГРЕГАТНЫЕ ФУНКЦИИ В СПИСКЕ ВОЗВРАЩАЕМЫХ СТОЛБЦОВ

ВЫЧИСЛЕНИЕ КОЛИЧЕСТВА ЗНАЧЕНИЙ В СТОЛБЦЕ (COUNT)

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

Мысленно трудно представить запрос вроде "подсчитать, сколько стоимостей заказов" или "подсчитать, сколько номеров заказов"; гораздо проще представить запрос "подсчитать, сколько заказов". Поэтому в SQL была введена специальная агрегатная функция count (*), которая подсчитывает строки, а не значения данных. Ниже приведен предыдущий запрос, переписанный с использованием этой функции:

Если использовать count (*) в качестве функции подсчета строк, то запрос становится более удобочитаемым. На практике для подсчета строк всегда применяется функция count (*), а не count ().

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

Агрегатные функции sum (), avg (), min (), мах () и count () в качестве аргумента принимают столбец значений и возвращают в качестве результата одно значение. А что происходит, когда в столбце содержится одно или несколько значений null? В стандарте ANSI/ISO сказано, что значения null агрегатными функциями игнорируются.

Следующий запрос показывает, как агрегатная функция count () игнорирует все значения null, содержащиеся в столбце:

В таблице salesreps содержится десять строк, поэтому функция count (*) возвращает число 10. В столбце sales содержится десять значений, причем ни одно из них не равно null, поэтому функция count (sale s) также возвращает число 10. А вот в столбце quota содержится одно значение null —- для служащего, принятого совсем недавно. Функция count (quota) игнорирует это значение и возвращает число 9. Именно из-за таких расхождений вместо функции count () для подсчета строк почти всегда используется функция count (*). Исключение составляют случаи, когда необходимо не учитывать строки, содержащие значения null в определенном столбце.

Игнорирование значений null не оказывает влияния на результаты, возвращаемые агрегатными функциями min () и мах (). Однако оно может привести к проблемам при использовании функций sum () и avg (), что иллюстрирует следующий запрос:

Можно ожидать, что выражения

SUM(SALES) - SUM (QUOTA) SUM (SALES-QUOTA)

вернут одинаковые результаты, однако пример показывает, что так не происходит. И снова причиной является строка со значением null в столбце quota. Выражение

SUM(SALES)

вычисляет сумму продаж для всех десяти служащих, а выражение

SUM (QUOTA)

вычисляет сумму только девяти значений и не учитывает значение null Следующее выражение вычисляет разницу между ними:

SUM(SALES) – SUM(QUOTA)

В то же время выражение

SUM(SALES-QUOTA)

принимает в качестве аргументов только девять значений, которые не равны null. В строке, где значение планового объема продаж равно null, оператор вычитания возвращает значение null, которое функция sum () игнорирует. Таким образом, из результатов этого выражения исключаются фактические продажи служащего, для которого еще не установлен план, вошедшие в результаты предыдущего выражения.

Какой же ответ является "правильным"? Оба! Первое выражение вычисляет именно то, что и означает, т.е. "сумма по sales минус сумма по QUOTA. И второе выражение также вычисляет именно то, что оно означает, "сумма (sales — quota)". Однако при наличии значений null результаты выражений отличаются.

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

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

• если все значения в столбце равны null, то функции sum (), avg min () и мах () возвращают значение null; функция count () возврат ноль;

• если в столбце нет значений (т.е. столбец пустой), то функции sum (), avg (), min () и мах () возвращают значение null; функция count возвращает ноль;

• функция count (*) подсчитывает количество строк и не зависит наличия или отсутствия в столбце значений null; если строк в таблице нет, эта функция возвращает ноль.

Дата добавления: 2014-01-11