Некоторые примеры составления запросов. Дополнительные сведения об аналитических функциях

Некоторые примеры составления запросов

Известное известно лишь немногим, а успех имеет одинаковый у всех.

Аристотель, "Поэтика"

Запрос первых N записей

Запросы о "первых N записях" известны еще по реляционной теории БД, где они именовались "запросами с квотой", то есть, в переводе с латинского, с указанием ограничения объема возвращаемого результата. Такие запросы достаточно распространены в приложениях. Заметьте однако, что их смысл отличается от запросов с неполной выборкой строк из таблиц-источников, оформляемых в SQL с помощью конструкции SAMPLE после имени таблицы. Примеры запросов с пробной выборкой приводились в обсуждении предложения SELECT ранее.

Вопрос к БД

Примеры постановки вопроса. Имеющиеся данные:

ENAME            SAL
---------- ---------
SMITH            800
ALLEN           1600
WARD            1250
JONES           2975
MARTIN          1250
BLAKE           2850
CLARK           2450
SCOTT           3000
KING            5000
TURNER          1500
ADAMS           1100
JAMES            950
FORD            3000
MILLER          1300

Выдать пять сотрудников с наибольшими окладами. Должно быть:

ENAME            SAL
---------- ---------
KING            5000
SCOTT           3000
FORD            3000
JONES           2975
BLAKE           2850

Выдать пять сотрудников с наименьшими окладами. Должно быть:

ENAME            SAL
---------- ---------
SMITH            800
JAMES            950
ADAMS           1100
MARTIN          1250
WARD            1250

Выдать сотрудников с пятью наибольшими окладами. Должно быть:

ENAME            SAL
---------- ---------
KING            5000
SCOTT           3000
FORD            3000
JONES           2975
BLAKE           2850
CLARK           2450

"Очевидное", но неправильное решение

SELECT   ename, sal 
FROM     emp
WHERE    ROWNUM <= 5 
ORDER BY sal DESC
;

Возможный ответ:

ENAME            SAL
---------- ---------
KING            5000
SCOTT           3000
CLARK           2450
MILLER          1300
JAMES            950

Фраза WHERE, порождающая значения для ROWNUM, обрабатывается до ORDER BY. Поэтому приведенный выше запрос на деле узнает множество произвольных пяти сотрудников и предъявляет их в упорядоченном виде.

Правильные решения

Приводимое ниже решение годится для всех версий Oracle. SQL дает более одного способа строить такие запросы, но они могут иметь разные планы исполнения.

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

SELECT *
FROM ( SELECT ename, sal FROM emp ORDER BY sal DESC )
WHERE ROWNUM <= 5
;

Запрос, оформленный по такому шаблону, дает единственный пример оправданости упорядочения строк в подзапросе — во всех остальных случаях упорядочение в подзапросе бессмысленно. Неочевидно, что эта оправданость — логическая, а на деле Oracle будет делать так, как только что было сказано. Подобная формулировка дает очередное красноречивое доказательство отличия SQL от естественного языка с одной стороны, а с другой —несовпадения того, что записано, с тем, что выполняется в действительности.

Когда такие запросы устроены более сложно, как это бывает в жизни, их удобно оформлять с вынесением подзапроса во фразу WITH, на манер следующего:

WITH elist AS
     ( SELECT ename, sal FROM emp ORDER BY sal DESC )
SELECT * 
FROM   elist 
WHERE  ROWNUM <= 5
;

Другой схожий пример оформления текста запроса встречался ранее.

Когда в таких запросах желательно иметь в ответе не только строки, но и их расположение в N-ке, во фразу SELECT основного запроса следует добавить выдачу ROWNUM. Легко заметить, что в нашем примере SCOTT и FORD получат при этом разные номера, хотя критерий их отбора, величина зарплаты, у них одинаков. Если в квотированном запросе требуется не пронумеровать строки, а выдать ранг (от немецкого Rang — класс, чин, разряд), потребуется воспользоваться аналитическими функциями RANK или DENSE_RANK, например:

WITH elist AS
     ( SELECT ename, sal, RANK ( ) OVER ( ORDER BY sal DESC ) FROM emp )
SELECT * 
FROM   elist 
WHERE  ROWNUM <= 5
;

Хотя здесь имеется обращение к аналитической функции, Oracle, обнаружив, что в окончательный ответ поступит ограниченное множество строк (WHERE ROWNUM <= 5), так же не будет тратить время на общее ранжирование, а будет вместо этого просматривать таблицу, выполняя по ходу дела минимум вычислений над небольшим массивом в оперативной памяти для очередной строки.

Упражнение. Построить запрос на выдачу сотрудников с пятой по счету сверху зарплатой.