FORD 3000 800 3000 800 5000 3000 3000 5000 KING 5000 800 5000 800 5000 5000 5000 5000

Хорошо, разложим этот запрос на составляющие:

MINI

В оконной функции, формирующей этот столбец, не определен оператор кадрирования, поэтому в дело вступает кадрирование по

умолчанию, UNBOUNDED PRECEDING AND CURRENT ROW. Почему значение MIN1 равно 800 для всех строк? Потому что первой идет наименьшая заработная плата (ORDER BY SAL), и она так всегда и остается наименьшей, или минимальной.

MAX1

Значения MAX1 сильно отличаются от значений MIN1. Почему? Ответ (опять же) - из-за оператора кадрирования по умолчанию,

UNBOUNDED PRECEDING AND CURRENT ROW. В сочетании

с ORDER BY SAL этот оператор кадрирования обеспечивает соответствие максимальной заработной платы значению заработной платы в текущей строке.

Рассмотрим первую строку, служащего SMITH. При обработке заработной платы SMITH и всех предыдущих заработных плат значение MAX1 для строки SMITH получается равным его заработной плате, потому что предыдущих заработных плат нет. Переходим к следующей строке, служащему JAMES. При сравнении заработной платы JAMES со всеми предыдущими заработными платами, в данном случае с заработной платой служащего SMITH, получаем, что заработная плата JAMES больше, чем у SMITH, и, таким образом, является максимальной из двух. Если применить эту логику ко всем строкам, мы увидим, что значение MAX1 для каждой строки соответствует заработной плате текущего служащего.

MIN2 и MAX2

В данном случае задан оператор кадрирования UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, что аналогично заданию пустых круглых скобок. Таким образом, при вычислении MIN и MAX участвуют все строки результирующего множества. Как и следовало ожидать, значения MIN и MAX для всего результирующего множества являются постоянными, и, следовательно, значения этих столбцов также неизменны.

MIN3 и MAX3

В данном случае задан оператор кадрирования CURRENT ROW

AND CURRENT ROW. Это означает, что при поиске MIN и MAX заработных плат используется только заработная плата текущего служащего. Таким образом, во всех строках и MIN3, и MAX3 равны SAL. Это просто, не так ли?

MAX4

Для вычисления MAX4 задан оператор кадрирования 3 PRECEDING AND 3 FOLLOWING, что означает, что для каждой строки рассматриваем три предыдущие и три последующие строки, а также саму текущую строку. В данном случае в результате вызова функции MAX(SAL) будет получено наибольшее значение заработной платы для этих строк.

Если взглянуть на значение MAX4 для служащего MARTIN, можно заметить, как действует оператор кадрирования. Заработная плата MARTIN - 1250. Заработные платы трех служащих до MARTIN: 1250 (WARD), 1100 (ADAMS) и 950 (JAMES). Заработные платы

трех служащих после MARTIN: 1300 (MILLER), 1500 (TURNER)

и 1600 (ALLEN). Из всех этих заработных плат, включая заработную плату MARTIN, наибольшей является заработная плата ALLEN. Таким образом, значение MAX4 для MARTIN равно 1600.

Понятность + производительность = мощь

Как видите, оконные функции обладают исключительной мощью, поскольку позволяют создавать запросы, содержащие как детальную, так и обобщенную информацию. Запросы, в которых применяются оконные функции, короче, но при этом эффективнее, чем запросы, использующие несколько рефлексивных объединений и/или скалярных подзапросов. Рассмотрим следующий запрос, который без труда отвечает на все поставленные вопросы: Сколько служащих в каждом отделе? Сколько служащих, занимающих ту или иную должность, в каждом отделе (например, сколько клерков в 10-м отделе)? Сколько всего служащих в таблице EMP?

select deptno, job,

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

30 SALESMAN 6 4 14

30 SALESMAN 6 4 14

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

select a.deptno, a.job,

(select count(*) from emp b

where b.deptno = a.deptno) as emp cnt,

(select count(*) from emp b

where b.deptno = a.deptno and b.job = a.job) as job cnt,

(select count(*) from emp) as total from emp a order by 1,2

DEPTNO JOB EMP CNT JOB CNT TOTAL

10 CLERK 3 1 14

10 MANAGER 3 1 14

10 PRESIDENT 3 1 14

20 ANALYST 5 2 14

20 ANALYST 5 2 14

20 CLERK 5 2 14

20 CLERK 5 2 14

20 MANAGER 5 1 14

30 CLERK 6 1 14

30 MANAGER 6 1 14

30 SALESMAN 6 4 14

30 SALESMAN 6 4 14

30 SALESMAN 6 4 14

30 SALESMAN 6 4 14

Решение без использования оконных функций написать нетрудно, хотя, безусловно, оно не такое ясное или эффективное (разница в производительности для таблицы в 14 строк незаметна, но примените эти запросы к таблице, скажем, включающей 1000 или 10000 строк, и преимущество оконных функций над многократным рефлексивным объединением и скалярными подзапросами станет очевидным).

Формирование основы

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