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

Question

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

看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+ingame 的表布局为:gameactivity:activityid、userid、gameid、started、ended

数据链接:

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;

Answer 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 是最活跃的一天(一列有 10 x)。请注意，这并不意味着 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;