mysql - 重叠日期时间的最大数量，有多个开始和结束时间

Question

经过一些实验和搜索，我发现这个查询给出了我所需要的结果:

SET @start := '2015-12-12 00:00:00', @end := '2015-12-12 23:59:59';

SELECT Max(simultaneous_people), 
       Max(simultaneous_event), 
       boundary 
FROM   (SELECT Count(id)                AS simultaneous_people, 
               Count(DISTINCT uniqueId) AS simultaneous_event, 
               boundary 
        FROM   mytable 
               RIGHT JOIN (SELECT row_begin AS boundary 
                           FROM   mytable 
                           WHERE  row_begin BETWEEN @start AND @end 
                           UNION 
                           SELECT row_end 
                           FROM   mytable 
                           WHERE  row_end BETWEEN @start AND @end 
                           UNION 
                           SELECT @start 
                           UNION 
                           SELECT @end 
                           UNION 
                           SELECT Max(boundary) 
                           FROM   (SELECT Max(row_begin) AS boundary 
                                   FROM   mytable 
                                   WHERE  row_begin <= @start 
                                   UNION ALL 
                                   SELECT Max(row_end) 
                                   FROM   mytable 
                                   WHERE  row_end <= @end) t) t 
                       ON row_begin <= boundary 
                          AND boundary < row_end 
        WHERE  row_status = 1 
        GROUP  BY boundary) t;

同一时间内重叠时间段的最大数量。但我需要在多个时间间隔(例如 10)之间提取此信息。我不知道如何使用如下查询在内置运行时的日历中提取此信息:

SELECT DATE_SUB(@date, INTERVAL @num MINUTE) AS endSample, 
       DATE_SUB(@date, INTERVAL @num:=@num+@lenght MINUTE) AS startSample
FROM 
  mytable,
  (SELECT @num:=0) num
LIMIT 10;

我正在使用 MySQL，不幸的是我无法在此数据库上存储任何数据/表/过程/ View 。

如果有人知道如何以有效的方式合并这两个查询，那就太好了。谢谢!

<小时/>

更新:

我的架构:

CREATE TABLE mytable (
  id         INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uniqueId   INT,
  row_status INT,
  row_begin DATETIME,
  row_end   DATETIME
);

还有一些数据只是一天的数据，仅测试小时粒度:

INSERT INTO mytable (uniqueId, row_status, row_begin, row_end)
VALUES
  (1, 1, '2015-12-12 08:00:00', '2015-12-12 12:00:00'),
  (1, 1, '2015-12-12 08:00:00', '2015-12-12 14:00:00'),
  (1, 1, '2015-12-12 08:00:00', '2015-12-12 14:00:00'),
  (2, 1, '2015-12-12 13:00:00', '2015-12-12 14:00:00'),
  (2, 1, '2015-12-12 13:00:00', '2015-12-12 16:00:00'),
  (3, 1, '2015-12-12 09:00:00', '2015-12-12 12:00:00'),
  (3, 0, '2015-12-12 08:00:00', '2015-12-12 16:00:00');

我刚刚添加了 SQL Fiddle .

在这里，我必须使用变量手动设置每个日期范围，但我需要能够设置“日历”，并且能够指定日历是按天、按小时还是按分钟、按调整@length变量。

我的解决方案的第一部分取自this answer .

我想要的输出(以小时为时间粒度)将类似于:

start_sample        | end_sample          | MAX(simultaneous_people) | MAX(simultaneout_event)
2015-12-12 08:00:00 | 2015-12-12 08:59:59 | 3                        | 1
2015-12-12 09:00:00 | 2015-12-12 09:59:59 | 4                        | 2
2015-12-12 10:00:00 | 2015-12-12 10:59:59 | 4                        | 2
2015-12-12 11:00:00 | 2015-12-12 11:59:59 | 4                        | 2
2015-12-12 12:00:00 | 2015-12-12 12:59:59 | 2                        | 1
2015-12-12 13:00:00 | 2015-12-12 13:59:59 | 4                        | 2
2015-12-12 14:00:00 | 2015-12-12 14:59:59 | 1                        | 1
...

但是如果我需要将粒度更改为天，利用这些数据，我将获得

start_sample        | end_sample          | MAX(simultaneous_people) | MAX(simultaneout_event)
2015-12-12 00:00:00 | 2015-12-12 23:59:59 | 4                        | 2
2015-12-13 00:00:00 | 2015-12-12 23:59:59 | 0                        | 0
...

Answer 1

解决方案比我想象的要容易(希望到目前为止看起来是正确的)。我将使用日历生成器添加一些额外的边界，以避免最终数据出现空洞(否则不会显示没有条目的一天):

                       ...
                       UNION 
                       SELECT @start 
                       UNION 
                       SELECT @end 
                       UNION
                           SELECT DATE_SUB(@date, INTERVAL @num:=@num+@lenght MINUTE)
                           FROM 
                             mytable,
                             (SELECT @num:=0) num
                           LIMIT 10
                       ...

然后我必须按 date_part (在 postgres 中)或 DATE_FORMAT (在 MySQL 中)进行分组，这对我来说更有趣(我必须添加一个与 @length 配对的变量)，而不是像我之前所做的那样按整个边界进行分组，外部查询将变为:

SELECT Max(simultaneous_people), 
       Max(simultaneous_event), 
       DATE_FORMAT(boundary,'%Y%m%d')
FROM (...) as t
GROUP BY DATE_FORMAT(boundary,'%Y%m%d');

我希望这可以帮助其他人，我花了一段时间才达到这一点。这个查询确实包含大量数据，因此您对数据的剪切/分段越多(垂直和水平)，它的性能就越好，应该在通过联合获得绑定(bind)的任何地方添加“WHERE row_status = 1” .