Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Calculating Running Streak over many records

It has come to my attention that sometimes there are more than 100,000 records for which a "running streak" should be calculated on, so my previous blog post http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx will not do.
But this will work, and work fast!

DECLARE  @Sample TABLE
        (
            Col1 INT,
            Col2 INT,
            Col3 INT,
            Col4 INT,
            Col5 INT,
            Col6 INT,
            Col7 INT,
            Col8 DATETIME
        )

INSERT  @Sample
SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:00' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:05' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:10' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:15' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:20' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:25' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:30' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:35' UNION ALL
SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:40'

SELECT      w.Col7,
            MIN(w.Col8)
FROM        (
                SELECT  Col7,
                        Col8,
                        ROW_NUMBER() OVER (ORDER BY Col8) AS recID
                FROM    @Sample
            ) AS w
INNER JOIN  (
                SELECT  Col7,
                        Col8,
                        ROW_NUMBER() OVER (PARTITION BY Col7 ORDER BY Col8) AS recID
                FROM    @Sample
            ) AS x ON x.Col7 = w.Col7
                AND x.Col8 = w.Col8
GROUP BY    w.Col7,
            w.recID - x.recID
ORDER BY    MIN(w.Col8)

Print | posted on Wednesday, August 12, 2009 7:24 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Feedback

Gravatar

# re: Calculating Running Streak over many records

Another idea - don't know how it compares...

--
select Col7, min(Col8) as Col8From, max(Col8) as Col8To, count(*) as Streak
from (select *, case Col7 when 1 then 1 else -1 end * (row_number() over (order by Col8) -
row_number() over (partition by Col7 order by Col8)) as p2 from @Sample) a
group by Col7, p2 order by 2
--/
8/13/2009 12:39 PM | Ryan Randall
Gravatar

# re: Calculating Running Streak over many records

Slightly more refined...

--
select Col7, min(Col8) as Col8From, max(Col8) as Col8To, count(*) as Streak
from (select *, (row_number() over (order by Col8) -
row_number() over (partition by Col7 order by Col8)) as p from @Sample) a
group by Col7, p order by 2
--/
8/13/2009 1:02 PM | Ryan Randall
Gravatar

# re: Calculating Running Streak over many records

Ryan, here is some feedback for you
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131540

I can't see why there is a difference...
8/21/2009 3:01 PM | Peso
Gravatar

# re: Calculating Running Streak over many records

The difference is that in the OP sample data in the link, there are a huge amount of duplicate records. Ryan's second suggestion will handle it, while my suggestion in the blog post will not.

However, if you remove the duplicates, both mine and Ryan's suggestion will both work.
8/22/2009 1:17 AM | Peso
Gravatar

# re: Calculating Running Streak over many records

This Query works fine. I Hope its also correct approach

DECLARE @Sample TABLE
(
id int,
Col1 INT,
Col2 INT,
Col3 INT,
Col4 INT,
Col5 INT,
Col6 INT,
Col7 INT,
Col8 int
)

INSERT @Sample
SELECT 1,43, 12345, 99887, 0, 0, 0, 0, 10 UNION ALL
SELECT 2,43, 12345, 99887, 0, 0, 0, 0, 20 UNION ALL
SELECT 3,43, 12345, 99887, 0, 0, 0, 0, 30 UNION ALL
SELECT 4,43, 12345, 99887, 0, 0, 0, 1, 40 UNION ALL
SELECT 5,43, 12345, 99887, 0, 0, 0, 1, 50 UNION ALL
SELECT 6,43, 12345, 99887, 0, 0, 0, 0, 60 UNION ALL
SELECT 7,43, 12345, 99887, 0, 0, 0, 0, 70 UNION ALL
SELECT 8,43, 12345, 99887, 0, 0, 0, 1, 80 UNION ALL
SELECT 9,43, 12345, 99887, 0, 0, 0, 0, 90




select s.*
from @Sample s
where s.col8 not in(
select s1.Col8
from @Sample s1
join
@Sample s2 on s1.id = s2.id+ 1 and s1.Col7 = s2.Col7
where s1.Col8 > s2.Col8)
8/6/2010 11:25 AM | dineshrajan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET