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

Ловушка в NOT IN (S)

Непродуманное использование связки NOT IN (S) в SQL может приводить к противоречию с интуитивной логикой. Пусть, например, нужно выдать всех сотрудников, имеющих подчиненных. Естественно спросить:

SELECT
   ename 
FROM
   emp
WHERE
   empno IN ( SELECT mgr FROM emp )
;

Пусть теперь нужно выдать сотрудников, не имеющих подчиненных. Если спросить

SELECT
   ename 
FROM
   emp
WHERE
   empno NOT IN ( SELECT mgr FROM emp )
;

то в ответе получим пустое множество. Чтобы понять, в чем дело, достаточно обратить внимание на отсутствие в столбце MGR значения (всегда ровно одного, если только в базе корректно отслеживается древовидная взаимосвязь сотрудников!) и вспомнить способ обработки конструкции NOT IN (S), приводившийся ранее. Для правильного результата строку с отсутствием значения в поле MGR следует отфильтровать:

SELECT
   ename 
FROM
   emp
WHERE
   empno NOT IN ( SELECT mgr FROM emp WHERE mgr IS NOT NULL )
;

Чтобы не впасть в непроизвольную ошибку, рекомендуется избегать конструкции NOT IN (S). В нашем случае более четкой и эффективной в исполнении могла бы оказаться другая запись:

SELECT
    senior.ename
FROM
    emp senior
    LEFT OUTER JOIN
    emp junior
ON  ( senior.empno = junior.mgr )
WHERE
    junior.mgr IS NULL
;

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

Обратите внимание, что по тому же типу можно переписать и прямой запрос:

SELECT DISTINCT
    senior.ename
FROM
    emp senior
    LEFT OUTER JOIN
    emp junior
ON  ( senior.empno = junior.mgr )
WHERE
    junior.mgr IS NOT NULL
;

Оба запроса в этой формулировке различаются (за вычетом необходимости в прямом запросе указать DISTINCT) лишь частицей NOT (что роднит их с соответствующими формулировками на естественном языке), но любопытно, что прямой запрос содержит ее, а обратный — нет!

Упражнение. Предложите еще одну корректную формулировку для прямого и обратного запросов, с использованием EXISTS.

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

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

Сравнение с обычными функциями агрегирования

Многие аналитические функции действуют подобно обычным скалярным функциям агрегирования SUM, MAX и прочим, примененным к группам строк, которые сформированы с помощью GROUP BY. Однако обычные функции агрегирования уменьшают степень детализации, а аналитические функции — нет. Поясняющий сравнительный пример двух запросов:

SELECT   deptno, job, SUM ( sal ) sum_sal
FROM     emp
GROUP BY deptno, job
;
SELECT ename, deptno, job, 
 SUM ( sal ) OVER ( PARTITION BY deptno, job ) sum_sal
FROM emp
;

Результат первого запроса:

    DEPTNO JOB          SUM_SAL
---------- --------- ----------
        10 CLERK           1300   ← одна группа
        10 MANAGER         2450   ← еще одна группа
        10 PRESIDENT       5000   ← еще одна группа
        20 ANALYST         6000   ← и так далее …
        20 CLERK           1900
        20 MANAGER         2975
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
9 rows selected.

Результат второго запроса:

ENAME          DEPTNO JOB          SUM_SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300   ← одна группа
CLARK              10 MANAGER         2450   ← еще одна группа
KING               10 PRESIDENT       5000   ← еще одна группа
SCOTT              20 ANALYST         6000   ← еще одна группа
FORD               20 ANALYST         6000
SMITH              20 CLERK           1900   ← еще одна группа
ADAMS              20 CLERK           1900
JONES              20 MANAGER         2975   ← еще одна группа
JAMES              30 CLERK            950   ← еще одна группа
BLAKE              30 MANAGER         2850   ← еще одна группа
ALLEN              30 SALESMAN        5600   ← еще одна группа
MARTIN             30 SALESMAN        5600
TURNER             30 SALESMAN        5600
WARD               30 SALESMAN        5600
14 rows selected.