Слияние интервалов с MySQL

Мне нужна помощь в объединении перекрывающихся интервалов.

У меня есть эта таблица:

id  start       end
1   15:30:00    16:20:00
2   10:00:00    13:00:00
3   15:00:00    16:09:00
4   11:00:00    14:00:00
5   16:20:00    16:30:00

SQL:

CREATE TABLE `intervals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start` time NOT NULL,
  `end` time NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `intervals` (`start`, `end`) VALUES
('15:30:00', '16:20:00'),
('10:00:00', '13:00:00'),
('15:00:00', '16:09:00'),
('11:00:00', '14:00:00'),
('16:20:00', '16:30:00');

И я хочу, чтобы вывод был таким:

id  start       end
2   10:00:00    14:00:00
3   15:00:00    16:30:00

Имеет значение только время начала и окончания; столбец ID в принципе можно игнорировать.

Существует аналогичная реализация PHP https://stackoverflow.com/a/4347215/1085872 (до шага 2), но я нужно добиться слияния только с MySQL.


mysql merge intervals overlapping
person Martin Koutny    schedule 07.12.2011    source источник
comment
Обратите внимание, что некоторые диапазоны, которые следует объединить (например, идентификаторы 3 и 5 в примере), сами по себе не перекрываются. Чтобы объединить их, вам понадобится транзитивное закрытие отношения перекрытия. Вычисление замыканий в SQL, как правило, сложно или невозможно. Почему ограничение на решение SQL?   -  person outis    schedule 07.12.2011
comment
Согласитесь, с помощью языка программирования вычислять интервал проще.   -  person ajreal    schedule 07.12.2011
comment
Найдено решение SQL, которое работает - это лучший ответ на stackoverflow.com/questions/8451925/   -  person servermanfail    schedule 16.03.2015

Ответы (1)


arrow_upward
-1
arrow_downward

Попробуй это -

SELECT MIN(i.start) start, MAX(i.end) end FROM
  (
    SELECT @a:=@a + 1 a, t1.start, GREATEST(t1.end, t2.end) AS end
    FROM (SELECT @a := 0) t, intervals t1
    JOIN intervals t2 ON t1.id <> t2.id and t1.start >= t2.start and t1.start < t2.end
  ) p
  JOIN intervals i
     ON (i.start BETWEEN p.start AND p.end) OR (i.end BETWEEN p.start AND p.end)
  GROUP BY p.a;

+----------+----------+
| start    | end      |
+----------+----------+
| 10:00:00 | 14:00:00 |
| 15:00:00 | 16:30:00 |
+----------+----------+

Запрос основан на этом решении — http://www.artfulsoftware.com/infotree/queries.php#807

Протестируйте на своих данных.

person Devart    schedule 08.12.2011
comment
Я попробовал это на более сложном наборе перекрывающихся данных, и это не удалось. Например, 1-4,2-5,3-6,10-11 не работает. - person servermanfail; 16.03.2015