Агрегатные функции 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; если строк в таблице нет, эта функция возвращает ноль.
УДАЛЕНИЕ ПОВТОРЯЮЩИХСЯ СТРОК (DISTINCT)
Ключевое слово distinct указывается в начале списка возвращаемых столбцов и служит для удаления повторяющихся строк из таблицы результатов запроса. С помощью этого ключевого слова можно также указать, что перед применением агрегатной функции к столбцу из него следует удалить все повторяющиеся значения. Для этого необходимо включить ключевое слово distinct перед аргументом агрегатной функции сразу же после открывающей круглой скобки.
В стандарте SQL1 говорится, что при использовании ключевого слова distinct в агрегатной функции, ее аргументом должно быть простое имя столбца; аргумент не может быть выражением. Стандарт позволяет использовать ключевое слово distinct в агрегатных функциях sum()и avg () и не разрешает использовать его в агрегатных функциях min () и мах (), поскольку в этом нет смысла; тем не менее в ряде реализации SQL подобное все-таки допустимо. Кроме того, стандарт требует применения ключевого слова distinct в функции count (), но в ряде реализации SQL можно использовать функцию count () и без него. В функции count (*) данное ключевое слово применять нельзя, поскольку она вообще не имеет отношения к столбцам, а просто подсчитывает число строк. В стандарте SQL2 упомянутые ограничения сняты и разрешается использовать ключевое слово distinct во всех агрегатных функциях, а также применять выражения в качестве аргументов для всех функций.
Кроме того, ключевое слово distinct в одном запросе можно употреблять только одинраз. Если оно применяется вместе с аргументом одной из агрегатных функций, его нельзя использовать ни с одним другим аргументом, Если оно указано перед списком возвращаемых столбцов, его нельзя употреблять ни в одной агрегатной функции. Единственным исключением из этого правила является случай, когда ключевое слово distinct используется внутри вложенного запроса, входящего в запрос, в котором оно уже применяется.
35. ЗАПРОСЫ С ГРУППИРОВКОЙ
(ПРЕДЛОЖЕНИЕ GROUP BY)
Итоговые запросы напоминают итоговую информацию, находящуюся обычно в конце отчета. Эти запросы "сжимают" подробные данные, содержащиеся в отчете, в одну строку итоговых результатов. Но, как известно, в отчетах иногда используются также промежуточные итоги. И точно так же бывает необходимо получать промежуточные итоги результатов запроса. Эту возможность предоставляет предложение group by оператора select.
Назначение предложения group by проще всего понять на примере. Рассмотрим два следующих запроса:
Первый запрос представляет собой простой итоговый запрос, аналогичный примерам, рассмотренным ранее. Второй запрос возвращает несколько итоговых строк — по одной строке для каждой группы. На логическом уровне запрос выполняется следующим образом:
1. Заказы делятся на группы, по одной группе для каждого служащего. В каждой группе все заказы имеют одно и то же значение в столбце rep.
2. Для каждой труппы вычисляется среднее значение столбца amount по всем строкам, входящим в группу, и генерируется одна итоговая строка результатов. Эта строка содержит значение столбца rep для группы и среднюю стоимость заказа для данной группы.
Запрос, включающий в себя предложение group by, называется запросом с группировкой, поскольку он объединяет строки исходных таблиц в группы и для каждой группы строк генерирует одну строку таблицы результатов запроса. Столбцы, указанные в предложении group by, называются столбцами группировки, поскольку именно они определяют, по какому признаку строки делятся на группы. Ниже приведен ряд запросов с группировкой:
Между агрегатными функциями SQL и предложением group by существует связь. Агрегатная функция берет столбец значений и возвращает одно значение. Предложение group by указывает, что результаты запроса следует разделить на группы, применить агрегатную функцию по отдельности к каждой группе и получить для каждой группы одну строку результатов.
НЕСКОЛЬКО СТОЛБЦОВ ГРУППИРОВКИ
SQL позволяет группировать результаты запроса на основании двух или более столбцов. Например, предположим, что вам требуется сгруппировать заказы по служащим и клиентам. Эту задачу выполняет запрос, приведенный ниже:
Можно отсортировать данные таким образом, чтобы строки в таблице результатов запроса шли в нужном порядке. Во многих реализациях SQL при использовании предложения group by сортировка выполняется автоматически, однако автоматический порядок сортировки можно изменить с помощью предложения order by, как показано ниже:
С помощью одного запроса невозможно получить как детальные, так и промежуточные итоговые результаты. Чтобы получить детальные результаты с итогами по группам, необходимо с помощью программного SQL написать прикладную программу и вычислить промежуточные итоговые результаты в программе. В SQL Server это ограничение стандартного SQL устраняется с помощью дополнительного предложения compute, которое добавляется в конец оператора select. Предложение compute обеспечивает получение промежуточных и общих итогов, какпоказано в следующем примере:
Приведенный выше запрос генерирует обычные результаты, содержащие одну строку для каждой строки таблицы orders и отсортированные по столбцам rep и cust. Кроме того, он вычисляет сумму заказов для каждой пары клиент/служащий (промежуточный итог нижнего уровня) и вычисляет сумму заказов и среднюю стоимость заказа для каждого служащего (промежуточный итог верхнего уровня). Таким образом, результаты запроса содержат смесь нормальных строк и итоговых строк двух уровней.
Предложение compute не вписывается ни в какие стандарты. Более того, оно нарушает основные правила построения реляционных запросов, поскольку результаты оператора select, в котором используется это предложение, являются не таблицей, а странной комбинацией строк различного типа. Но тем не менее, как показывает пример, оно может быть весьма полезным.
ОГРАНИЧЕНИЯ НА ЗАПРОСЫ С ГРУППИРОВКОЙ
На запросы, в которых используется группировка, накладываются дополнительные ограничения. Столбцы с группировкой должны представлять собой реальные столбцы таблиц, перечисленных в предложении from. Нельзя группировать строки на основании значения вычисляемого выражения.
Кроме того, существуют ограничения на элементы списка возвращаемых столбцов. Все элементы этого списка должны иметь одно значение для каждой группы строк. Это означает, что возвращаемым столбцом может быть:
• константа,
• агрегатная функция, возвращающая одно значение для всех строк, входящих в группу;
• столбец группировки, который, по определению, имеет одно и то же значение во всех строках группы;
• выражение, включающее в себя перечисленные выше элементы.
На практике в список возвращаемых столбцов запроса с группировкой всегда входят столбец группировки и агрегатная функция. Если последняя не указана, значит, запрос можно более просто выразить с помощью ключевого слова distinct без использования предложения group by. И наоборот, если не включить в результаты запроса столбец группировки, вы не сможете определить, к какой группе относится каждая строка результатов!
Еще одно ограничение запросов с группировкой обусловлено тем, что в SQL игнорируется информация о первичных и внешних ключах при анализе правильности запроса с группировкой. Рассмотрим следующий запрос:
Зная природу данных, можно сказать, что запрос правильный, поскольку группировка по идентификатору служащего — фактически то же самое, что и группировка по имени служащего. Говоря более точно, столбец группировки empl_num является первичным ключом таблицы salesreps, поэтому столбец name должен иметь одно значение для каждой группы. Тем не менее выдается сообщение об ошибке, поскольку столбец name не указан в качестве столбца группировки. Чтобы решить эту проблему, необходимо просто включить этот столбец в предложение group by:
Конечно, если идентификатор служащего в результатах запроса не требуется, можно вообще исключить его из списка возвращаемых столбцов:
