В этом примере COUNT(*) OVER() - это вызов оконной функции. Присутствие ключевого слова OVER показывает, что COUNT будет рассматриваться не как агрегатная, а как оконная функция. В общем, стандарт SQL допускает использование всех агрегатных функций в качестве оконных, а ключевое слово OVER является отличительным признаком оконных функций.

Итак, что именно делает оконная функция COUNT(*) OVER ()? Для каждой возвращаемой запросом строки она возвращает количество всех строк в таблице. Как предполагают пустые круглые скобки, ключевое слово OVER принимает дополнительные операторы, которые определяют диапазон строк, рассматриваемый оконной функцией. Если таких операторов нет, оконная функция обрабатывает все строки результирующего множества, поэтому в данном примере значение 14 повторяется в каждой строке вывода.

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

Порядок выполнения

Прежде чем углубиться в оператор OVER, важно отметить, что оконные функции выполняются как последний шаг в обработке SQL перед оператором ORDER BY. В качестве примера порядка выполнения оконных функций возьмем запрос из предыдущего раздела и применим предикат WHERE, чтобы отфильтровать служащих 20 и 30-го отделов (DEPTNO 20 и 30):

select ename, deptno,

count(*) over() as cnt from emp where deptno = 10 order by 2

ENAME DEPTNO CNT

CLARK 10 3

KING 10 3

MILLER 10 3

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

Этот запрос по-прежнему возвращает 14 строк, но теперь в результате

применения PARTITION BY DEPTNO функция COUNT выполняется

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

select e.ename, e.deptno,

(select count(*) from emp d where e.deptno=d.deptno) as cnt

Сегменты

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

select ename, deptno,

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

from emp e order by 2

Оператор PARTITION BY замечателен также тем, что выполняет вычисления независимо от других оконных функций, осуществляя сегментирование по другим столбцам в том же выражении SELECT. Рассмотрим следующий запрос, в результате которого для каждого служащего возвращается такая информация: его имя, отдел, количество служащих в этом отделе, его должность и количество служащих, занимающих эту должность:

select ename, deptno,

count(*) over(partition by deptno) as dept cnt, job,

count(*) over(partition by job) as job cnt from emp order by 2

ENAME

DEPTNO DEPT CNT JOB

JOB CNT