Значения столбца DIFF для четырех из пяти служащих, поступивших на работу в один день, равны нулю. Это неправильно. Даты найма на работу всех служащих, принятых в один день, должны сравниваться с одним следующим по отношению к ним значением HIREDATE, т. е. для всех служащих, нанятых 17 ноября, вычисления должны проводиться относительно значения HIREDATE служащего MILLER. В дан-

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

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

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

select ename, hiredate

from emp where deptno=10 order by 2

ENAME HIREDATE

CLARK 09-JUN-1981

ant 17-NOV-1981

]oe 17-NOV-1981

KING 17-NOV-1981

]im 17-NOV-1981

choi 17-NOV-1981 MILLER 23-JAN-1982

Теперь в 10-м отделе несколько служащих, которые были приняты на работу в один день. Если к данному результирующему множеству применить предложенное решение (с переносом фильтра во вложенный запрос, чтобы рассматривались только служащие с DEPTNO 10 и их значения HIREDATE), то будет получено следующее:

select ename, hiredate, next hd, next hd - hiredate diff from (

select deptno, ename, hiredate,

lead(hiredate)over(order by hiredate) next hd from emp where deptno=10 )

Теперь получен правильный результат. Даты приема на работу (значения столбца HIREDATE) всех служащих, принятых в один день, сравниваются со следующей по хронологии датой (другим значением HI-REDATE), а не с таким же значением HIREDATE. Если решение этой задачи не вполне понятно, просто разложите запрос на составляющие. Начнем с вложенного запроса:

select deptno,ename,hiredate,

count(*)over(partition by hiredate) cnt,

ном случае проблема в том, что функция LEAD упорядочивает строки по столбцу HIREDATE, но не пропускает дубликаты. Таким образом, например, при сравнении значений HIREDATE служащих ANT и JOE разница получается равной нулю, т. е. значение столбца DIFF для ANT - нуль. К счастью, Oracle предоставил простой выход из подобных ситуаций. При вызове функции LEAD в нее можно передать аргумент, точно определяющий местоположение строки для сравнения (т. е. будет ли это следующая строка, 10-я после рассматриваемой строка и т. д.). Таким образом, для служащего ANT мы проводим сравнение не со следующей строкой, а заглядываем на пять строк вперед (перешагиваем через все остальные дубликаты), на строку служащего MILLER. Строка MILLER отстоит от строки служащего JOE на четыре строки; от JIM - на три строки; от KING - на две; и красавчик CHOI находится на предыдущей относительно MILLER строке. Чтобы получить правильный ответ, просто передаем в функцию LEAD в качестве аргумента расстояние от строки каждого служащего до строки служащего MILLER. Решение показано ниже:

select ename, hiredate, next hd, next hd - hiredate diff from (

select deptno, ename, hiredate,

lead(hiredate,cnt-rn+1)over(order by hiredate) next hd

from (

select deptno,ename,hiredate,

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

Оконная функция COUNT OVER (пересчитать) подсчитывает, сколько раз встречается каждое из значений HIREDATE, и возвращает это количество для каждой строки. Для строк с дублирующимися значениями HIREDATE возвращается значение 5. Ранжирующая функция ROW NUMBER OVER ранжирует каждого служащего по столбцу EMPNO. Ранги разделяются по HIREDATE, т. е. если значение HIRE-DATE не дублируется, служащий получает ранг 1. Если значение HI-REDATE дублируется, все дубликаты пересчитываются и получают соответствующий ранг. Их ранг может использоваться для вычисления расстояния до следующего значения HIREDATE (HIREDATE служащего MILLER). Это можно увидеть, вычитая RN из CNT и прибавляя 1 для каждой строки при вызове LEAD:

select deptno, ename, hiredate,

cnt-rn+1 distance to miller,

lead(hiredate,cnt-rn+1)over(order by hiredate) next hd from (

select deptno,ename,hiredate,

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

Как видите, при получении соответствующего аргумента (количества строк до строки для сравнения) функция LEAD обеспечивает верное сравнение дат.