Теперь проект с PROJ ID 4 включен в окончательное результирующее множество, и только проект с PROJ ID 5 не вошел в него. Применяя эти рецепты в своем коде, внимательно изучайте предъявляемые требования.

Вычисление разности между значениями строк одной группы или сегмента

Задача

Требуется выбрать для каждого служащего DEPTNO, ENAME и SAL, а также найти разность между значениями SAL для служащих одного отдела (т. е. для служащих с одинаковыми значениями DEPTNO). Разность должна вычисляться между каждым служащим и служащим, который был принят на работу сразу после него (необходимо выяснить, есть ли зависимость между стажем работы и заработной платой для каждого отдела). Для служащих, которые были приняты на работу позже всех в своих отделах, возвращается значение N/A . Должно быть получено следующее результирующее множество:

Решение

Данная задача - еще один пример, когда пригодятся оконные функ-ции1 Oracle LEAD OVER и LAG OVER. Они обеспечивают возможность доступа к последующей и предыдущей строкам без дополнительных объединений. Для остальных СУБД можно использовать скалярные подзапросы, хотя они усложняют решение. Эта конкретная задача теряет всю свою прелесть, когда приходится решать ее с помощью скалярных подзапросов и рефлексивных объединений.

В документации по Oracle эти функции также называются аналитическими. - Примеч. науч. ред.

DB2, MySQL, PostgreSQL и SQL Server

Для получения значения HIREDATE для служащего, принятого на работу следующим после рассматриваемого служащего, используйте скалярный подзапрос. Затем с помощью другого скалярного подзапроса найдите его заработную плату:

1 select deptno,ename,hiredate,sal,

2 coalesce(cast(sal-next sal as char(10)),N/A) as diff

3 from (

4 select e.deptno,

5 e.ename,

6 e.hiredate,

7 e.sal,

8 (select min(sal) from emp d

9 where d.deptno=e.deptno

10 and d.hiredate =

11 (select min(hiredate) from emp d

12 where e.deptno=d.deptno

13 and d.hiredate > e.hiredate)) as next sal

14 from emp e

15 ) x

Oracle

Чтобы получить заработную плату служащего, следующего по отношению к текущей строке, используйте оконную функцию LEAD OVER:

1 select deptno,ename,sal,hiredate,

2 lpad(nvl(to char(sal-next sal),N/A),10) diff

3 from (

4 select deptno,ename,sal,hiredate,

5 lead(sal)over(partition by deptno

6 order by hiredate) next sal

7 from emp

Обсуждение

DB2, MySQL, PostgreSQL и SQL Server

Первый шаг - с помощью скалярного подзапроса найти значение HIRE-DATE служащего, принятого на работу в тот же отдел сразу после рассматриваемого служащего. В скалярном подзапросе используется функция MIN(HIREDATE), которая обеспечивает возвращение только одного значения, даже если в один день было нанято несколько человек:

select e.deptno, e.ename, e.hiredate, e.sal,

(select min(hiredate) from emp d where e.deptno=d.deptno

and d.hiredate > e.hiredate) as next hire

Следующий шаг - с помощью другого скалярного подзапроса найти заработную плату служащего, принятого на работу в день, соответствующий дате NEXT HIRE. Опять же в решении используется функция MIN, что гарантирует возвращение одного значения:

select e.deptno, e.ename, e.hiredate, e.sal,

(select min(sal) from emp d where d.deptno=e.deptno and d.hiredate =

(select min(hiredate) from emp d where e.deptno=d.deptno

and d.hiredate > e.hiredate)) as next sal

from emp e order by 1

DEPTNO ENAME

HIREDATE

SAL NEXT SAL