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

scott@TKYTE816>-select ename, sal, hiredate, hiredate-100windowtop,

2 first value(ename)

3 over (order by hiredate asc

4 range 100 preceding) ename prec,

5 first value(hiredate)

6 over (order by hiredate asc

7 range 100 preceding) hiredate prec

8 from emp

9 order by hiredate asc

14 ws selected.

Мы упорядочили один фрагмент по критерию HIREDATE ASC. При этом использовалась аналитическая функция FIRST VALUE поиска первого значения ENAME и первого значения HIREDATE в соответствующем окне. Посмотрев на строку данн1х хя сотрудника CLARK, можно обнаружить, что него значение в столбце HIREDATE - 9 июня 1981 года, 09-JUN-81, а дата за 100 дней до этой соответствует 1 марта 1981 года, 01-MAR-81. Для удобства эта дата помещена в столбец WINDOWTOP. Аналитическая функция затем вычисляется всех строк отсортированного фрагмента, предшествующих строке для сотрудника CLARK и имеющих значение в столбце HIREDATE в диапазоне с 01-MAR-81 по 09-JUN-81. Первое значение ENAME этого окна - JONES. Это имя и выдает аналитическая функция в столбце ENAME PREC.

Упорядочив данные по критерию HIREDATE DESC, мы получим:

scott@TKYTE816> select ename, sal, hiredate, hiredate+100 windowtop,

2 first value(ename)

3 over (order by hiredate desc

4 range 100 preceding) ename prec,

5 first value(hiredate)

6 over (order by hiredate desc

7 range 100 preceding) hiredate prec

8 from emp

9 order by hiredate desc 10 /

1050

Глава 12

14 rows selected.

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

Иногда достаточно сложно понять, какие значения будут входить в диапазон. Я считаю использование функции FIRST VALUE удобным методом, помогающим увидеть диапазоны окна и проверить, корректно ли установлены параметры. Теперь, представив диапазоны окон, мы используем их для в1числения чего-то более существенного. Пусть необходимо выбрать зарплату каждого сотрудника и среднюю зарплату всех принятых на работу в течение 100 преды1дущих дней, а также среднюю зарплату всех принятых на работу в течение 100 следующих дней. Соответствующий запрос будет выглядеть так:

scott@TKYTE816> select ename, hiredate, sal.

avg(sal)

over (order by hiredate asc avg sal 100 days before,

avg(sal)

over (order by hiredate desc avg sal 100 days after from emp

range 100 preceding)

range 100 preceding)

order

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

Если теперь снова обратиться к строке для сотрудника CLARK, то, поскольку мы уже понимаем, какое окно в группе будет с ней связано, легко убедиться, что средняя зарплата (2758,33) равна (2450+2850+2975)/3. Это средняя зарплата для строки CLARK и строк, предшествующих CLARK (это строки для сотрудников JONES и BLAKE) при упорядочении данных по возрастанию. С другой стороны, средняя зарплата 1975,00 равна (2450+1500)/2. Это средняя зарплата для строки CLARK и строк, предшествующих CLARK при упорядочении данных по убыванию. С помощью этого запроса можно одновременно вычислить среднюю зарплату для сотрудников, принятых на работу за 100 дней до и за 100 дней после сотрудника CLARK.

Окна диапазона можно задавать только по данным типа NUMBER или DATE, поскольку нельзя добавить или вычесть N единиц из значения типа VARCHAR2. Еще одно ограничение для таких окон состоит в том, что в конструкции ORDER BY может быть только один столбец - диапазоны по природе своей одномерны. Нельзя задать диапазон в N-мерном пространстве.

Окна строк

Окна срок задаются в физических единицах, строках. Перепишем вступительный пример из предыдущего раздела, задав окно строк:

count (*) over (order by x ;WS 5 preceding)

Это окно будет включать до 6 строк: текущую и пять предыдущих (порядок определяется конструкцией ORDER BY). Для окон по строкам нет ограничений, присущих окнам по диапазону; данные могут быть любого типа и упорядочивать можно по любому количеству столбцов. Вот пример, сходный с рассмотренным ранее:

scott@TKYTE816> select ename, sal, hiredate,

2 first value(ename)

3 over (order by hiredate asc

4 rows 5 preceding) ename prec,

5 first value(hiredate)

6 over (order by hiredate asc

7 rows 5 preceding) hiredate prec

8 from emp

9 order by hiredate asc 10 /

ENAME

SAL HIREDATE ENAME PREC HIREDATE

SMITH 800.00 17-DEC-80 SMITH 17-DEC-80

ALLEN 1600.00 20-FEB-81 SMITH 17-DEC-80 WARD 1250.00 22-EEB-81 SMITH 17-DEC-80