В результирующем множестве можно увидеть, что служащие одного отдела имеют одно значение поля DEPT CNT и что значение поля JOB CNT одинаковое для служащих, занимающих одну и ту же должность.

На данный момент должно быть ясно, что оператор PARTITION BY работает так же, как и оператор GROUP BY, но на него не оказывают влияния другие элементы оператора SELECT, и он не требует присутствия оператора GROUP BY.

Что происходит в случае присутствия NULL-значений

Как и оператор GROUP BY, PARTITION BY сводит все NULL-значе-ния в одну группу или сегмент. Таким образом, результат присутствия NULL-значений при использовании PARTITION BY аналогичен тому, который имеем при работе с GROUP BY. В следующем запросе оконная функция применяется для подсчета количества служащих, получающих ту или иную сумму комиссионных (для удобства чтения вместо NULL возвращается -1):

select coalesce(comm,-1) as comm,

count(*)over(partition by comm) as cnt from emp

Поскольку используется COUNT(*), подсчитывается количество строк. Можно заметить, что 10 служащих не получают комиссионные (NULL). Примените COMM вместо * и получите совершенно другие результаты:

select coalesce(comm,-1) as comm,

count(comm)over(partition by comm) as cnt from emp

COMM

300 500 1400

В этом запросе используется COUNT(COMM). Это означает, что пере-считываются только не-NULL значения столбца COMM. Выявлен один служащий с размером комиссионного вознаграждения 0, один служащий с размером комиссионного вознаграждения 300 и т. д. Но обратите внимание на значение счетчика для служащих с комиссионными NULL! Оно равно 0. Почему? Потому что агрегатные функции игнорируют NULL-значения или, если выразиться более точно, агрегатные функции учитывают только не-NULL значения.

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

Когда порядок имеет значение

Иногда порядок рассмотрения строк оконной функцией оказывает влияние на результаты запроса. Поэтому синтаксис оконной функции включает подоператор ORDER BY, который можно разместить в операторе OVER. Оператор ORDER BY определяет порядок расположения строк в сегменте (помните, сегмент в отсутствие оператора PARTITION BY - это все результирующее множество).

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

Оператор ORDER BY, используемый в операторе OVER оконной функции, определяет две вещи:

1. Как упорядочены строки в сегменте.

2. Какие строки участвуют в вычислениях.

Рассмотрим следующий запрос, который суммирует и вычисляет текущую сумму заработных плат служащих 10-го отдела (DEPTNO 10):

select deptno, ename, hiredate, sal,

sum(sal)over(partition by deptno) as total1, sum(sal)over() as total2,

sum(sal)over(order by hiredate) as running total from emp where deptno=10

DEPTNO ENAME HIREDATE SAL TOTAL1 TOTAL2 RUNNING TOTAL

10 CLARK 09-JUN-1981 2450 8750 8750 2450

10 KING 17-NOV-1981 5000 8750 8750 7450

10 MILLER 23-JAN-1982 1300 8750 8750 8750

Чтобы держать вас в тонусе, я включил суммирование с пустыми круглыми скобками. Обратите внимание, что значения TOTAL1 и TOTAL2 одинаковые. Почему? Опять же из-за порядка обработки оконных функций. Предикат WHERE фильтрует результирующее множество таким образом, что при суммировании учитываются только заработные платы 10-го отдела (DEPT-NO 10). В данном случае имеется всего один сегмент - все результирующее множество, состоящее из заработных плат только 10-го отдела. Следовательно, TOTAL1 и TOTAL2 равны.

Взглянув на значения столбца SAL, можно сразу понять, как получаются значения RUNNING TOTAL (промежуточные результаты найти несложно, просуммировав значения SAL). Но более важно, почему включение ORDER BY в конструкцию OVER обеспечило вычисление текущей суммы? Дело в том, что при использовании ORDER BY в конструкции OVER в сегменте задается скользящее или подвижное окно по умолчанию, даже несмотря на то что мы его не видим. Последним элементом строки является сумма по HIREDATE, заданная выражением ORDER BY HIREDATE.

Следующий запрос аналогичен предыдущему, но в нем поведение по умолчанию, являющееся результатом применения ORDER BY HIRE-DATE, явно задается оператором RANGE BETWEEN (о котором более подробно рассказывается далее):

select deptno, ename, hiredate, sal,

sum(sal)over(partition by deptno) as totall, sum(sal)over() as total2, sum(sal)over(order by hiredate

range between unbounded preceding and current row) as running total

from emp where deptno=10