MySQL中重叠日期时间范围的总和 [英] Sum amount of overlapping datetime ranges in MySQL

问题描述

我有一个事件表，每个都有MySQL表中的StartTime和EndTime（作为DateTime类型）。

我正在尝试输出重叠次数 和重叠的事件数。

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

 创建表如果不存在`event`（
`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'）; 
 
 
＃如果使用上面的数据运行查询，
＃下表将是所需的输出
 
＃重叠事件数|这些事件重叠的总时间。 
 1，03:00:00 
 2，02:00:00 
 3，02:00:00 
  

这些结果的目的是生成一个帐单，用于使用几个小时。 （如果您有一个活动运行，您可能每小时支付10美元，但是如果两个活动正在运行，则只需要支付每小时8美元，但只能在两次运行的时间内。）

解决方案

尝试这样：

  SELECT COUNT，SEC_TO_TIME（SUM（持续时间））
 FROM（
 SELECT 
 COUNT（*）AS`Count`，
 UNIX_TIMESTAMP（Times2.Time） -  UNIX_TIMESTAMP时间）AS持续时间
 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` FR OM事件
 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事件ON Times1.Time> = events.StartTime AND Times2.Time< = events.EndTime 
 GROUP BY Times1.rownum 
）总计
 GROUP BY`Count` 
  

结果：

  1，03:00:00 
 2，02:00:00 
 3， 02:00:00 
  

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

I have a table of events, each with a StartTime and EndTime (as type DateTime) in a MySQL Table.

I'm trying to output the sum of overlapping times and the number of events that overlapped.

What is the most efficient / simple way to perform this query in 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

The purpose of these results is to generate a bill for hours used. (if you have one event running, you might pay 10 dollars per hour. But if two events are running, you only have to pay 8 dollars per hour, but only for the period of time you had two events running.)

解决方案

Try this:

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`

Result:

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

If this doesn't do what you want, or you want some explanation, please let me know. It could be made faster by storing the repeated subquery AllTimes in a temporary table, but hopefully it runs fast enough as it is.

这篇关于MySQL中重叠日期时间范围的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！