在MySQL中计算重叠日期时间范围的总和

Question

我有一个事件表，每个事件都有一个开始时间和结束时间（类型为DateTime）在MySQL表中。

我想输出重叠时间的总和以及重叠的事件数量。

在MySQL中执行此查询的最有效/简单的方法是什么？

CREATE TABLE IF NOT EXISTS `events` (
  `EventID` int(10) unsigned NOT NULL auto_increment,
  `StartTime` datetime NOT NULL,
  `EndTime` datetime default NULL,
  PRIMARY KEY  (`EventID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;


INSERT INTO `events` (`EventID`, `StartTime`, `EndTime`) VALUES
(10001, '2009-02-09 03:00:00', '2009-02-09 10:00:00'),
(10002, '2009-02-09 05:00:00', '2009-02-09 09:00:00'),
(10003, '2009-02-09 07:00:00', '2009-02-09 09:00:00');


# if the query was run using the data above,
# the table below would be the desired output

# Number of Overlapped Events | Total Amount of Time those events overlapped.
1, 03:00:00
2, 02:00:00
3, 02:00:00

这些结果的目的是生成使用小时数的账单。（如果您只有一个事件在运行，您可能会支付每小时10美元。但如果有两个事件在运行，您只需要支付每小时8美元，但仅限于您有两个事件运行的时间段。）

- maxsilver

---

这个问题缺乏清晰度。这样的问题有什么用处，而且你的提议是有缺陷的。3小时 - 只有一个事件正在运行（3a到5a和9a到10a） 2小时 - 两个事件同时运行（5a到7a） 2小时 - 所有三个事件都在运行（7a到9a）在7点到9点之间有3个并发事件，所以你的中间那个点是错误的和无意义的。 - Eddie

很抱歉让你感到困惑，我已编辑问题以提高清晰度，并添加了问题背后的目的。当您说“提案有缺陷”时，我不明白您的意思。您是绝对正确的，在7点至9点之间有3个同时发生的活动，但我的问题已经在上面的示例中提到了这一点（那是第3行）。您提到的那一行（第2行）是指5a到7a期间，而不是7a到9a期间。我希望包含的SQL语句可以澄清这一点。 - maxsilver

你的解释对于问题非常有帮助，特别是包括问题的背景、表结构和测试数据、期望输出以及没有不必要/无关紧要的内容。以目前的形式来看，我认为这是我在这里看到的较好的问题之一。 - Mark Byers

Answer 1

试试这个：

SELECT `COUNT`, SEC_TO_TIME(SUM(Duration))
FROM (
    SELECT
        COUNT(*) AS `Count`,
        UNIX_TIMESTAMP(Times2.Time) - UNIX_TIMESTAMP(Times1.Time) AS Duration
    FROM (
        SELECT @rownum1 := @rownum1 + 1 AS rownum, `Time`
        FROM (
            SELECT DISTINCT(StartTime) AS `Time` FROM events
            UNION
            SELECT DISTINCT(EndTime) AS `Time` FROM events
        ) AS AllTimes, (SELECT @rownum1 := 0) AS Rownum
        ORDER BY `Time` DESC
    ) As Times1
    JOIN (
        SELECT @rownum2 := @rownum2 + 1 AS rownum, `Time`
        FROM (
            SELECT DISTINCT(StartTime) AS `Time` FROM events
            UNION
            SELECT DISTINCT(EndTime) AS `Time` FROM events
        ) AS AllTimes, (SELECT @rownum2 := 0) AS Rownum
        ORDER BY `Time` DESC
    ) As Times2
    ON Times1.rownum = Times2.rownum + 1
    JOIN events ON Times1.Time >= events.StartTime AND Times2.Time <= events.EndTime
    GROUP BY Times1.rownum
) Totals
GROUP BY `Count`

结果：

1, 03:00:00
2, 02:00:00
3, 02:00:00

如果这不是你想要的，或者你需要一些解释，请告诉我。通过在临时表中存储重复的子查询AllTimes，可以使其更快，但希望它现在运行得足够快。

- Mark Byers

---

在我的MySQL服务器上，除非将“times2.rownum”更改为“Times2.rownum”，否则它无法运行，但除此之外，这正是我要找的！完美地工作！谢谢！ - maxsilver

抱歉，那是个打字错误，我没有注意到它因为它并没有失败。现在已经修复了。很高兴你解决了问题！ :) - Mark Byers

所以显然maxsilver在Linux上运行了查询，而Mark在Windows上。 - Anax

Answer 2

从一个包含单个日期时间字段作为主键的表开始，并使用您感兴趣的每个时间值填充该表。闰年有527040分钟（31622400秒），因此如果您的事件跨越几年，这个表可能会变得很大。

现在加入此表，执行类似以下操作：

SELECT i.dt as instant, count(*) as events
FROM instant i JOIN event e ON i.dt BETWEEN e.start AND e.end
GROUP BY i.dt
WHERE i.dt BETWEEN ? AND ?

在instant.dt上建立索引可能让您省去ORDER BY。

如果事件很少添加，这可能是您想要通过离线运行查询来预先计算并填充单独的表格。

- Dave W. Smith

Answer 3

我建议使用一个内存结构，包含开始时间、结束时间和事件数量...（这里简化为时间（小时），但使用Unix时间可以精确到秒）

对于每个事件，如果没有重叠，就将新事件按原样插入；否则，找到重叠部分，并将事件分成（最多3个）可能重叠的部分。以您的示例数据为例，从第一个事件开始：

事件1从早上3点开始，到早上10点结束：由于没有重叠，只需添加该事件即可：

    3,10,1

活动2从早上5点开始，到早上9点结束：时间有重叠，因此需要拆分原始活动，并在新的活动后添加额外的“＃events”标识。

    3,5,1
    5,9,2
    9,10,1

第三个事件从早上7点开始，到早上9点结束：也有重叠的部分，请对所有时间段执行相同操作：

    3,5,1
    5,7,2
    7,9,3
    9,10,1

因此，计算每个#事件的重叠小时数：

1 event= (5-3)+(10-9)=3 hours
2 events = 7-5 = 2 hours
3 events = 9-7 = 2 hours

如果有许多事件需要比较，将其作为后台进程运行是有意义的。

- Osama Al-Maadeed