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

Здесь для получения трех максимальных зарплат б1ла использована функция DENSE RANK(). Мы присвоили записям непрерывные ранговые значения по столбцу sal и отсортировали результат по убыванию. Если обратиться к описанию функций, оказывается, что при непрерывном ранжировании значения ранга не пропускаются и две строки с одинаковыми значениями получают одинаковый ранг. Поэтому после построения результирующего множества в виде подставляемого представления, можно просто выбирать все строки с плотным рангом не более трех. В результате для каждого отдела будут получены все сотрудники с одной из трех максимальных зарплат в отделе. Для сравнения выберем функцию RANK и сравним, что происходит при обнаружении дублирующихся значений:

scott@TKYTE816> select deptno, ename, sal,

2 dense rank()

3 over (partition by deptno

4 order by sal desc) dr,

5 rank()

6 over (partition by deptno

7 order by sal desc) r

8 from emp

9 order by deptno, sal desc

10 /

DEPTNO ENAME

14 rows selected.

Если бы использовалась функция RANK, сотрудник ADAMS (получивший ранг 4) не вошел бы в результирующее множество, но он - один из сотрудников отдела 20, получивших одну из трех максимальных зарплат, так что в результат он попадать должен. В данном случае использование функции RANK вместо DENSERANK привело бы к неправильному ответу на поставленный вопрос.

Наконец, пришлось использовать подставляемое представление и задать псевдоним DR для результатов аналитической функции dense rank(). Дело в том, что нельзя использовать аналитические функции в конструкциях WHERE или HAVING непосредственно, так что пришлось выбрать результат в представление, а затем отфильтровать,

1062

Глава 12

оставив только необходимые строки. Использование подставляемого представления с условием - типичная конструкция для многих примеров в этой главе.

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

scott@TKYTE816> select *

from (select deptno, ename, sal,

count(*) over (partition by deptno order by sal desc range unbounded preceding)

cnt from emp) where cnt <= 3 order by deptno, sal desc /

9 rows selected.

Этот запрос немного нетривиален. Мы подсчитываем все записи в окне, предшествующие текущей, при сортировке по зарплате. Диапазон RANGE UNBOUNDED PRECEDING задает окно, включающее все записи, зарплата в которых больше или равна зарплате в текущей записи, поскольку сортировка выполнена по убыванию (DESC). Подсчитывая всех сотрудников с такой же или более высокой зарплатой, можно выбирать только строки, в которых значение этого количества (CNT), меньше или равно 3. Обратите внимание, что в отделе 20 для сотрудников SCOTT и FORD возвращается значение 2. Оба они получили наибольшую зарплату в отделе, так что попадают в окно друг для друга. Интересно отметить небольшое отличие, которое дает следующий запрос:

scott@TKYTE816> select *

2 from (select deptno, ename, sal,

3 count(*) over (partition by deptno

4 order by sal desc, ename

5 range unbounded preceding)

6 cnt from emp)

7 where cnt <= 3

8 order by deptno, sal desc

DEPTNO ENAME

10 KING

5000

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

CLARK MILLER

20 FORD SCOTT

JONES

30 BLAKE ALLEN TURNER

9 rows selected.

2450 1300

3000 3000

2975 2850

1600 1500

Обратите внимание, как добавление столбца в конструкцию ORDER BY повлияло на окно. Ранее сотрудники FORD и SCO оба имели в столбце CNT значение 2. Причина в том, что окно строилось исключительно по столбцу зарплаты. Более избирательное окно дает другие результаты функции COUNT. Я привел этот пример, чтобы подчеркнуть, что функция окна зависит от обеих конструкций, ORDER BY и RANGE. Если фрагмент сортировался только по зарплате, строка для сотрудника FORD предшествовала строке для SCOTT, когда строка для SCOTT была текущей, а строка для SCOTT, в свою очередь, предшествовала строке для FORD, когда та б1ла текущей. Только при сортировке по столбцам SAL и ENAME можно однозначно упорядочить строки для сотрудников SCOTT и FORD по отношению друг к другу.

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

scott@TKYTE816> update emp set sal = 99 where deptno = 30;

б rows updated.

scott@TKYTE816> select *

2 from (select deptno, ename, sal,

3 count(*) over (partition by deptno

4 order by sal desc

5 range unbounded preceding)

6 cnt from emp)

7 where cnt <= 3

8 order by deptno, sal desc

DEPTNO ENAME

10 KING CLARK MILLER

20 SCOTT FORD JONES

5000

2450 1300

3000 3000

6 rows selected.

Теперь строк для отдела 30 в отчете нет, поскольку 6 сотрудников этого отдела имеют одинаковую зарплату. В поле CNT для всех них находится значение 6, которое никак не меньше или равно 3.