0

I have a MySQL table that contains user actions over a given day for all my users. I am trying to construct sessions for these users from this data. My algorithm says "if a user has not made an action for 30s, the session has finished". I have my data in the format of:

User_id | Action | Timestamp

I'm wondering if its possible to use SQL to output the sessions by grouping wherever there is less than a 30s gap between actions carried out by the same user?

dojogeorge
  • 1,472
  • 3
  • 21
  • 33
  • You could add a field in your query that would be something like `if(gap > 30, 'Y', 'N') as myField` and group on that field – Osuwariboy Jul 20 '15 at 15:08
  • how would this be applied on a per row basis? ie as it goes through the data it only triggers when the current row is more than 30s after the previous row? – dojogeorge Jul 20 '15 at 15:13
  • LAG functions support this. see this answer for MySQL http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql – Randy Jul 20 '15 at 15:20
  • My apologies, I misread your question. I believe this post will answer your question [here](http://stackoverflow.com/questions/5483319/how-do-i-lag-columns-in-mysql) – Osuwariboy Jul 20 '15 at 15:23
  • This looks good, I've implemented it with my `timestamp` column, however it appears to just be giving me years. I have the following query: `SET @quot=-1; select mongoid,@quot lag_timestamp, @quot:=timestamp curr_timestamp from datas order by mongoid,timestamp` But the `lag_timestamp` column is always just a year – dojogeorge Jul 21 '15 at 09:10

0 Answers0