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

Оператор RANGE BETWEEN, представленный в этом запросе, ANSI называет оператором кадрирования. Далее я буду придерживаться этого термина. Теперь нетрудно увидеть, почему ORDER BY в конструкции OVER обусловливает вычисление текущей суммы; мы (по умолчанию) указали запросу суммировать все строки, начиная с текущей строки и включая все предыдущие ( предыдущие согласно ORDER BY; в данном случае строки упорядочиваются по HIREDATE).

Оператор кадрирования

Применим к результирующему множеству оператор кадрирования из предыдущего запроса, начиная со служащего CLARK, который был принят на работу первым:

1. Вычисляем сумму, начиная с заработной платы служащего CLARK, 2450, и включая заработные платы всех служащих, принятых на работу до CLARK. Поскольку CLARK был первым служащим 10-го отдела, сумма просто равна его заработной плате, 2450. Это первое значение, возвращенное RUNNING TOTAL.

2. Перейдем к следующему согласно HIREDATE служащему, KING, и еще раз применим оператор кадрирования. Вычисляем сумму по столбцу SAL, начиная с текущей строки, 5000 (заработная плата KING), и включаем все предыдущие строки (всех служащих, которые были приняты на работу до KING). CLARK - единственный служащий, принятый на работу раньше KING, поэтому сумма равна 5000 + 2450, что составляет 7450 - второе значение, возвращенное RUNNING TOTAL.

3. Переходим к MILLER, последнему служащему сегмента по HIREDA-TE, и еще раз применяем оператор кадрирования. Вычисляем сумму по столбцу SAL, начиная с текущей строки, 1300 (заработная плата MILLER), и включаем все предыдущие строки (всех служащих, которые были приняты на работу до MILLER). CLARK и KING поступили на работу раньше MILLER, таким образом, их заработные платы включаются в текущую сумму для MILLER: 2450+5000+1300= =8750 - значение, возвращаемое RUNNING TOTAL для MILLER.

Как видите, именно оператор кадрирования формирует текущую сумму. ORDER BY определяет порядок вычисления и также подразумевает кадрирование по умолчанию.

В общем, оператор кадрирования позволяет определять разные подокна данных, участвующих в вычислениях. Существует множество способов определения таких подокон. Рассмотрим следующий запрос:

select deptno, ename, sal,

sum(sal)over(order by hiredate

range between unbounded preceding and current row) as run total1, sum(sal)over(order by hiredate

rows between 1 preceding and current row) as run total2, sum(sal)over(order by hiredate

range between current row and unbounded following) as run total3, sum(sal)over(order by hiredate

rows between current row and 1 following) as run total4

Не пугайтесь, этот запрос не так ужасен, как выглядит. Мы уже видели RUN TOTAL1 и результаты применения оператора кадрирования UNBOUNDED PRECEDING AND CURRENT ROW . Вот краткое описание происходящего в других примерах:

KUN TOTAL2

Вместо ключевого слова RANGE в данном операторе кадрирования используется ROWS; это означает, что кадр, или окно, будет создано из некоторого количества строк. 1 PRECEDING говорит о том, что кадр будет начинаться со строки, стоящей непосредственно перед текущей строкой. Диапазон распространяется до CURRENT ROW. Таким образом, RUN TOTAL2 - это сумма заработных плат текущего и предыдущего, на основании HIREDATE, сотрудников.

Так случилось, что RUN TOTAL1 и RUN TOTAL2 для CLARK и KING равны. Почему? Подумайте, какие значения суммировались для каждого из этих служащих, в каждой из двух оконных функций. Подумайте хорошенько и найдете ответ.

RUN TOTAL3

Оконная функция для вычисления RUN TOTAL3 выполняет обратное тому, что делалось для RUN TOTAL1. Суммирование начинается с текущей строки и включает не все предыдущие строки, а все последующие строки.

RUN TOTAL4

Инверсия RUN TOTAL2. Суммирование начинается с текущей строки и включает не одну предыдущую, а одну следующую строку.

Если вы поняли все, о чем шла речь до сих пор, у вас не возникнет проблем ни с одним рецептом данной книги. Однако если что-то остается неясным, попытайтесь попрактиковаться с собственными примерами и данными. Лично мне проще учиться через написание кода с использованием новых возможностей, а не просто читать о них.

Заключение к вопросу о кадрировании

Ниже приведен последний пример того, какой эффект на результат запроса оказывает применение оператора кадрирования:

select ename, sal,

min(sal)over(order max(sal)over(order min(sal)over(order range and

max(sal)over(order range and

min(sal)over(order range and

max(sal)over(order range and

max(sal)over(order rows and

from emp

by sal) min1, by sal) max1, by sal

between unbounded preceding unbounded following) min2, by sal

between unbounded preceding unbounded following) max2, by sal

between current row current row) min3, by sal

between current row current row) max3, by sal

between 3 preceding 3 following) max4