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

问题描述

我有一个问题，几乎与 MySQL的重复日期时间范围的总和相等，所以我正在重用部分他的文本，希望这是OK ...

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

我正在尝试输出每种类型事件的重叠次数和重叠事件的数量。

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

  CREATE TABLE IF NOT EXISTS`events （
`EventID` int（10）unsigned NOT NULL auto_increment，
`EventType` int（10）unsigned NOT NULL，
`StartTime` datetime NOT NULL，
`EndTime` datetime default NULL，
 PRIMARY KEY（`EventID`）
）ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 37; 
 
 
 INSERT INTO`events`（'EventID`，EventType，'StartTime'，'EndTime`）VALUES 
（10001,1，'2009-02-09 03： 00:00'，'2009-02-09 10:00:00'），
（10002,1，'2009-02-09 05:00:00'，'2009-02-09 09:00 ：00'），
（10003,1，'2009-02-09 07:00:00'，'2009-02-09 09:00:00'），
（10004,3， '2009-02-09 11:00:00'，'2009-02-09 13:00:00'），
（10005,3，'2009-02-09 12:00:00'，' 2009-02-09 14:00:00'）; 
 
 
＃如果使用上面的数据运行查询，
＃下表将是所需的输出
 
＃重叠事件数，事件类型，|这些事件重叠的总时间。 
 1,1，03:00:00 
 2,1，02:00:00 
 3,1，02:00:00 
 1,3，01:00 ：00 
  

Mark Byers有一个非常漂亮的解决方案，我想知道可以扩展为包括事件类型。

他没有事件类型的解决方案是：

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

解决方案

  SELECT 
 COUNT（*）as occurrence 
，sub.event_id 
，SEC_TO_TIME（SUM（LEAST（e1end，e2end）） -  GREATEST e1start，e2start）））作为持续时间
 FROM 
（SELECT 
，e1.event_id 
，UNIX_TIMESTAMP（e1.starttime）as e1start 
，UNIX_TIMESTAMP（e1。作为e1end 
，UNIX_TIMESTAMP（e2.starttime）为e2start 
，UNIX_TIMESTAMP（e2.endtime）为e2end 
 FROM events e1 
 INNER JOIN事件e2 
 ON （e1.eventtype = e2.eventtype AND e1.id< e2.id 
 AND NOT（e1.starttime> e2.endtime OR e1.endtime< e2.starttime））
）sub 
 GROUP BY sub.event_id 
 ORDER BY发生DESC 
  

I have a question that is almost the same as Sum amount of overlapping datetime ranges in MySQL, so I'm reusing part of his text, hope that is ok...

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 for each type of event 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,
  `EventType` int(10) unsigned NOT NULL,
  `StartTime` datetime NOT NULL,
  `EndTime` datetime default NULL,
  PRIMARY KEY  (`EventID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;


INSERT INTO `events` (`EventID`, EventType,`StartTime`, `EndTime`) VALUES
(10001,1, '2009-02-09 03:00:00', '2009-02-09 10:00:00'),
(10002,1, '2009-02-09 05:00:00', '2009-02-09 09:00:00'),
(10003,1, '2009-02-09 07:00:00', '2009-02-09 09:00:00'),
(10004,3, '2009-02-09 11:00:00', '2009-02-09 13:00:00'),
(10005,3, '2009-02-09 12:00:00', '2009-02-09 14:00:00');


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

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

There is a really beautiful solution given there by Mark Byers and I'm wondering if that one can be extended to include "Event Type".

His solution without event type was:

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`

解决方案

SELECT
  COUNT(*) as occurrence
  , sub.event_id
  , SEC_TO_TIME(SUM(LEAST(e1end, e2end) - GREATEST(e1start, e2start)))) as duration
FROM
  (  SELECT  
      , e1.event_id
      , UNIX_TIMESTAMP(e1.starttime) as e1start
      , UNIX_TIMESTAMP(e1.endtime) as e1end
      , UNIX_TIMESTAMP(e2.starttime) as e2start
      , UNIX_TIMESTAMP(e2.endtime) as e2end
    FROM events e1
    INNER JOIN events e2 
      ON (e1.eventtype = e2.eventtype AND e1.id <> e2.id
      AND NOT(e1.starttime > e2.endtime OR e1.endtime < e2.starttime))
  ) sub
GROUP BY sub.event_id 
ORDER BY occurrence DESC

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