计算表中时间间隔之和的总长度

Question

我想计算一下表中时间间隔之和的总时间长度。

例如，考虑到以下情况：

mysql> SELECT * FROM Temp;
+------+---------------------+---------------------+
| id   | start               | end                 |
+------+---------------------+---------------------+
|    1 | 2010-01-01 10:00:00 | 2010-01-01 11:00:00 |
|    2 | 2010-01-01 12:00:00 | 2010-01-01 14:00:00 |
|    3 | 2010-01-01 13:00:00 | 2010-01-01 15:00:00 |
+------+---------------------+---------------------+

我想以某种方式选择总时间长度，在这种情况下是4小时(间隔的总时间长度(10:00，11:00)和(12:00，15:00)。

我不在乎输出是以秒为单位( INT或FLOAT)，还是以任何其他合理的格式。

值得一提的是，我不确定表中日期的顺序。不能保证以任何方式对开始日期时间或结束日期时间进行排序。我也不能说一个“典型的”日期间隔--例如，它可能不止一天。

然而，I可以说，任何一个时间间隔都是非负长度的.也就是说，对于任何记录，结束日期至少要晚到开始日期。

我知道如何用简单的编程语言(如Python)来完成这个任务；我只是想知道在纯MySQL中是否有一种合理的方法来完成这个任务。如果没有，我将选择所有内容，并使用其他编程语言进行处理。因此，“如果没有非常认真的努力就不可能在MySQL中实现这一点”也可以作为这个问题的合法答案。

我见过类似的this question，但它是关于tsql的。这里提供的解决方案是使用MySQL所不知道的语法，比如cross apply，而我翻译它的尝试失败了。

根据请求，下面是创建示例数据的查询：

CREATE TABLE Temp (id INT, start DATETIME, end DATETIME);
INSERT INTO Temp (id, start, end) VALUES (1, '2010-01-01 10:00', '2010-01-01 11:00');
INSERT INTO Temp (id, start, end) VALUES (2, '2010-01-01 13:00', '2010-01-01 14:00');
INSERT INTO Temp (id, start, end) VALUES (3, '2010-01-01 11:00', '2010-01-01 16:00');

因此，数据如下：

+------+---------------------+---------------------+
| id   | start               | end                 |
+------+---------------------+---------------------+
|    1 | 2010-01-01 10:00:00 | 2010-01-01 11:00:00 |
|    2 | 2010-01-01 13:00:00 | 2010-01-01 14:00:00 |
|    3 | 2010-01-01 11:00:00 | 2010-01-01 16:00:00 |
+------+---------------------+---------------------+

这个示例数据的结果应该是6小时。

Answer 1

免责声明

这可能是在SQL之外执行的最佳方法。

用于那些喜欢痛苦查询的人，

您可以创建一个查询，试图确定表中其他地方是否存在与end列重叠的行。如果没有，那么试着找出end列和最近的start列(又名gap )之间有多少时间。

然后从整个表中取最大end，从整个表中减去最小start，最后减去gap列的总数：

select
unix_timestamp(maxEnd)-unix_timestamp(minSt)-sum(case when hasEndOverlap=0 then gap else 0 end) as unionSecs,
(unix_timestamp(maxEnd)-unix_timestamp(minSt)-sum(case when hasEndOverlap=0 then gap else 0 end))/(60*60) as unionHrs
from
(
 select c.id,c.`start`,c.`end`,
 c.minSt,c.maxEnd,
 c.hasEndOverlap,
 @prevSt,
 unix_timestamp(@prevSt)-unix_timestamp(c.`end`) as gap,
 @prevSt := c.`start`
 from
 (
  select t.id,t.`start`,t.`end`,
  a.minSt,a.maxEnd,
  case when min(te.id) is null and t.`end` != a.maxEnd then 0 else 1 end as hasEndOverlap
  from Temp t
  left outer join Temp te on t.`end` >= te.`start` and t.`end` <= te.`end` and t.id != te.id
  join (select min(`start`) as minSt,max(`end`) as maxEnd from test.`Temp`) a
  group by t.id,t.`start`,t.`end`
  ) c
  join (select @prevSt := '1970-01-01') r
  order by c.`end` desc
) d
group by minSt,maxEnd
;