mysql按时间分组不对_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 ?

Thanks

解决方案

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)

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

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)