Sql-server – Set based query to group data ordered data

sql servert-sql

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?

Best Answer

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.

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt

T-sql – Return table based on 1, 2, 3, 4, 5 or 0 selected search options

Dynamic SQL is another option. I would try both this method and @a1ex07's method and see which runs faster for you.

CREATE PROCEDURE WorkAssignmentReport @Category varchar(25)
                                 ,@---Name varchar(25)
                                 ,@Stats varchar(25) 
                                 ,@---Name  varchar(25) 
                                 .........
                                 ......

DECLARE @sql nvarchar(max)

SET @sql = 'SELECT * ' + CHAR(13) + 
        'FROM [Ticket]  ' + CHAR(13) + 
        'INNER JOIN dbo.[TicketType] ON [Ticket].TicketTypeID = [TicketType].TicketTypeID  ' + CHAR(13) + 
        'INNER JOIN dbo.[Status] ON [Ticket].Stat .... ' + CHAR(13) + 
        '..... ' + CHAR(13) + 
        ' ' + CHAR(13) + 
        ' ' + CHAR(13) + 
        'WHERE 1=1 '

IF @Category IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [TicketType].Name LIKE @Category '

IF @ReportName IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Ticket].Name LIKE @ReportName  '

IF @Status IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Status].StatusName LIKE @Status '

IF @PhaseName IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Phase].PhaseName LIKE @PhaseName  '

IF @Name1 IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND (BA.FullName = @Name1 OR AD.FullName = @Name1) '

EXEC sp_executesql @sql, N'@Category varchar(25), @ReportName varchar(25), @Status varchar(25), @PhaseName varchar(25), @Name1 varchar(25)',
                @Category, @ReportName, @Status, @PhaseName, @Name1

Best Answer

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

T-sql – Return table based on 1, 2, 3, 4, 5 or 0 selected search options

Related Question