Peter Larsson Blog

Patron Saint of Lost Yaks

New article series going on by Itzik Ben-Gan

by Peter Larsson in  , , ,

I recommend you read it. Next part will contain a brilliant solution for calculating concurrent sessions in a linear algorithm. The math involved is very good indeed.

First part is found here http://www.sqlmag.com/articles/index.cfm?articleid=102734

//Peso

Legacy Comments
Arnold Fribble
2009-10-26		 re: New article series going on by Itzik Ben-Gan
You know that page is subscriber-only?
Arnold Fribble
2009-10-26		 re: New article series going on by Itzik Ben-Gan
Oh, here's the gist of what I came up with last year:

SELECT MAX(s_hi) FROM (
SELECT d * (2*rd - ROW_NUMBER() OVER (ORDER BY t, d)) - (d+1)/2 AS s_hi
FROM (
SELECT 1 AS d, start AS t, ROW_NUMBER() OVER (ORDER BY start) AS rd
FROM dbo.Event

UNION ALL

SELECT -1 AS d, finish AS t, ROW_NUMBER() OVER (ORDER BY finish) AS rd
FROM dbo.Event
) AS A
) AS A

I was never quite sure what to count for a time when there were both events (sessions) starting and ending.
Peso
2009-10-26		 re: New article series going on by Itzik Ben-Gan
Arnold, that is really good! And adapted to multiple calculation used in Itzik's sample data,

SELECT app,1+MAX(s_hi) FROM (
SELECT app,d * (2 * rd - ROW_NUMBER() OVER (partition by app ORDER BY t, d)) - (d+1)/2 AS s_hi
FROM (
SELECT 1 AS d, app, starttime AS t, ROW_NUMBER() OVER (partition by app ORDER BY starttime) AS rd
FROM dbo.sessions

UNION ALL

SELECT -1 AS d, app, endtime AS t, ROW_NUMBER() OVER (partition by app ORDER BY endtime) AS rd
FROM dbo.sessions
) AS A
) AS A
group by app
Arnold Fribble
2009-10-27		 re: New article series going on by Itzik Ben-Gan
Well, as I said, the page is subscriber only, so I can only see the first two paragraphs of text, and I'm unlikely to pay $6 each month just to read the rest.