Thinking outside the box

Patron Saint of Lost Yaks

posts - 199, comments - 687, trackbacks - 4

My Links

News

Post Categories

<< Pass the Torch | Home | Superfast sequence generators! >>

New article series going on by Itzik Ben-Gan

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

Feedback

# re: New article series going on by Itzik Ben-Gan

You know that page is subscriber-only?

10/26/2009 7:35 PM | Arnold Fribble

# 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.

10/26/2009 7:44 PM | Arnold Fribble

# 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

10/26/2009 9:36 PM | Peso

# 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.

10/27/2009 10:07 AM | Arnold Fribble