I have a table of user action data and I would like to try and group it into ‘sessions’, so I could figure out how many times a user has logged on, how long they stay online, and how many actions they do during the time…
Full sql fiddle here: http://sqlfiddle.com/#!6/e24a50/4
Basically I have a table defined below, and I would like to group the actions when there is less than 20minutes of activity between two requests
CREATE TABLE WebsiteUsage ( Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, UserId INT NOT NULL, ApplicationId VARCHAR(MAX) NOT NULL, TimeStamp DATETIME NOT NULL )
Ideally, I would get a resultset that resembled this:
UserId Session StartTime EndTime ActionCount ----------- ----------- ----------------------- ----------------------- ----------- 1 1 2015-04-15T10:28:03.887 2015-04-15T10:38:18.037 58 1 2 2015-04-15T14:40:26.277 2015-04-15T14:56:02.857 13
I’ve got the time between requests using the following query, but I cannot figure out how to group them based on when
MinutesSinceLastRequest < 20
WITH cte AS ( SELECT *, PreviousTimestamp = LAG([Timestamp], 1, NULL) OVER (PARTITION BY UserId, ApplicationId ORDER BY [Timestamp]) FROM dbo.WebsiteUsage ) SELECT *, MinutesSinceLastRequest = DATEDIFF(MINUTE, PreviousTimestamp, [Timestamp]) FROM cte
Is there a way to accomplish this in a set based operation or do I have to fall back to the old cursor?
Start by making a column like
CASE WHEN MinutesSinceLastRequest < 20 THEN 0 ELSE 1 END AS IsOver20
Then wrap that in a rolling sum – this will change every time there’s a 1 for IsOver20:
SUM(IsOver20) OVER (ORDER BY [TimeStamp] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupNum
So now you can group by this easily. Include
PARTITION BY UserId if you need to break it down by UserId too.