Как найти окончание накладывающихся отрезков?

Question

[Сергей Соколов]

В таблице хранятся диапазоны оплаченного времени: начало и конец. Если оплаченные отрезки накладываются, это продлевает оплаченный период на время пересечения.

id  i   o
----------
1   11  22
2   31  42
3   35  45

В этом примере один изолированный отрезок 11..22, и два накладывающихся: 31..42 + 35..45

За счёт наложения окончание периода не в 45, а в 45 + (42 - 35) = 52

Ранее задача решалась в PHP. Просто получал все диапазоны из базы и дальше в коде легко находил действующий финиш.

Как SQL запросом получить такое значение окончания оплаченного периода?

Answer 1

[longclaps]

Не такой он и простой, этот вопрос. Решается применением оконных функций, в постгресе они есть, в мускуле вроде обещали сделать, может уже сделали.

Comments

[Сергей Соколов]

а с переменной как в running total не получится? Только надо проверять, меньше ли она начала следующего периода.

Опечатку исправил, спасиб!

[longclaps]

Сергей Соколов, процедурой можно - я лишь ответил, как это сделать средствами SQL. Это идеологически более правильный подход, так как оставляет свободу рук для оптимизатора запросов - а в процедуре порядок исполнения прописан.
Кстати, наверное можно состряпать рекурсивный запрос - но опять в постгресе.

Answer 2

[Rsa97]

Найти все пересекающиеся интервалы и длительности пересечения несложно:

SELECT `i1`.`i`, `i1`.`o`, `i2`.`i`, `i2`.`o`, 
       (IF(`i1`.`o` > `i2`.`o`, `i2`.`o`, `i1`.`o`) 
        - IF(`i1`.`i` > `i2`.`i`, `i1`.`i`, `i2`.`i`)) AS `crosstime`
  FROM `table`  AS `i1`
  JOIN `table` AS `i2` ON `i2`.`id` > `i1`.`id`
    AND `i1`.`i` < `i2`.`o` AND `i2`.`i` < `i1`.`o`

Затем можно добавить группировку, и вычислить SUM(`crosstime`)