Хотя в подавляющем большинстве рецептов, предлагаемых в данной книге, сценарии что если не рассматриваются (из соображений удобства для чтения и с целью сохранения психического здоровья автора), сценарий с участием дублирующихся значений при таком использовании функции Oracle LEAD OVER требует особого внимания. В простых данных таблицы EMP, используемых нами для примера, ни для одного служащего значения столбца HIREDATE не дублируются, тем не менее такая возможность весьма вероятна. В обычной ситуации я бы не обсуждал обработку дубликатов (поскольку таких значений нет в таблице EMP), но не всем (особенно тем, кто не работает с Oracle) может быть понятна методика с использованием функции LEAD. Рассмотрим следующий запрос, возвращающий разность заработных плат для служащих 10-го отдела (разности вычисляются в порядке приема служащих на работу):

select deptno,ename,sal,hiredate,

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

select deptno,ename,sal,hiredate,

lead(sal)over(partition by deptno

order by hiredate) next sal

from emp where deptno=10 and empno > 10 )

DEPTNO ENAME SAL HIREDATE DIFF

10 CLARK 2450 09-JUN-1981 -2550 10 KING 5000 17-NOV-1981 3700 10 MILLER 1300 23-JAN-1982 N/A

Здесь все правильно, если рассматривать данные таблицы EMP. Но если бы в таблице присутствовали строки с дублирующимися значениями, решение дало бы сбой. Посмотрим на пример ниже, в котором в таблицу вносятся данные о еще четырех служащих, которые были приняты на работу в один день со служащим KING:

insert into emp (empno,ename,deptno,sal,hiredate) values (1,ant,10,1000,to date(17-NOV-1981))

insert into emp (empno,ename,deptno,sal,hiredate) values (2,joe,10,1500,to date(17-NOV-1981))

Как мы видим, за исключением служащего JON, для всех служащих, принятых в один день (17 ноября), разность заработных плат вычисляется относительно другого служащего, который был нанят в тот же день! Это неправильно. Заработная плата всех служащих, которые устроились 17 ноября, должна сравниваться с заработной платой служащего MILLER. Рассмотрим, например, служащего ANT. Значение столбца DIFF для ANT равно -500, потому что его значение SAL сравнивается с SAL служащего JOE и оно на 500 меньше, чем заработная плата JOE, отсюда получаем -500. Значение DIFF служащего ANT должно составлять -300, поскольку ANT получает на 300 денежных единиц меньше, чем MILLER, служащий, принятый на работу следующим, согласно HIREDATE. Такие ошибки возникают из-за стандартного поведения Oracle-функции LEAD OVER. По умолчанию LEAD OVER проверяет только следующую строку. И для служащего ANT, исходя из HIREDA-TE, следующим значением SAL является SAL служащего JOE, потому что LEAD OVER просто берет следующую строку и не пропускает дубликаты. К счастью, Oracle предусматривает такую ситуацию: в LEAD OVER может быть передан дополнительный параметр, определяющий, как далеко вперед должна заглянуть функция. В приведенном выше примере вопрос стоит лишь в определении того, на сколько строк отстоит запись каждого служащего, принятого на работу 17 ноября, от записи, соответствующей 23 января (дате приема на работу служащего MILLER). Решение ниже показывает, как это реализовывается:

select deptno,ename,sal,hiredate,

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

insert into emp (empno,ename,deptno,sal,hiredate) values (3,jim,10,1600,to date(17-NOV-1981))

insert into emp (empno,ename,deptno,sal,hiredate) values (4,jon,10,1700,to date(17-NOV-1981))

select deptno,ename,sal,hiredate,

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

select deptno,ename,sal,hiredate,

lead(sal)over(partition by deptno

order by hiredate) next sal

from emp where deptno=10 )

from (

select deptno,ename,sal,hiredate,

lead(sal,cnt-rn+1)over(partition by deptno

order by hiredate) next sal

from (

select deptno,ename,sal,hiredate,

count(*)over(partition by deptno,hiredate) cnt, row number()over(partition by deptno,hiredate order by sal) rn from emp where deptno=10 ) )

Теперь все правильно. Как видите, заработные платы всех служащих, принятых 17 ноября, сравниваются с заработной платой MILLER. В результате для служащего ANT значение DIFF равно -300, что и требовалось получить. Может быть, не все поняли, но переданное в LEAD OVER выражение CNT - RN + 1 - это просто количество строк между записями каждого служащего, поступившего на работу 17 ноября, и служащего MILLER. Рассмотрим вложенный запрос, приведенный ниже, который демонстрирует значения CNT и RN:

select deptno,ename,sal,hiredate,

count(*)over(partition by deptno,hiredate) cnt, row number()over(partition by deptno,hiredate order by sal) rn

Значение CNT для каждого служащего показывает, сколько в таблице служащих с таким значением HIREDATE. Значение RN представляет ранг служащего. Ранги подразделяются по DEPTNO и HIREDATE, поэтому только служащие с дублирующимся HIREDATE могут иметь ранг