Тогда как создать запрос, правильно подсчитывающий количество членов в группе значений NULL, таким образом, предоставляющий нам искомую информацию и при этом соответствующий нашему определению группы? Пример ниже показывает, как справиться с парадоксом группы значений NULL:

select coalesce(name,NULL) as name, count(*) as cnt from fruits group by name

NAME CNT

Apple 1

Oranges 3

Peach 1

NULL 5

Чтобы обойти парадокс группы значений NULL, используем функцию COUNT(*), а не COUNT(NAME). Агрегатные функции проигнорируют значения NULL, если таковые встретятся в переданных столбцах. Таким образом, чтобы не получить нуль при использовании COUNT, передаем не имена столбцов, а звездочку (*). Это заставит функцию COUNT подсчитывать строки, а не значения столбцов, поэтому есть ли там значения NULL или нет, не важно.

Еще один парадокс связан с аксиомой о том, что каждая группа результирующего множества (для каждого e в G) уникальна. Из-за природы результирующих множеств SQL и таблиц, которые, если быть более точным, являются не множествами, а мультимножествами или множествами с повторяющимися элементами (поскольку допускается существование дублирующихся строк), может быть получено результирующее множество с дублирующимися группами. Рассмотрим следующие запросы:

select coalesce(name,NULL) as name, count(*) as cnt from fruits group by name union all

select coalesce(name,NULL) as name, count(*) as cnt from fruits group by name

NAME CNT

Apple 1

Oranges 3

Peach 1

NULL 5

Apple 1

Oranges Peach

NULL

select x.* from (

select coalesce(name,NULL) as name, count(*) as cnt from fruits group by name

) x,

(select deptno from dept) y

NAME

Apple

Apple

Apple

Apple

Oranges

Oranges

Oranges

Oranges

Peach

Peach

Peach

Peach

NULL

NULL

NULL

NULL

Как видим, в окончательных результатах группы повторяются. К счастью, не стоит сильно переживать, поскольку это можно назвать парадоксом лишь частично. Первое свойство группы говорит, что для (G,e) G является результирующим множеством самостоятельного или самодостаточного запроса, использующего оператор GROUP BY. Попросту говоря, результирующее множество любого запроса с GROUP BY соответствует нашему определению группы. Дублирование групп может возникнуть лишь при создании мультимножества в результате сочетания результатов двух запросов с GROUP BY. В первом запросе предыдущего примера используется оператор UNION ALL, что является операцией не над множествами, а над мультимножествами, и дважды вызывается GROUP BY, таким образом, по сути, выполняется два запроса.

Если использовать оператор UNION, что является операцией над множествами, повторяющихся групп не появится.

Второй из представленных запросов использует декартово произведение, но для его выполнения сначала надо материализовать группу. Та-

10 3

20 5 30 6

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

select hello as msg, 1 as num, deptno,

(select count(*) from emp) as total, count(*) as cnt from emp group by deptno

MSG NUM DEPTNO TOTAL CNT

hello 1 10 14 3 hello 1 20 14 5 hello 1 30 14 6

ким образом, самодостаточный запрос с GROUP BY удовлетворяет нашему определению. Ни в одном из двух примеров нет никаких противоречий определению SQL-группы. Они приведены здесь для полноты картины и как доказательство того, что в SQL возможно практически все.

Отношения между SELECT и GROUP BY

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

select deptno, count(*) as cnt from emp

DEPTNO должен быть обязательно указан в списке оператора GROUP

select deptno, count(*) as cnt

from emp

group by deptno

DEPTNO CNT