10 11

Oracle

max(proj end) as proj end from (

select a.proj id,a.proj start,a. proj end, (select sum(b.flag) from V2 b

where b.proj id <= a.proj id) as proj grp from V2 a ) x

group by proj grp

Хотя решения для других СУБД подойдут и для Oracle, использование оконной функции Oracle LAG OVER устраняет необходимость вводить дополнительные представления. С помощью LAG OVER сравниваем значения PROJ END предыдущей строки со значением PROJ START текущей строки и распределяем строки по группам. Сформировав группы, находим их граничные значения с помощью функций MIN и MAX:

1 select proj grp, min(proj start), max(proj end)

2 from (

3 select proj id,proj start,proj end,

4 sum(flag)over(order by proj id) proj grp

5 from (

6 select proj id,proj start,proj end,

7 case when

8 lag(proj end)over(order by proj id) = proj start

9 then 0 else 1

10 end flag

11 from V

12 )

13 )

14 group by proj grp

Обсуждение

DB2, MySQL, PostgreSQL и SQL Server

Применение представления V2 упрощает решение данной задачи. Представление V2 с помощью скалярного подзапроса в выражении CASE определяет принадлежность той или иной строки к множеству последовательных значений. Выражение CASE под псевдонимом FLAG возвращает 0, если текущая строка является частью последовательного множества, и 1 в противном случае (членство в последовательном множестве определяется существованием записи, значение PROJ END которой соответствует значению PROJ START текущей строки). Следующий шаг - проверить вложенный запрос Х (строки 5-9). Вложенный запрос Х возвращает все строки представления V2 и текущую сумму по FLAG; эта текущая сумма и является в данном случае признаком группы, что можно увидеть ниже:

select a.proj id,a.proj start,a.proj end, (select sum(b.flag) from v2 b

where b.proj id <= a.proj id) as proj grp from v2 a

Сгруппировав строки по диапазонам, находим начальную и конечную точку каждой группы, просто применяя агрегатные функции MIN и MAX к значениям столбцов PROJ START и PROJ END соответственно, и группируем результаты по значениям текущей суммы.

Oracle

Оконная функция LAG OVER исключительно полезна в данной ситуации. Она позволяет проверять значение PROJEND предыдущей строки без рефлексивного объединения, без скалярного подзапроса и без представления. Результаты выполнения функции LAG OVER без выражения CASE следующие:

select proj id,proj start,proj end,

lag(proj end)over(order by proj id) prior proj end from V

PROJ ID PROJ START PROJ END PRIOR PROJ END

1 01-JAN-2005 02-JAN-2005

2 02-JAN-2005 03-JAN-2005 02-JAN-2005

3 03-JAN-2005 04-JAN-2005 03-JAN-2005

4 04-JAN-2005 05-JAN-2005 04-JAN-2005

5 06-JAN-2005 07-JAN-2005 05-JAN-2005

6 16-JAN-2005 17-JAN-2005 07-JAN-2005

7 17-JAN-2005 18-JAN-2005 17-JAN-2005

8 18-JAN-2005 19-JAN-2005 18-JAN-2005

9 19-JAN-2005 20-JAN-2005 19-JAN-2005

10 21-JAN-2005 22-JAN-2005 20-JAN-2005

11 26-JAN-2005 27-JAN-2005 22-JAN-2005

12 27-JAN-2005 28-JAN-2005 27-JAN-2005

13 28-JAN-2005 29-JAN-2005 28-JAN-2005

14 29-JAN-2005 30-JAN-2005 29-JAN-2005

Выражение CASE в решении просто сравнивает значение, возвращаемое LAG OVER, со значением PROJSTART текущей строки. Если они совпадают, возвращается 0, в противном случае возвращается 1. Следующий шаг - вычислить текущую сумму 0 и 1, возвращенных выражением CASE, чтобы распределить все строки по группам. Результаты вычисления текущей суммы приведены ниже:

select proj id,proj start,proj end,

sum(flag)over(order by proj id) proj grp from (

select proj id,proj start,proj end, case when

lag(proj end)over(order by proj id) = proj start then 0 else 1 end flag from V )

Распределив все строки по группам, просто применяем агрегатные

функции MIN и MAX к значениям PROJ START и PROJ END соответственно и группируем полученные значения по столбцу текущей

суммы PROJ GRP.

Вставка пропущенных значений диапазона Задача

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