Внешнее объединение при использовании агрегатных функций

Задача

Задача аналогична задаче раздела Осуществление объединений при использовании агрегатных функций , но таблица EMP BONUS изменена. В данном случае премии получали не все служащие 10-го отдела. Рассмотрим таблицу EMP BONUS и запрос, обеспечивающий (якобы) вычисление суммы заработных плат и суммы премий всех служащих 10-го отдела:

select * from emp bonus

EMPNO RECEIVED TYPE

7934 17-MAR-2005 1

7934 15-FEB-2005 2

select deptno,

sum(sal) as total sal,

sum(bonus) as total bonus from ( select e.empno,

e.ename,

e.sal,

e.deptno,

e.sal*case when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end as bonus from emp e, emp bonus eb where e.empno = eb.empno and e.deptno = 10 )

group by deptno

DEPTNO TOTAL SAL TOTAL BONUS

10 2600 390

Результат в TOTAL BONUS верен, а значение, возвращенное для TO-TAL SAL, не является суммой заработных плат всех служащих 10-го отдела. Следующий запрос показывает, почему значение TOTAL SAL ошибочно:

select e.empno, e.ename, e.sal, e.deptno,

e.sal*case when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end as bonus from emp e, emp bonus eb

where e.empno = eb.empno and e.deptno = 10

Вместо вычисления суммы всех заработных плат 10-го отдела суммируется только заработная плата служащего MILLER, и ошибочно она суммируется дважды. В конце концов необходимо было получить такое результирующее множество:

DEPTNO TOTAL SAL TOTAL BONUS

8750

Решение

Решение аналогично решению, представленному в разделе Осуществление объединений при использовании агрегатных функций , но в данном случае осуществляется внешнее объединение с EMP BONUS, что гарантирует включение всех служащих 10-го отдела.

DB2, MySQL, PostgreSQL, SQL Server

Осуществляем внешнее объединение с EMP BONUS, затем суммируем только уникальные заработные платы 10-го отдела:

Можно также использовать оконную функцию SUM OVER:

1 select distinct deptno,total sal,total bonus

2 from (

3 select e.empno,

4 e.ename,

5 sum(distinct e.sal) over

(partition by e.deptno) as total sal, e.deptno,

sum(e.sal*case when eb.type is null then 0 when eb.type = 1 then .1 when eb.type = 2 then .2 else .3

(partition by deptno) as total bonus

При работе с Oracle 9i Database или более поздними версиями можно использовать предыдущее решение. В качестве альтернативы применяется собственный синтаксис Oracle для внешнего объединения; этот вариант является единственно возможным для пользователей Oracle 8i Database и более ранних версий:

Пользователи Oracle 8i Database могут также применить решение с функцией SUM OVER, приведенное для DB2 и других баз данных, но в него необходимо внести изменения с использованием собственного синтаксиса Oracle для внешнего объединения, показанного в предыдущем запросе.

Обсуждение

Второй запрос в разделе Задача данного рецепта объединяет таблицы EMP и EMP BONUS и возвращает только строки для служащего MILLER, что и является причиной ошибки при вычислении суммы EMP.SAL (остальные служащие 10-го отдела не получали премий, и их заработные платы не вошли в сумму). Решение - провести внеш-