基于开始和结束时间的一天中最活跃的时间 [英] most active time of day based on start and end time

问题描述

我正在记录社区中玩家的统计信息.对于他们的在线状态和游戏状态，我正在注册它们的开始时间"和结束时间".为了显示一天中最活跃的日期和时间，我想使用一条SQL语句，该语句根据开始"和结束"日期时间值来测量最活跃的时刻.

I'm logging statistics of the gamers in my community. For both their online and in-game states I'm registering when they "begin" and when they "end". In order to show the most active day and hour of the day I'd like to use an SQL statement that measures the most active moments based on the "begin" and "end" datetime values.

查看 SQL-从数据库中选择最活跃"的时间我可以看到相似之处，但还需要包括开始时间和结束时间之间的时刻.

Looking at SQL - select most 'active' time from db I can see similarities, but I need to also include the moments between the start and end time.

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

Perhaps the easiest way is to write a cron that does the calculations, but I hope this question might teach me how to address this issue in SQL instead.

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

I've been searching for an SQL statement that allows to create a datetime period and use that to substract single hours and days. But to no avail.

---更新

随着我对此进行更多思考，我想知道是否明智的做法是，根据一天中的每个小时(最活跃的时间)运行24个查询，并针对最活跃的一天运行多个查询.但这似乎是对性能的浪费.但是此解决方案可能使查询成为可能，例如:

As I'm thinking more about this, I'm wondering whether it might be wise to run 24 queries based on each hour of the day (for most active hour) and several queries for the most active day. But that seems like a waste of performance. But this solution might make a query possible like:

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.列也仅出于示例目的，无论您认为哪种方式都易于理解，因为对于我来说，开始和结束都是可以的)

($hour is added for example purposes, of course I'm using PDO. Columns are also just for example purposes, whatever you think is easy for you to use in explaining that is identifiable as start and end is ok with me)

其他信息； PHP 5.5以上版本，PDO，MySQL 5以上版本 游戏中的表格布局为:游戏活动:活动ID，用户ID，游戏ID，开始，结束

Additional information; PHP 5.5+, PDO, MySQL 5+ Table layout for ingame would be: gameactivity: activityid, userid, gameid, started, ended

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;

推荐答案

如果我正确理解了您的要求，并且该图表示用户活动，则:

If I understood your requirements correctly, if this graph represents user activity:

       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是有史以来最活跃的小时，如您在示例中所看到的.如果这不是您想要的，请使用输入和所需结果的具体示例进行说明.

You want to know that 02:00 is the time of the day with the highest average activity (a row with 7 x), and 12/4 was most active day (a column with 10 x). Note that this doesn't imply that 02:00 of 12/4 was the most active hour ever, as you can see in the example. If this is not what you want please clarify with concrete examples of input and desired result.

我们做一些假设:

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

• An activity record can start on one date and finish on the next one. For instance: online 2013-12-02 23:35, offline 2013-12-03 00:13.
• No activity record has a duration longer than 23 hours, or the number of such records is negligible.

我们需要定义活动"的含义.我选择了在每种情况下都更易于计算的标准.如果需要，可以使两者都更加准确，但以进行更复杂的查询为代价.

And we need to define what does 'activity' mean. I picked the criteria that were easier to compute in each case. Both can be made more accurate if needed, at the cost of having more complex queries.

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

在一天中最活跃的时间里，我们将使用一个小的辅助表来容纳24个可能的小时.也可以使用其他答案中描述的技术即时生成并加入.

For the most active time of day we'll use a small auxiliary table holding the 24 possible hours. It can also be generated and joined on the fly with the techniques described in other answers.

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);

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

Then the following queries give the required results:

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;

这篇关于基于开始和结束时间的一天中最活跃的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！