I have a vendor application which stores session data in a SQL Server 2008 database table. Columns it includes are things like the sessionId, the user's IP address, the datetime stamp when the session was created (i.e. user logs in), and the datetime stamp of when the session was destroyed (i.e. user logs off or system logs off user).
My objective is to analyze all the records in this table and figure out the average number of concurrent sessions across all records.
Now, unfortunately, the session destroy date is not accurate, for reasons outside the scope of this question. Therefore, I'm using a very rough estimate for the duration of the session: 1 hour. And I can always change the number later after I have the design phase sorted out.
I'm certain I can put together a stored procedure to get the average number of concurrent sessions, but I was hoping I can accomplish it with a query.
To simplify things here, let's assume there are 5 records in the table and all were created on the same day and are in GMT time:
sessionId sessionStart sessionEnd Accumulative # of Concurrent Sessions
1 12:00 13:00 1
2 12:15 13:15 2
3 12:30 13:30 3
4 12:45 13:45 4
5 13:00 14:00 4
At 13:00, the first session is destroyed. The number of concurrent sessions stays at 4, since sessions 2 through 5 still exist.
The question is how I can write a query which will output the average number of concurrent sessions? Can it be done? I imagine it would involve multiple joins on the same table, but I haven't quite figured out yet where to start.
The table has under a million records. I have access to a 2012 box and can copy the table there if it helps.
Best Answer
I find SQL Server 2012 to be a much better fit for this kind of problem because it supports
ORDER BY
in theOVER
clause for theSUM
window aggregate. Throwing your data into a temp table:I'll break the query into three parts so it's easier to understand. The first trick uses a running total to get the number of concurrent sessions at each time it changes. Imagine taking your data, assigning a 1 for the rows that create a session, and making a second copy of it for the destroyed rows and assigning a -1 for those rows. If you calculate a running total ordered by time you end up with the number of active sessions at each time the value changes.
Here are the results:
Now we need to take the average of that value. I assume that you want a weighted average by time, so what's missing is the number of minutes that each measurement should count for. SQL Server 2012 introduces the
LEAD
function which makes this pretty easy. Now the query is as follows:The intermediate result set:
We need to calculate the average which is the easiest part. Putting it all together:
The final result is 2.5.
Apparently, this can also be done in SQL Server 2008.