Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 6784 / 673 | Оценка: 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'));

И сколько строк он все таки вернет
Ладисьяно Писеев
Ладисьяно Писеев
Россия