1046

Глава 12

ops$tkyte@DEV816> select ename, deptno,

2 sum(sal) over (order by ename, deptno)

3 sum(sal) over (order by deptno, ename)

4 from emp

5 order by ename, deptno

sum ename deptno, sum deptno ename

14 rows selected.

Оба столбца SUM(SAL) одинаково корректны; один из них содержит SUM(SAL) при упорядочении по столбцу DEPTNO, а потом - по ENAME, а другой - при упорядочении по столбцу ENAME, а потом - по DEPTNO. Поскольку результирующее множество упорядочено по (ENAME, DEPTNO), значения SUM(SAL), вычислявшиеся именно в этом порядке, кажутся более корректными, но общая сумма совпадает: 29025.

Конструкция ORDER BY в аналитических функциях имеет следующий синтаксис:

ORDER BY выражение [ASC DESC] [NULLS FIRST NULLS LAST]

Она совпадает с конструкцией ORDER BY для запроса, но будет упорядочивать строки только в пределах фрагментов и может не совпадать с конструкцией ORDER BY для запроса в целом (или любого другого фрагмента). Конструкции NULLS FIRST и NULLS LAST впервые появились в версии Oracle 8.1.6. Они позволяют указать, где при упорядочении должны быть значения NULL - в начале или в конце. В случае сортировки по убыванию (DESC), особенно в аналитических функциях, эта новая возможность принципиально важна. Почему - описано в разделе Проблемы в конце главы.

Конструкция окна

Синтаксис этой конструкции на первый взгляд кажется сложным из-за используемых ключевых слов. Конструкция вида RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, задающая стандартное окно при использовании конструкции ORDER BY, не похожа на те, что постоянно используются разработчиками. Синтаксис конструкции окна достаточно сложен для описания. Вместо попыток перерисовать синтаксические схемы, представленные в руководстве OracleSi SQL Reference Manual, я перечислю все варианты конструкции окна и опишу, какой набор данных в пределах груп-

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

1047

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

Конструкция окна позволяет задать перемещающееся или жестко привязанное окно (набор) данных в пределах группы, с которым будет работать аналитическая функция. Например, конструкция диапазона RANGE UNBOUNDED PRECEDING означает: применять аналитическую функцию к каждой строке данной группы, с первой по текущую . Стандартным является жестко привязанное окно, начинающееся с первой строки группы и продолжающееся до текущей. Если используется следующая аналитическая функция:

SUM(sal) OVER

(PARTITION BY deptno ORD BY ename

ROWS 2 PRECEDING) department total2,

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

scott@TKYTE816> break on deptno scott@TKYTE816> select deptno, ename, sal,

sum(sal) over

(partition by deptno order by ename rows 2 preceding) sliding total from emp

order by deptno, ename

DEPTNO ENAME

SAL SLIDING TOTAL

14 rows selected.

Нас интересует эта часть запроса:

2 sum(sal) over

3 (partition by deptno

1048

Глава 12

4 order by ename

5 rows 2 preceding) sliding total

Конструкция, определяющая фрагментацию, приводит к вычислению SUM(SAL) по отделам, независимо от других групп (значение SUM(SAL) сбрасывается при изменении номера отдела). Конструкция ORDER BY ENAME приводит к сортировке данн1х в каждом отделе по столбцу ENAME; это позволяет с помощью конструкции окна, rows 2 preceding, при суммировании зарплат обращаться к двум предыдущим строкам в соответствии с заданным порядком сортировки. Например, значение в столбце SLIDINGTOTAL для сотрудника SMITH - 6775, что равно сумме значений 800, 3000 и 2975. Это сумма зарплат в строке для SMITH и двух предыдущих строках окна.

Можно создавать окна по двум критериям: по диапазону (RANGE) значений данн1х или по смещению (ROWS) относительно текущей строки. Конструкция RANGE уже встречалась ранее, RANGE UNBOUNDED PRECEDING например. Она требует брать все строки вплоть до текущей, в соответствии с порядком, задаваемым конструкцией ORDER BY. Следует помнить, что для использования окон необходимо задавать конструкцию ORDER BY. Сейчас мы рассмотрим задание окон с помощью конструкций ROWS и RANGE, а затем другие способы задания окон.

Окнадиапазона

Окна диапазона объединяют строки в соответствии с заданным порядком. Если в запросе сказано, например, range 5 preceding , то будет сгенерировано перемещающееся окно, включающее предыдущие строки группы, отстоящие от текущей строки не более чем на 5 строк. Диапазон можно задавать в виде числового выражения или выражения, значением которого является дата. Применять конструкцию RANGE с другими типами данных нельзя.

Если имеется таблица ЕМР со столбцом HIREDATE типа даты и задана аналитическая функция

count(*) over (order by hiredate asc range 100 preceding)

она найдет все предыдущие строки фрагмента, значение которых в столбце HIREDATE лежит в пределах 100 дней от значения HIREDATE текущей строки. В этом случае, поскольку данные сортируются по возрастанию (ASC), значения в окне будут включать все строки текущей группы, у которых значение в столбце HIREDATE меньше значения HIREDATE текущей строки, но не более чем на 100 дней. Если использовать функцию

count(*) over (order by hiredate desc range 100 preceding)

и сортировать фрагмент по убыванию (DESC), базовая логика работы останется той же, но, поскольку группа отсортирована иначе, в окно попадет другой набор строк. В рассматриваемом случае функция найдет все строки, предшествующие текущей, где значение в поле HIREDATE больше значения HIREDATE в текущей строке, но не более чем на 100 дней. Пример поможет это прояснить. Я буду использовать запрос с аналитической функцией FIRST VALUE. Эта функция возвращает вычисленное значение для первой строки окна. Так мы легко сможем понять, где начинается окно: