MySQL按时间段分组结果 [英] MySQL grouping results by time periods

问题描述

我有一个包含会话事件时间戳的表. 我想根据时间戳查询两个会话之间至少有10分钟的事件间隔时，我有多少会话. 我可以使用MySql查询来计算会话数吗?

I have a table that contains timestamps of session events. I want to query how many sessions I have according to the timestamps when 2 sessions are separated by at least 10 minutes difference of events. Can I count the sessions with an MySql query ?

谢谢

推荐答案

表上的信息很少，对您来说只是一个基本的想法，但是您可以执行以下操作:-

With little info on your tables this is no more than a basic idea for you, but you could do something like this:-

SELECT COUNT(*)
FROM
(
    SELECT a.TimeStamp AS ThisTimeStamp, MIN(b.TimeStamp) AS NextTimeStamp
    FROM SomeTable a
    INNER JOIN SomeTable b
    ON a.TimeStamp < b.TimeStamp
    GROUP BY a.TimeStamp
) Sub1
WHERE Sub1.ThisTimeStamp < (Sub1.NextTimeStamp - 600)

获取所有时间戳，并将它们与所有其他较大的时间戳合并，然后使用MIN将其缩小到下一个最大的时间戳.然后从中选择差异小于600秒的计数(假设使用unix时间戳记).

Get all the timestamp and join them against all other timestamps that are greater, and use MIN to narrow that down to the next greatest timestamp. Then from that select the count where the difference is less than 600 seconds (assuming unix timestamps).

编辑-如果要限制用户事件中10分钟以上的间隔时间，则:-

EDIT - If you want the tied down for the number of 10 minute+ gaps in events for users then:-

SELECT COUNT(*)
FROM
(
    SELECT a.user_id, a.TimeStamp AS ThisTimeStamp, MIN(b.TimeStamp) AS NextTimeStamp
    FROM SomeTable a
    INNER JOIN SomeTable b
    ON a.TimeStamp < b.TimeStamp
    AND a.user_id = b.user_id
    GROUP BY a.user_id, a.TimeStamp
) Sub1
WHERE Sub1.ThisTimeStamp < (Sub1.NextTimeStamp - 600)

这篇关于MySQL按时间段分组结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！