Шаг 33 - SELECT - Агрегаты и старый знакомый NILL!

А, вот интересно, а что будет происходить с функциями типа AVG(), MIN(), MAX(), SUM(), COUNT(), если значение столбца будет содержать значение нашего доброго старого знакомого - NULL? По правилам ANSI/ISO сказано, что "агрегатные функции игнорируют значение NULL"! Вот если честно, ну достал этот NULL, просто сил нет! :) Итак давайте проверим, на примере, что же будет происходить. Дадим вот такой запрос:

SELECT COUNT(*), COUNT(SALES), COUNT(QUOTA)
FROM SALESREPS
/

Поучаем:

SQL> SELECT COUNT(*), COUNT(SALES), COUNT(QUOTA)
  2  FROM SALESREPS
  3  /

  COUNT(*) COUNT(SALES) COUNT(QUOTA)
---------- ------------ ------------
        11           11           10

Странный какой то результат? В чем тут вопрос. Таблица вроде одна, а вот значения в запросе разные. А все дело в том что, одно из полей QUOTA - содержит NULL. От сюда и вся не разбериха. Функция COUNT вида COUNT(поле), при работе, как и было сказано, игнорирует значение NULL, а COUNT(*) просто подсчитывает общее число строк. Ей все равно есть там NULL или нет! :) Как в том мультике - "Он нас всех посчитал!" :) Просто запомните вышесказанное и не будете делать в дальнейшем ошибок! А, вот функции MIN(), MAX() - особо не искажают результат при наличии NULL, так как так же его игнорируют. Но AVG(), SUM() - может при наличии NULL немного ввести вас в заблуждение! Например, посмотрите на следующий запрос:

SELECT SUM(SALES), SUM(QUOTA), (SUM(SALES) - SUM(QUOTA)), (SUM(SALES - QUOTA))
FROM SALESREPS
/

Получаем:

SQL> SELECT SUM(SALES), SUM(QUOTA), (SUM(SALES) - SUM(QUOTA)), (SUM(SALES - QUOTA))
  2  FROM SALESREPS
  3  /

SUM(SALES) SUM(QUOTA) (SUM(SALES)-SUM(QUOTA)) (SUM(SALES-QUOTA))
---------- ---------- ----------------------- ------------------
  3279,574       3100                 179,574            103,589

С SUM(SALES), SUM(QUOTA) все вроде бы ясно, но вот (SUM(SALES)-SUM(QUOTA)) и (SUM(SALES-QUOTA)) как-то странно выглядит. По закону математики собственно, оба выражения должны были вернуть одинаковые результаты, но на поверку это не так! Почему? Давайте подумаем. Выражение SUM(SALES-QUOTA) принимает в качестве аргумента только 10-ть значений, которые не равны NULL. В следствии того, что одно из полей QUOTA, содержит значение NULL. То выражение SUM(значение столбца - NULL) возвращает NULL! SUM его игнорирует! Что и следовало ожидать! Таким образом одно из выражений полностью исключается! Тогда, встает вопрос - какое, выражение верно? А, вот ответ, вас еще более запутает! ОБА! Первое выражение вычисляет именно то, что и означает "СУММА по SALES - СУММА по QUOTA". И соответственно и второе выражение, так же вычисляет именно то, что и должно - "СУММА (SALES-QUOTA)"! Важно понять, что оба выражения справедливы и не содержат ошибок!!! Но значения возвращаемые выражениями отличаются, при наличии в полях таблицы значения NULL! По этому, важно: ХОРОШО ПРЕДСТАВЛЯТЬ, ЧТО ВЫ ХОТИТЕ ПОЛУЧИТЬ В ДАННОМ КОНКРЕТНОМ СЛУЧАЕ ПРИ ПОСТРОЕНИИ ЗАПРОСА! Иначе можно, наделать много ошибок и окончательно в них запутаться!!! :( Вот такой он "кровопийца", этот NULL и это еще не все где мы с ним будем разбираться! Вообще то, я стараюсь по мере, сил (а, сил у меня не меряно!!!) не забивать вам голову сухими формулировками! Но здесь как раз тот случай когда, следует их процитировать, итак:

  1. Если какие либо из значений содержащихся в столбце, равны NULL, при вычислении результата функции они исключаются!
  2. Если все значения в столбце равны NULL, то функции AVG(), SUM(), MIN(), MAX() возвращают значения NULL! Функция COUNT() возвращает ноль!
  3. Если в столбце нет значений (т.е. столбец пуст), то функции AVG(), SUM(), MIN(), MAX() возвращают значения NULL! Функция COUNT() возвращает ноль!
  4. Функция COUNT(*) подсчитывает количество строк и не зависит от наличия или отсутствия в столбце значений NULL! Если строк в столбце нет, то эта функция возвращает ноль!

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

1. Сколько различных названий рапортов существует в нашей компании?

SELECT COUNT(DISTINCT TITLE)
FROM SALESREPS
/

Получаем:

SQL> SELECT COUNT(DISTINCT TITLE)
  2  FROM SALESREPS
  3  /

COUNT(DISTINCTTITLE)
--------------------
                   5

2. В скольких офисах есть служащие превысившие плановые объемы продаж?

SELECT COUNT(DISTINCT REP_OFFICE)
FROM SALESREPS
WHERE SALES > QUOTA
/

Получаем:

SQL> SELECT COUNT(DISTINCT REP_OFFICE)
  2  FROM SALESREPS
  3  WHERE SALES > QUOTA
  4  /

COUNT(DISTINCTREP_OFFICE)
-------------------------
                        4

Вкратце опишу основные понятия, при работе с DISTINCT и агрегатами. Если вы используете DISTINCT и агрегатную функцию, то ее аргументом может быть только имя столбца, выражение не может быть аргументом. В функциях MIN(), MAX() так же нет смысла использовать DISTINCT! В функции COUNT() в принципе можно использовать DISTINCT, но это требуется не часто. А вот к функции COUNT(*) вообще не применимо DISTINCT, так как она просто подсчитывает число строк! Так же в одном запросе DISTINCT можно употреблять только один раз! Если оно применяется с аргументом, агрегатной функции, его уже нельзя использовать ни с одним другим аргументом! Вот, такие правила для DISTINCT с агрегатами, поэкспериментируйте сами и сможете убедиться!


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей - 05.10.2003