Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7083 / 760 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 14:

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

< Лекция 13 || Лекция 14: 12345

Ловушка в 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.
< Лекция 13 || Лекция 14: 12345
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Анатолий Федоров
Анатолий Федоров
Россия, Москва, Московский государственный университет им. М. В. Ломоносова, 1989
Дмитрий Квашнёв
Дмитрий Квашнёв
Россия, Коломна, Московский государственный открытый университет, 2001