4 05-JAN-2005 26-JAN-2005 11

4 05-JAN-2005 27-JAN-2005 12

4 05-JAN-2005 28-JAN-2005 13

4 05-JAN-2005 29-JAN-2005 14

Из этого результирующего множества видно, почему строка с PROJ ID 1 вошла в окончательное результирующее множество, а строка с PROJ ID 4 нет: для V1 ID 4 нет такой строки, в которой значение V2 BEGIN соответствовало бы значению V1 END.

Но в зависимости от того, как рассматриваются данные, PROJID 4 может быть включен в результирующее множество. Рассмотрим следующее результирующее множество:

select * from V where proj id <= 5

PROJ ID PROJ START PROJ END

1 01-JAN-2005 02-JAN-2005

2 02-JAN-2005 03-JAN-2005

3 03-JAN-2005 04-JAN-2005

4 04-JAN-2005 05-JAN-2005

5 06-JAN-2005 07-JAN-2005

Если последовательность образуют проекты, которые начинаются в день окончания другого проекта, PROJ ID 4 должен быть включен в результирующее множество. Изначально PROJ ID 4 был исключен из-за сравнения со следующей строкой (сравнивалось значение PROJ END текущей строки со значением PROJ START из следующей). Но при сравнении с предыдущей строкой (сравнивается значение PROJ START текущей строки со значением PROJ END из предыдущей) PROJ ID 4 будет включен в результат.

Изменить решение так, чтобы PROJ ID 4 был включен в результирующее множество, просто: требуется ввести дополнительный предикат, обеспечивающий проверку не только значения PROJ END, но и PROJ START. Внесенные изменения, которые демонстрирует следующий запрос, обеспечивают создание результирующего множества, включающего PROJID 4 (ключевое слово DISTINCT необходимо, поскольку некоторые строки удовлетворяют обоим условиям):

select distinct

v1.proj id, v1.proj start, v1.proj end from V v1, V v2 where v1.proj end = v2.proj start or v1.proj start = v2.proj end

PROJ ID PROJ START PROJ END

1 01-JAN-2005 02-JAN-2005

2 02-JAN-2005 03-JAN-2005

3 03-JAN-2005 04-JAN-2005

4 04-JAN-2005 05-JAN-2005

Oracle

Несомненно, рефлексивное объединение выполняет поставленную задачу, но идеальным решением проблем такого типа является использование оконной функции LEAD OVER.1 Она позволяет сравнивать строки без рефлексивного объединения (хотя для этого ей требуется упорядоченное результирующее множество). Рассмотрим результаты вложенного запроса (строки 3-5) для ID 1 и 4:

select * from (

select proj id,proj start,proj end,

lead(proj start)over(order by proj id) next proj start from v )

where proj id in ( 1,4 )

PROJ ID PROJ START PROJ END NEXT PROJ START

1 01-JAN-2005 02-JAN-2005 02-JAN-2005 4 04-JAN-2005 05-JAN-2005 06-JAN-2005

После анализа приведенного выше фрагмента кода и результирующего множества становится понятным, почему PROJ ID 4 не включен в окончательное результирующее множество решения: потому что его значение PROJEND (05-JAN-2005) не совпадает с датой начала следующего проекта (06-JAN-2005).

Функция LEAD OVER исключительно полезна при решении подобных задач, особенно при рассмотрении частичных результатов. При работе с оконными функциями необходимо помнить, что они выполняются после операторов FROM и WHERE. Таким образом, функция LEAD OVER в приведенном выше запросе должна располагаться во вложенном запросе. В противном случае LEAD OVER применяется к результирующему множеству уже после того, как предикат отсеет все строки, кроме строк с PROJ ID 1 и 4.

Теперь в зависимости от того, как рассматриваются данные, может потребоваться включить проект с PROJ ID 4 в окончательное результирующее множество. Рассмотрим первые пять строк представления V:

select * from V

Не совсем корректно говорить о функции LEAD OVER, т. к. функцией является только LEAD, а OVER - это связанное с ней аналитическое выражение. - Примеч. науч. ред.

where proj id <= 5

PROJ ID PROJ START PROJ END

1 01-JAN-2005 02-JAN-2005

2 02-JAN-2005 03-JAN-2005

3 03-JAN-2005 04-JAN-2005

4 04-JAN-2005 05-JAN-2005

5 06-JAN-2005 07-JAN-2005

Если предъявляемые требования таковы, что проект с PROJ ID 4 считается последовательным (потому что его PROJ START соответствует PROJ END проекта PROJ ID 3), и условиям не удовлетворяет только проект PROJ ID 5, предлагаемое для данного рецепта решение неправильное (!) или, по крайней мере, неполное:

select proj id,proj start,proj end from (

select proj id,proj start,proj end,

lead(proj start)over(order by proj id) next start from V where proj id <= 5 )

where proj end = next start

PROJ ID PROJ START PROJ END

1 01-JAN-2005 02-JAN-2005

2 02-JAN-2005 03-JAN-2005

3 03-JAN-2005 04-JAN-2005

Если проект с PROJ ID 4 должен быть включен, просто добавьте в запрос LAG OVER и используйте дополнительный фильтр в предикате

WHERE:

select proj id,proj start,proj end from (

select proj id,proj start,proj end,

lead(proj start)over(order by proj id) next start, lag(proj end)over(order by proj id) last end from V where proj id <= 5 )

where proj end = next start or proj start = last end

PROJ ID PROJ START PROJ END

1 01-JAN-2005 02-JAN-2005

2 02-JAN-2005 03-JAN-2005

3 03-JAN-2005 04-JAN-2005

4 04-JAN-2005 05-JAN-2005