Обсуждение

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

select ename, sal,

dense rank()over(order by sal desc) rnk from emp

Как видно из приведенного выше результирующего множества, вложенный запрос Х просто ранжирует записи служащих по SAL, допуская при этом наличие одинаковых значений рангов для дублирующихся значений (поскольку используется функция DENSE RANK, а не RANK, получаем непрерывную последовательность значений рангов). Следующий шаг - сгруппировать строки, возвращенные вложенным запросом Х, применяя выражения CASE для обработки результатов, возвращаемых DENSE RANK. Кроме того, используем ранжирующую функцию ROW NUMBER OVER и ранжируем служащих по SAL в их группах (в рамках групп, созданных выражением CASE). Все это происходит во вложенном запросе Y и показано ниже:

Теперь запрос начинает вырисовываться. Если проследить его с самого начала (от вложенного запроса Х), можно увидеть, что он не так уж и сложен. На данный момент запрос возвращает всех служащих и для каждого из них заработную плату (SAL), ранг (RNK), представляющий уровень заработной платы служащего по сравнению со всеми остальными сотрудниками, номер группы (GRP), обозначающий, к какой группе принадлежит служащий (на основании SAL), и, наконец, ранг в группе (GRP RANK), определяемый на основании значения SAL в рамках отдельной GRP.

Теперь выполним традиционное разворачивание по ENAME, присоединяя при этом значения SAL оператором конкатенации Oracle . Функция RPAD обеспечивает выравнивание числовых значений в круглых скобках. Наконец, применяем GROUP BY по GRPRNK, чтобы в ре-

select ename, sal, rnk,

case when rnk <= 3 then 1

when rnk <= 6 then 2

else 3

end grp,

row number()over ( partition by case when rnk <= 3 then 1 when rnk <= 6 then 2 else 3

order by sal desc, ename ) grp rnk from ( select ename, sal,

dense rank()over(order by sal desc) rnk from emp ) x

зультирующем множестве гарантированно присутствовали все служащие. Окончательное результирующее множество показано ниже:

select max(case grp when 1 then rpad(ename,6)

( sal ) end) top 3, max(case grp when 2 then rpad(ename,6)

( sal ) end) next 3, max(case grp when 3 then rpad(ename,6)

( sal ) end) rest

from ( select ename, sal, rnk,

case when rnk <= 3 then 1

when rnk <= 6 then 2

else 3

end grp,

row number()over ( partition by case when rnk <= 3 then 1 when rnk <= 6 then 2 else 3

order by sal desc, ename ) grp rnk from ( select ename, sal,

dense rank()over(order by sal desc) rnk from emp ) x ) y

group by grp rnk

TOP 3 NEXT 3 REST

KING (5000) BLAKE (2850) TURNER (1500) FORD (3000) CLARK (2450) MILLER (1300) SCOTT (3000) ALLEN (1600) MARTIN (1250) JONES (2975) WARD (1250)

ADAMS (1100)

JAMES (950)

SMITH (800)

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