Article: Detecting "Runs" or "Streaks" in your data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Detecting "Runs" or "Streaks" in your data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-16 : 19:27:45

This article comes from Jeff Smith. Jeff writes "It can be difficult in a set-based language such as SQL to determine when a number of records in a row (when ordered by a unique key) have the same values in a particular field. For example, you may have a table of GameResults, with fields such as GameDate (primary key) and result (W for win, L for loss), and you may wish to find out what kinds of winning or losing streaks exist in your data."

Article Link.

FuKoMatic
Starting Member

4 Posts

Posted - 2003-03-01 : 13:58:44

are these run and streaks at all related to the ones in my underwear?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-03-01 : 14:06:05

quote:

are these run and streaks at all related to the ones in my underwear?

I'm not too sure but you must have a very small database....

DavidM

"SQL-3 is an abomination.."

FuKoMatic
Starting Member

4 Posts

Posted - 2003-03-02 : 01:36:57

hey now, it's not how big the database is, it's how you use the stored procedures

dstaikos
Starting Member

1 Post

Posted - 2003-07-25 : 07:34:11

Cool exercise :-)
I like doing things with SQL2000 functions, so here is a function that will return the length of a run at any random record.

Using the table definition:

create table TS(AA int not null identity, v int not null)

where AA is the order key and v the value the function is :

create function TSLen(@AA int) returns int
as
begin
declare @ret int, @V int

SELECT @V = v FROM TS WHERE AA = @AA

select @ret = count(*)+1 from ts as TS1
where TS1.AA < @AA
and TS1.v = @V
and TS1.AA > ISNULL(
(
select max(AA) from ts as TS1
where TS1.AA < @AA
and TS1.v <> @V
), 0)

return @ret
end

Then with a query like:

select AA, v, dbo.TSLen(AA) from ts order by AA ASC

you can see the data, and with a query like:

select AA, dbo.TSLen(AA) from ts where dbo.TSLen(AA) = (select max(dbo.TSLen(AA)) from ts)

you can see the AA of the records where the maximum run(s) have come to an end.

Enjoy!

Go with THE flow...

schuhtl
Posting Yak Master

102 Posts

Posted - 2010-10-18 : 10:55:46

I realize that this is a fairly old artilce but any help I could get would be appreciated. The data that I am working with is very similar to the data used in this article however my data contains multiple results for the same day, player and event. I would like to find the winning streak of a player(s) that spans multiple events/dates. For example...the data below should show that player 1000 had a 4 game winning streak, player 2000 had a 5 game winning streak and player 3000 had a 3 game winning streak.

CREATE TABLE [dbo].[GameResults](
[TournamentId] [int] NULL,
[PlayerId] [int] NULL,
[GameDate] [date] NULL,
[Result] [varchar](5) NULL
)

INSERT INTO GameResults (TournamentId, PlayerId, GameDate,Result)
VALUES
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'L'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W')

empire3
Starting Member

1 Post

Posted - 2012-05-03 : 23:13:13

This is a great algorithm by Jeff Smith. Wondering if he has tried modifying it for numeric data to find runs numbers below some threshold. We need something to detect runs of daily temperatures below some cutoff.

For example, for every calendar year, show the runs of temperatures less than 32 degrees. Seems to me you need a subquery using case to convert the number data into high or low, based on the temperature cutoff. But I can't get this to work.

haysjp
Starting Member

1 Post

Posted - 2013-11-20 : 01:57:51

I was curious if the solution to this was found. I have a very similar problem and am having trouble finding the correct way to handle multiple results for the same day. Any help would be appreciated! Thanks for the great article. It's still proving useful a decade later.

quote:
Originally posted by schuhtl

I realize that this is a fairly old artilce but any help I could get would be appreciated. The data that I am working with is very similar to the data used in this article however my data contains multiple results for the same day, player and event. I would like to find the winning streak of a player(s) that spans multiple events/dates. For example...the data below should show that player 1000 had a 4 game winning streak, player 2000 had a 5 game winning streak and player 3000 had a 3 game winning streak.

CREATE TABLE [dbo].[GameResults](
[TournamentId] [int] NULL,
[PlayerId] [int] NULL,
[GameDate] [date] NULL,
[Result] [varchar](5) NULL
)

INSERT INTO GameResults (TournamentId, PlayerId, GameDate,Result)
VALUES
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'L'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W')

rdunnivan
Starting Member

1 Post

Posted - 2014-10-09 : 16:40:52

quote:

CREATE TABLE [dbo].[GameResults](
[TournamentId] [int] NULL,
[PlayerId] [int] NULL,
[GameDate] [date] NULL,
[Result] [varchar](5) NULL
)

INSERT INTO GameResults (TournamentId, PlayerId, GameDate,Result)
VALUES
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'L'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W')

quote:
Originally posted by haysjp

I was curious if the solution to this was found. I have a very similar problem and am having trouble finding the correct way to handle multiple results for the same day. Any help would be appreciated! Thanks for the great article. It's still proving useful a decade later.
quote:
Originally posted by schuhtl

I realize that this is a fairly old artilce but any help I could get would be appreciated. The data that I am working with is very similar to the data used in this article however my data contains multiple results for the same day, player and event. I would like to find the winning streak of a player(s) that spans multiple events/dates. For example...the data below should show that player 1000 had a 4 game winning streak, player 2000 had a 5 game winning streak and player 3000 had a 3 game winning streak.

It can't be solved as it stands. There is no way to differentiate which games happened first on a given day. If you add a gameID parameter that was based on when the game was played you could do it with this.

SQL1
SELECT gameID,
PlayerID,
GameDate,
Result,
(SELECT count(*)
FROM GameResults G
WHERE G.Result <> GR.Result
AND G.gameID <= GR.gameID
AND G.PlayerID = GR.PlayerID) as RunGroup
FROM GameResults GR

SQL2
----
SELECT gameID,
PlayerID,
Result,
MIN(GameDate) as StartDate,
MAX(GameDate) as EndDate,
COUNT(*) as Games
FROM (SQL1) A
GROUP BY PlayerID, Result, RunGroup
ORDER BY Min(gameID)

pawankkmr
Starting Member

4 Posts

Posted - 2015-05-05 : 08:49:56

Solution to the above puzzle is

;WITH CTE1 AS
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY PlayerId ORDER BY GameDate) rnk
FROM GameResults
)
,CTE2 AS
(
SELECT *,
CASE WHEN Result = lag(Result)
over(PARTITION BY PlayerId order by rnk) THEN 0 ELSE 1 END cols
FROM CTE1 c2
)
,CTE3 AS
(
SELECT * , SUM(cols) OVER (PARTITION BY PlayerId ORDER BY rnk) grouper FROM CTE2
)
,CTE4 AS
(
SELECT PlayerId, grouper , COUNT(grouper) ConsecutiveWins FROM CTE3
GROUP BY PlayerId,grouper
)
SELECT PlayerId , MAX(ConsecutiveWins) ConsecutiveWins FROM CTE4 GROUP BY PlayerId

http://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/

Thanks !
Pawan Kumar Khowal
MSBISkills.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources