больше единицы. Ранжирование выполняется согласно размеру заработной платы (это произвольный выбор; использовать значения столбца SAL удобно, но точно так же можно было бы выбрать и столбец EMP-NO). Теперь, когда мы знаем общее количество дубликатов и упорядочили их, расстояние до записи MILLER - это просто общее число дубликатов минус ранг текущей записи плюс один (CNT - RN + 1). Результаты вычисления расстояния и выполнения LEAD OVER показаны ниже:

select deptno,ename,sal,hiredate,

lead(sal)over(partition by deptno

order by hiredate) incorrect,

cnt-rn+1 distance,

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

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 )

Сейчас можно ясно видеть эффект от передачи в LEAD OVER второго параметра. Столбец INCORRECT (неправильно) представляет значения, возвращаемые LEAD OVER при реализации поведения по умолчанию (сравнения со значением следующей строки). Столбец CORRECT (правильно) представляет значения, возвращаемые LEAD OVER при использовании расстояния для каждого служащего с дублирующимся HIREDATE до строки служащего MILLER. На данном этапе осталось лишь найти разность между значениями CORRECT и SAL для каждой строки, что уже было показано.

Определение начала и конца диапазона последовательных значений

Задача

Данный рецепт является расширением предыдущего и использует то же представление V. Теперь, когда диапазоны последовательных зна-

требуется получить такое результирующее множество:

PROJ GRP PROJ START PROJ END

1 01-JAN-2005 05-JAN-2005

2 06-JAN-2005 07-JAN-2005

3 16-JAN-2005 20-JAN-2005

4 21-JAN-2005 22-JAN-2005

5 26-JAN-2005 30-JAN-2005

Решение

Эта задача несколько сложнее предыдущей. Во-первых, необходимо найти диапазоны. Диапазон строк определяется значениями PROJ START и PROJEND. Чтобы строка считалась последовательной или частью группы, ее значение PROJ START должно быть равным значению PROJ END предыдущей строки. В случае, когда значение PROJ START строки не равно значению PROJ END предыдущей строки и ее значение PROJ END не равно значению PROJ START следующей строки, строка является экземпляром группы, состоящей из одной строки. После определения диапазонов необходимо сгруппировать строки в этих диапазонах (в группы) и возвратить только их начальные и конечные точки.

Рассмотрим первую строку требуемого результирующего множества.

Значение PROJ START - это PROJ START строки PROJ ID 1 пред-

чений определены, требуется найти всего лишь начало и конец этих диапазонов. В отличие от рецепта выше, если строки не входят в множество последовательных значений, они все равно должны быть возвращены. Почему? Потому что именно такие строки и представляют начало и конец диапазонов. Используя данные представления V:

При использовании V2 порядок решения такой: сначала находим строки, составляющие множество последовательных значений, затем группируем эти строки, после чего с помощью функций MIN и MAX находим начальную и конечную точки:

1 select proj grp,

2 min(proj start) as proj start,

ставления V, и PROJEND - это PROJEND строки PROJID 4 представления V. Хотя за значением строки PROJ ID 4 не следует никакого последовательного значения, это последнее значение диапазона последовательных значений, таким образом, оно включено в первую группу.

DB2, MySQL, PostgreSQL и SQL Server

В решении для этих платформ используется представление V2, чтобы сделать код более понятным. Представление V2 определено следущим образом:

create view v2 as

select a.*, case when (

select b.proj id

from V b where a.proj start = b.proj end )

is not null then 0 else 1 end as flag from V a

Результирующее множество представления V2:

select * from V2