1052

Глава 12

Взглянув на строку для сотрудника CLARK, можно увидеть, что первой в окне ROWS 5 PRECEDING следует строка для сотрудника SMITH - она просто пятая перед строкой для CLARK в соответствии с заданным порядком. Строка для сотрудника SMITH будет первой в окне и для всех предыдущих строк (для BLAKE, JONES и т.д.). Дело в том, что строка для SMITH - первая в данной группе (она будет первой и для самой себя). При сортировке группы по возрастанию окна изменяются:

scott@TKYTE816> select ename, sal, hiredate.

2 first value(ename)

3 over (order by hiredate

4 rows 5 preceding)

5 first value(hiredate)

6 over (order by hiredate

7 rows 5 preceding)

8 from emp

9 order by hiredate desc 10 /

desc

ename prec,

desc

hiredate prec

14 rows selected.

Теперь первое значение для набора из 5 строк, предшествующих в группе строке для сотрудника CLARK, - строка для сотрудника JAMES. Теперь можно вычислить сред-

Аналитические функции

нюю зарплату для указанного сотрудника и пяти принятых на работу до него и после него:

scott@TKYTE816> select ename, hiredate, sal,

2 avg(sal)

3 over (order by hiredate asc rows 5 preceding) avg 5 before,

4 count(*)

5 over (order by hiredate asc rows 5 preceding) obs before,

6 avg(sal)

7 over (order by hiredate desc rows 5 preceding) avg 5 after,

8 count(*)

9 over (order by hiredate desc rows 5 preceding) obs after

10 from emp

11 order by hiredate

12 /

Обратите внимание, что я выбирал также значение COUNT(*). Это позволяет понять, по какому количеству строк б1ло вычислено среднее значение. Можно явно увидеть, что для вычисления средней зарплаты сотрудников, принятых до сотрудника ALLEN, использовалось только 2 записи, а для вычисления средней зарплаты сотрудников, нанятых после него, - 6. В том месте группы, где находится строка для сотрудника ALLEN, есть только 1 пред]дущая запись, а при вычислении аналитической функции используются все имеющиеся строки.

Задание окон

Теперь, понимая различие между окнами диапазонов и окнами строк, можно изучать способы задания окон.

В простейшем случае, окно задается с помощью одной из трех следующих взаимоисключающих конструкций.

UNBOUNDED PRECEDING. Окно начинается с первой строки текущей группы и заканчивается текущей обрабатываемой строкой.

1054

Глава 12

CURRENT ROW. Окно начинается (и заканчивается) текущей строкой.

Числовое в1ражение PRECEDING. Окно начинается со строки за числовое вы-ражение строк до текущей, если оно задается по строкам, или со строки, меньшей по значению столбца, упомянутого в конструкции ORDER BY, не более чем на числовое в1ражение, если оно задается по диапазону.

Окно CURRENT ROW в простейшем виде, вероятно, никогда не используется, поскольку ограничивает применение аналитической функции одной текущей строкой, а для этого аналитические функции не нужны. В более сложном случае для окна задается также конструкция BETWEEN. В ней CURRENT ROW можно указывать в качестве начальной или конечной строки окна. Начальную и конечную строку окна в конструкции BETWEEN можно задавать с использованием любой из перечисленн1х выше конструкций и еще одной, дополнительной:

Числовое в1ражение FOLLOWING. Окно заканчивается (или начинается) со строки, через числовое в1ражение строк после текущей, если оно задается по строкам, или со строки, большей по значению столбца, упомянутого в конструкции ORDER BY, не более чем на числовое в1ражение, если оно задается по диапазону.

Рассмотрим ряд примеров такого задания окон:

scott@TKYTE816> select deptno, ename, hiredate.

9 10

count(*) over (partition by deptno

order by hiredate nulls first range 100 preceding) cnt range,

count(*) over (partition by deptno

order by hiredate nulls first rows 2 preceding) cnt rows

from emp

where deptno in (10, 20) order by deptno, hiredate

Как видите, окно RANGE 100 PRECEDING содержит только строки текущего фрагмента, предшествующие текущей строке, и те, значение которых HIREDATE находится в диапазоне HIREDATE-100 и HIREDATE относительно текущей. В данном случае таких строк всегда 1 или 2, т.е. интервал между приемом людей на работу обычно пре-