следующим, но не в один день с рассматриваемым служащим, в качестве альтернативы можно использовать следующее решение:

select ename, sal, hiredate from (

select ename, sal, hiredate,

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

select ename,sal,hiredate,

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

where sal < next sal

Основная идея данного решения - найти, на сколько строк текущая строка отстоит от строки, с которой должна сравниваться. Например, если имеется пять дубликатов, первый из пяти должен сравниваться со строкой, отстоящей от него на пять строк. Значение CNT для каждого служащего с дублирующимся значением HIREDATE представляет общее количество дубликатов данного HIREDATE. Значение RN представляет ранг служащего 10-го отдела (DEPTNO 10). Ранги подразделяются по HIREDATE, поэтому только для служащих с дублирующимися HIREDATE это значение может быть больше 1. Сортируются ранги по EMPNO (это выбрано произвольно). Теперь, когда известно общее количество дублирующихся значений и для каждого их них определен ранг, чтобы найти расстояние до следующего значения HIRE-DATE, необходимо просто из общего числа дубликатов вычесть ранг текущего и прибавить единицу (CNT - RN + 1).

См. также

Дополнительные примеры использования LEAD OVER при наличии дублирующихся значений (и более подробное обсуждение приведенной выше техники) представлены в главе 8 в разделе Определение интервала времени в днях между текущей и следующей записями и в главе 10 в разделе Вычисление разности между значениями строк одной группы или сегмента .

Смещение значений строк Задача

Требуется выбрать имя и заработную плату каждого служащего, а также наибольшую и наименьшую заработные платы служащих, зарабатывающих соотвественно меньше и больше рассматриваемого. Граничные значения заработных плат замыкаются (первым значением SAL указывается последнее значение SAL и наоборот). Должно быть получено следующее результирующее множество:

Решение

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

DB2, SQL Server, MySQL и PostgreSQL

С помощью скалярного подзапроса для каждой заработной платы найдите следующую и предыдущую:

1 select e.ename, e.sal,

2 coalesce(

3 (select min(sal) from emp d where d.sal > e.sal),

4 (select min(sal) from emp)

5 ) as forward,

6 coalesce(

7 (select max(sal) from emp d where d.sal < e.sal),

8 (select max(sal) from emp)

9 ) as rewind

10 from emp e

11 order by 2

Oracle

С помощью оконных функций LAG OVER и LEAD OVER организуйте доступ к предыдущей и следующей строкам относительно текущей строки:

1 select ename,sal,

2 nvl(lead(sal)over(order by sal),min(sal)over()) forward,

3 nvl(lag(sal)over(order by sal),max(sal)over()) rewind

4 from emp

Обратите внимание, что REWIND равно NULL для служащего SMITH и FORWARD равно NULL для служащего KING. Это объясняется тем, что эти два служащих имеют наименьшую и наибольшую заработные платы соответственно. Требование, предъявленное в разделе Задача , гласит, что в случае существования в столбцах FORWARD или REWIND значений NULL результаты должны замыкаться , т. е. для наибольшего значения SAL в FORWARD должно быть помещено наименьшее значение SAL таблицы и для наименьшего значения SAL в REWIND должно быть помещено наибольшее значение SAL таблицы. Оконные функции MIN OVER и MAX OVER, если для них не заданы сегмент или окно (т. е. за оператором OVER следуют пустые круглые

Обсуждение

DB2, SQL Server, MySQL и PostgreSQL

Использование скалярного подзапроса не обеспечивает полного решения данной задачи. Если любые две записи будут содержать одинаковые значения SAL, данное приближение даст сбой, но это лучшее, что можно сделать в отсутствие оконных функций.

Oracle

Оконные функции LAG OVER и LEAD OVER будут (по умолчанию и если не определено ничего другого) возвращать значения предыдущей и последующей строк соответственно. Порядок строк определяется частью ORDER BY конструкции OVER. Если посмотреть на решение, на первом шаге возвращаются следующая и предыдущая строки для текущей строки, причем строки упорядочены по SAL:

select ename,sal,

lead(sal)over(order by sal) forward, lag(sal)over(order by sal) rewind from emp