Работа с диапазонами данных

Данная глава рассказывает об обыденных запросах с участием диапазонов. В повседневной жизни диапазоны встречаются всюду. Например, работа над проектом занимает определенный диапазон времени. В SQL часто возникает необходимость поиска диапазонов, или формирования диапазонов, или любой другой обработки диапазонов данных. Представленные здесь запросы немного сложнее обсуждавшихся в предыдущих главах, но они так же часто используются. Овладев ими в полной мере, вы почувствуете настоящую мощь SQL.

Поиск диапазона последовательных значений Задача

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

select * from V

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

6 16-JAN-2005 17-JAN-2005

7 17-JAN-2005 18-JAN-2005

8 18-JAN-2005 19-JAN-2005

9 19-JAN-2005 20-JAN-2005 10 21-JAN-2005 22-JAN-2005

11 26-JAN-2005 27-JAN-2005

12 27-JAN-2005 28-JAN-2005

13 28-JAN-2005 29-JAN-2005

14 29-JAN-2005 30-JAN-2005

Для всех строк, кроме первой, значение PROJ START (начало проекта) должно быть равным значению PROJ END (конец проекта) предыдущей строки ( предыдущая строка для текущей строки определяется как PROJID - 1). Рассмотрев первые пять строк представления V, можно заметить, что проекты с PROJ ID от 1 до 3 являются частью одной группы , так как для каждого из них PROJ END равняется PROJ START следующей строки. Поскольку требуется найти диапазон дат последовательно выполняющихся проектов, должны быть выбраны все строки, в которых значение PROJ END текущей строки равно значению PROJSTART следующей строки. Если бы представление V состояло только из первых пяти строк, были бы выбраны лишь первые три из них. Необходимо получить следущее результирующее множество (используя все 14 строк представления V):

Строки с PROJID 4, 5, 9, 10 и 14 не вошли в это результирующее множество, потому что их PROJ END не совпадает с PROJ START последующих строк.

Решение

DB2, MySQL, PostgreSQL и SQL Server

Для выявления строк с последовательными значениями используйте рефлексивное объединение:

1 select v1.proj id,

2 v1.proj start,

3 v1.proj end

4 from V v1, V v2

5 where v1.proj end = v2.proj start

Oracle

Предыдущее решение подходит и для Oracle. Привожу альтернативное решение, в котором для проверки значения BEGIN DATE сле-

дующей строки используется оконная функция LEAD OVER, что устраняет необходимость в рефлексивном объединении:

1 select proj id,proj start,proj end

2 from (

3 select proj id,proj start,proj end,

4 lead(proj start)over(order by proj id) next proj start

5 from V

7 where next proj start = proj end

Обсуждение

DB2, MySQL, PostgreSQL и SQL Server

Благодаря объединению представления с самим собой (рефлексивному объединению) каждую строку можно сравнить со всеми остальными строками. Рассмотрим часть результирующего множества для строк со значениями ID 1 и 4:

select v1.proj id as v1 id, v1.proj end as v1 end, v2.proj start as v2 begin,