基于开始和结束时间的一天中最活跃的时间

我正在记录社区中玩家的统计数据。对于他们的在线状态和游戏内状态，我会在它们“开始”和“结束”时进行注册。为了显示一天中最活跃的一天和一小时，我想使用一个 SQL 语句来根据“开始”和“结束”日期时间值来测量最活跃的时刻。

看着SQL - 从数据库中选择最“活跃”的时间 https://stackoverflow.com/questions/12938880/sql-select-most-active-time-from-db我可以看到相似之处，但我还需要包括开始时间和结束时间之间的时刻。

也许最简单的方法是编写一个 cron 来进行计算，但我希望这个问题可以教我如何在 SQL 中解决这个问题。

我一直在寻找一个 SQL 语句，它允许创建一个日期时间段并使用它来减去单个小时和天。但无济于事。

- - 更新

当我更多地思考这个问题时，我想知道基于一天中的每个小时（对于最活跃的时间）运行 24 个查询以及针对最活跃的一天运行多个查询是否明智。但这似乎是对性能的浪费。但这个解决方案可能会使查询成为可能，例如：

SELECT COUNT(`userID`), DATE_FORMAT("%H",started) AS starthour, 
       DATE_FORMAT("%H",ended) AS endhour 
       FROM gameactivity 
       WHERE starthour >= $hour 
             AND endhour <= $hour GROUP BY `userID`

（添加 $hour 是为了示例目的，当然我正在使用 PDO。列也只是为了示例目的，任何您认为易于使用的内容来解释可识别的开始和结束对我来说都可以）

附加信息; PHP 5.5+、PDO、MySQL 5+ 游戏内的表布局为： gameactivity: Activityid, userid, gameid, started, end

DDL:

CREATE TABLE IF NOT EXISTS `steamonlineactivity` (
  `activityID` int(13) NOT NULL AUTO_INCREMENT,
  `userID` varchar(255) NOT NULL,
  `online` datetime DEFAULT NULL,
  `offline` datetime DEFAULT NULL,
  PRIMARY KEY (`activityID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

如果我正确理解您的要求，如果该图代表用户活动：

       Day 
       12/1 12/2 12/3 12/4 ...
Hour 0  xx    x    x   xx
     1   x   xx        xx
     2 xxx    x    x   xx
     3   x              x
     4        x         x
     5   x              x
     6                  x
   ...

您想知道 02:00 是一天中平均活动最高的时间（连续 7 个x），12/4 是最活跃的一天（一列有 10x）。请注意，这并不意味着 12/4 的 02:00 是有史以来最活跃的时间，正如您在示例中看到的那样。如果这不是您想要的，请用输入和期望结果的具体示例进行澄清。

我们做出几个假设：

• 活动记录可以在一个日期开始并在下一个日期结束。例如：在线2013-12-02 23:35， 离线2013-12-03 00:13.
• 没有活动记录的持续时间超过 23 小时，或者此类记录的数量可以忽略不计。

我们需要定义“活动”的含义。我选择了在每种情况下更容易计算的标准。如果需要，两者都可以变得更准确，但代价是查询更复杂。

• 一天中最活跃的时间将是有更多活动记录重叠的时间。请注意，如果用户在一小时内启动和停止多次，则会被多次计数。
• 最活跃的一天将是一天中任何时间都有更多活跃的唯一用户的一天。

对于一天中最活跃的时间，我们将使用一个小型辅助表来保存 24 小时的数据。它还可以使用其他答案中描述的技术动态生成和连接。

CREATE TABLE hour ( hour tinyint not null, primary key(hour) );
INSERT hour (hour)
VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
     , (11), (12), (13), (14), (15), (16), (17), (18), (19), (20)
     , (21), (22), (23);

然后以下查询给出所需的结果：

SELECT hour, count(*) AS activity
  FROM steamonlineactivity, hour
 WHERE ( hour BETWEEN hour(online) AND hour(offline)
      OR hour(online) BETWEEN hour(offline) AND hour
      OR hour(offline) BETWEEN hour AND hour(online) )
 GROUP BY hour
 ORDER BY activity DESC;

SELECT date, count(DISTINCT userID) AS activity
  FROM ( 
       SELECT userID, date(online) AS date
         FROM steamonlineactivity
        UNION
       SELECT userID, date(offline) AS date
         FROM steamonlineactivity
   ) AS x
 GROUP BY date
 ORDER BY activity DESC;