根据时间戳之间的时间间隔将时间戳分组,然后从MySQL组进行计算

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了根据时间戳之间的时间间隔将时间戳分组,然后从MySQL组进行计算，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3289字，纯文字阅读大概需要5分钟。

内容图文

为了解决这个问题,我试图基于事件日志来计算“应用程序中的时间”.

假设下表：

user_id   event_time
2         2012-05-09 07:03:38
3         2012-05-09 07:03:42
4         2012-05-09 07:03:43
2         2012-05-09 07:03:44
2         2012-05-09 07:03:45
4         2012-05-09 07:03:52
2         2012-05-09 07:06:30

我想从彼此之间2分钟内(并按用户分组)的一组时间戳中获得最高和最低event_time之间的差异.如果时间戳超出集合2分钟间隔,则应将其视为另一集合的一部分.

所需的输出：

user_id  seconds_interval
2        7     (because 07:03:45 - 07:03:38 is 7 seconds)
3        0     (because 07:03:42)
4        9     (because 07:03:52 - 2012-05-09 07:03:43)
2        0     (because 07:06:30 is outside 2 min interval of 1st user_id=2 set)

这是我尝试过的方法,尽管我无法对seconds_interval进行分组(即使可以,但我不确定这是正确的方向)：

SELECT (max(tr.event_time)-min(tr.event_time)) as seconds_interval
FROM some_table tr
INNER JOIN TrackingRaw tr2 ON (tr.event_time BETWEEN 
   tr2.event_time - INTERVAL 2 MINUTE AND tr2.event_time + INTERVAL 2 MINUTE) 
GROUP BY seconds_interval

解决方法:

我认为没有一种非常简单的方法来查询现有表以生成所需的数据.但是,您可以维护第二个用户会话表(当然,这样做的缺点是,如果您以后想要使用不同会话超时时间的报告,则需要从头开始重新填充该表)：

CREATE TABLE Sessions (
  user_id INT,
  session_start TIMESTAMP,
  session_end   TIMESTAMP,
  PRIMARY KEY (user_id, session_start),
  FOREIGN KEY (user_id, session_start) REFERENCES TrackingRaw(user_id, event_time),
  FOREIGN KEY (user_id, session_end  ) REFERENCES TrackingRaw(user_id, event_time)
);

您可以使用使用INSERT … SELECT … ON DUPLICATE KEY UPDATE的触发器自动填充/更新这样的表：

CREATE TRIGGER after_insert_TrackingRaw AFTER INSERT ON TrackingRaw FOR EACH ROW
  INSERT INTO Sessions (user_id, session_start, session_end)
    SELECT NEW.user_id,
           IFNULL(MAX(session_start), NEW.event_time),
           NEW.event_time
    FROM   Sessions
    WHERE  user_id = NEW.user_id
       AND session_end >= NEW.event_time - INTERVAL 2 MINUTE
  ON DUPLICATE KEY UPDATE
    session_start = session_start,
    session_end   = NEW.event_time;

然后,获取所需的查询结果：

SELECT user_id, session_end - session_start AS seconds_interval FROM Sessions;

在sqlfiddle上看到它.

更新

经过进一步的思考,您当然可以在存储过程中构建这样的Sessions表：

CREATE PROCEDURE getSessions(IN secs INT) READS SQL DATA BEGIN
  DECLARE no_more_rows BOOLEAN;
  DECLARE cur CURSOR FOR
    SELECT user_id, event_time FROM TrackingRaw ORDER BY event_time ASC;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

  DROP   TEMPORARY TABLE IF EXISTS Sessions;
  CREATE TEMPORARY TABLE Sessions (
    user_id INT,
    session_start TIMESTAMP,
    session_end   TIMESTAMP,
    PRIMARY KEY(user_id,session_start),
    FOREIGN KEY(user_id,session_start) REFERENCES TrackingRaw(user_id,event_time),
    FOREIGN KEY(user_id,session_end  ) REFERENCES TrackingRaw(user_id,event_time)
  );

  OPEN cur;
  the_loop: LOOP
    FETCH cur INTO @u, @t;
    IF no_more_rows THEN
      CLOSE cur;
      LEAVE the_loop;
    END IF;

    INSERT INTO Sessions
      SELECT @u, IFNULL(MAX(session_start), @t), @t
      FROM   Sessions
      WHERE  user_id = @u AND session_end >= @t - secs
    ON DUPLICATE KEY UPDATE
      session_start = session_start, session_end = @t
  END LOOP the_loop;

  DEALLOCATE PREPARE stmt;
  SELECT user_id, session_end - session_start AS seconds_interval FROM Sessions;
  DROP TEMPORARY TABLE Sessions;
END;;

然后获取您的输出：

CALL getSessions(120); -- for a 2 minute (120 second) timeout

内容总结

以上是互联网集市为您收集整理的根据时间戳之间的时间间隔将时间戳分组,然后从MySQL组进行计算全部内容，希望文章能够帮你解决根据时间戳之间的时间间隔将时间戳分组,然后从MySQL组进行计算所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端