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)
Legacy Comments
|
Ryan Randall
2009-08-13 |
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 --/ |
|
Ryan Randall
2009-08-13 |
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 --/ |
|
Peso
2009-08-21 |
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... |
|
Peso
2009-08-22 |
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. |
|
dineshrajan
2010-08-06 |
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) |