SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Detecting "Runs" or "Streaks" in your data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 02/16/2003 :  19:27:45  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 03/01/2003 :  13:58:44  Show Profile  Reply with Quote
are these run and streaks at all related to the ones in my underwear?

Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 03/01/2003 :  14:06:05  Show Profile  Reply with Quote
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.."
Go to Top of Page

FuKoMatic
Starting Member

4 Posts

Posted - 03/02/2003 :  01:36:57  Show Profile  Reply with Quote
hey now, it's not how big the database is, it's how you use the stored procedures

Go to Top of Page

dstaikos
Starting Member

Greece
1 Posts

Posted - 07/25/2003 :  07:34:11  Show Profile  Send dstaikos an ICQ Message  Reply with Quote
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...
Go to Top of Page

schuhtl
Posting Yak Master

USA
102 Posts

Posted - 10/18/2010 :  10:55:46  Show Profile  Reply with Quote
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')
Go to Top of Page

empire3
Starting Member

USA
1 Posts

Posted - 05/03/2012 :  23:13:13  Show Profile  Reply with Quote
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.
Go to Top of Page

haysjp
Starting Member

1 Posts

Posted - 11/20/2013 :  01:57:51  Show Profile  Reply with Quote
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')

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000