Сумма интервалов времени

[Тугай]

Решай влоб избавлясь от пересечений чрез временную таблицу:

1, 12:00:00, 17:00:00
 2, 13:00:00, 14:00:00
 3, 16:30:00, 17:30:00
 4, 19:00:00, 20:30:00

нужно преобразовать в

12:00:00, 17:30:00
19:00:00, 20:30:00

Алгоритм тут простой:
Цикл по основным данным
Если в временной таблице нет пересекающегося интервала, добавляем, если есть объединяем и заменяем.

 

[Sad Spirit]

В общем если пересечений у нас может быть произвольное количество, то спасёт только рекурсивный запрос.

У меня получилось вот такое угрёбище, но вроде работает правильно:

with recursive overs as (
    select first.id_event as id_event,
           least(second.time_end, first.time_end) as time_end,
           greatest(second.time_start, first.time_start) as time_start,
           -1 as mult
    from events as first, events as second
    where first.id_event < second.id_event and
          (first.time_start, first.time_end) overlaps (second.time_start, second.time_end)
    union all
    select one.id_event, least(two.time_end, one.time_end), greatest(two.time_start, one.time_start),
           mult * -1
    from events as one, overs as two
    where one.id_event < two.id_event and
          (one.time_start, one.time_end) overlaps (two.time_start, two.time_end)
)
select sum(time_end - time_start) + coalesce((
    select sum(mult * (time_end - time_start))
    from overs
), '0'::interval)
from events;

жду рецензий, дико интересно, не накосячил ли.

 

[Sad Spirit]

А, я перемудрил, нерекурсивной частью запроса можно сделать саму таблицу events, поэтому угрёбище упрощается:

with recursive overs as (
    select *
    from events
    union all
    select one.id_event, least(two.time_end, one.time_end), greatest(two.time_start, one.time_start)
    from events as one, overs as two
    where one.id_event < two.id_event and
          (one.time_start, one.time_end) overlaps (two.time_start, two.time_end)
)
select sum(time_end - time_start)
from overs;

 

[Тугай]

Еще как вариант, но тоже мало читабельно:

select ts1.time_start, ts2.time_end, 
TIME_FORMAT(
sum(ts2.time_end - ts1.time_start),
'%H:%i:%s')
from
(
select count(*) as n, l.time_start
from
(select time_start
from t as ts
where not exists
(select time_start from t where ts.time_start between time_start and time_end and ts.id<>id)) as l,
(select time_start
from t as ts
where not exists
(select time_start from t where ts.time_start between time_start and time_end and ts.id<>id)) as r
where l.time_start >= r.time_start
group by l.time_start
) as ts1,
(
select count(*) as n, l.time_end
from
(select time_end
from t as ts
where not exists
(select time_end from t where ts.time_end between time_start and time_end and ts.id<>id)) as l,
(select time_end
from t as ts
where not exists
(select time_end from t where ts.time_start between time_start and time_end and ts.id<>id)) as r
where l.time_end >= r.time_end
group by l.time_end
) as ts2
where ts1.n=ts2.n
group by ts1.time_start, ts2.time_end

идея в том, что

select time_start
from t as ts
where not exists
(select time_start from t where ts.time_start between time_start and time_end and ts.id<>id)

и

select time_end
from t as ts
where not exists
(select time_end from t where ts.time_end between time_start and time_end and ts.id<>id)

вернут правильные концы, потом только целый гемор склеить эти два запроса.

 

[WBS]

Вторая задача

Разобрался. Решение отличное, работает правильно.
Теперь интересно, можно ли как-то избавиться от ограничения, что time_start и time_end должны быть типа time. Хотелось бы более универсальное решение для типов данных timestamp (т.е. когда не выполняется условие "события у нас через нуль часов нуль минут не перескакивают"). Тут простым переопределением типа "time_end::time" не обойтись. И по-моему решение для timestamp будет сложнее.

А, я перемудрил, нерекурсивной частью запроса можно сделать саму таблицу events, поэтому угрёбище упрощается

Проверил, запрос работает правильно, спасибо. Хотя пока не разобрался как (никогда раньше не использовал рекурсивные запросы).

Еще как вариант, но тоже мало читабельно

Очень запутанно . Но идею я понял, и она мне понравилась, спасибо.
Я оформил Вашу идею в более простом виде:

SELECT SUM(time_end - time_start) FROM
	(SELECT row_number() OVER (ORDER BY time_start) as n, time_start FROM events as ts
		WHERE NOT EXISTS (SELECT time_start FROM events WHERE ts.time_start BETWEEN time_start AND time_end AND ts.id_event<>id_event)) R1
INNER JOIN
	(SELECT row_number() OVER (ORDER BY time_end) as n, time_end FROM events as ts
		WHERE NOT EXISTS (SELECT time_end FROM events WHERE ts.time_end BETWEEN time_start AND time_end AND ts.id_event<>id_event)) R2
ON R1.n = R2.n

Для запросов, которые "вернут правильные концы", я пронумеровал строки. После этого склеить их очень легко. Также я добавил сортировки для этих запросов. Это обязательно нужно было сделать, т.к. в исходной таблице строки (интервалы) могут находиться в произвольном порядке. Без сортировок концы интервалов могут перепутаться, и результат будет неверным.

 

[Тугай]

Оффтопик он:
Всех этих "крутых" штук нет в ANSI SQL, все так же я смотрю скептически и на всю эту явовощину, магию и прочую нелинейщину которой заразили PHP, да и SQL походу.
Хотя в ZF2, то как реализовали ControllerPlugins и ViewHelpers - это действительно круто, но все остальное не пришей кобыле хвост
Линейный код, если он и на две страницы да хоть на 10, читается и изменяется без напряга, да и работает быстрей, если к нему подходить с умом.
И хотя эта все неленейщина: ооп и паттрены на бумаге дает какую-то оптимизацию, на практике все больше заканчивается возвартом к Си и тому железу где это все исполняется.

 

[Sad Spirit]

Разобрался. Решение отличное, работает правильно.
Теперь интересно, можно ли как-то избавиться от ограничения, что time_start и time_end должны быть типа time. Хотелось бы более универсальное решение для типов данных timestamp (т.е. когда не выполняется условие "события у нас через нуль часов нуль минут не перескакивают"). Тут простым переопределением типа "time_end::time" не обойтись. И по-моему решение для timestamp будет сложнее.

Мне кажется, с таймштампами первая задача тоже должна работать, проверять, правда, уже лень. Во второй, есессно, придётся по-другому generate_series() писать, но тоже должно сработать.

Оффтопик он:
Всех этих "крутых" штук нет в ANSI SQL, все так же я смотрю скептически и на всю эту явовощину, магию и прочую нелинейщину которой заразили PHP, да и SQL походу.

Если речь про "WITH RECURSIVE", то это называется Common table expression, описано как раз в стандарте SQL:1999 и реализовано во всех популярных СУБД, кроме Понятно Какой.

Ну и я бы не сказал, что нерекурсивный запрос тут получился более понятным.

 

[Тугай]

Sad Spirit
Спс за ccылку, про такую штуку просто не знал. Теперь я тоже умный.
Последний рекурсивный запрос супер - понятный и копактный.
Для меня "популярные" это Oracle 8i, MS SQL 2000, Sybase SQLA 5.x, там можно и циклами обойтись.