Find consecutive in a set

Question

Post reply

Find consecutive in a set

keymoo

SSCrazy

Points: 2975
More actions
January 6, 2011 at 6:23 am

#243232

Hi there,
I have a table called TradeView:
CREATE TABLE [dbo].[tradeView](
[tradeId] [int] NOT NULL,
[tradeClosedDateTime] [datetime] NOT NULL,
[pipsProfit] [decimal](38, 6) NOT NULL,
CONSTRAINT [PK_tradeView] PRIMARY KEY CLUSTERED
([tradeId] ASC)
)
GO
With this sample data:
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(184,'Nov 5 2010 7:00:00:000PM',129.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(62,'Sep 30 2010 3:00:00:000PM',31.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(37,'Sep 28 2010 3:00:00:000PM',168.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(160,'Sep 24 2010 7:00:00:000PM',310.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(173,'Sep 24 2010 3:00:00:000PM',53.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(98,'Sep 24 2010 3:00:00:000PM',-364.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(38,'Sep 23 2010 7:00:00:000PM',837.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(138,'Sep 23 2010 3:00:00:000AM',226.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(36,'Sep 20 2010 11:00:00:000AM',71.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(159,'Sep 16 2010 11:00:00:000AM',126.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(172,'Sep 16 2010 3:00:00:000AM',385.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(125,'Sep 16 2010 3:00:00:000AM',314.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(16,'Sep 10 2010 7:00:00:000AM',-111.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(61,'Sep 9 2010 11:00:00:000AM',-35.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(77,'Sep 8 2010 3:00:00:000PM',96.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(149,'Sep 8 2010 11:00:00:000AM',-81.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(35,'Sep 8 2010 3:00:00:000AM',-100.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(137,'Sep 7 2010 3:00:00:000AM',-16.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(97,'Sep 3 2010 11:00:00:000AM',231.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(171,'Sep 3 2010 3:00:00:000AM',20.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(60,'Sep 2 2010 11:00:00:000AM',-46.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(33,'Sep 1 2010 11:00:00:000AM',-150.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(96,'Aug 27 2010 7:00:00:000PM',-68.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(148,'Aug 26 2010 11:00:00:000PM',366.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(15,'Aug 26 2010 7:00:00:000PM',-114.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(183,'Aug 26 2010 3:00:00:000AM',1067.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(95,'Aug 25 2010 7:00:00:000PM',74.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(76,'Aug 25 2010 3:00:00:000AM',683.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(59,'Aug 25 2010 3:00:00:000AM',105.000000)
INSERT INTO [tradeView] ([tradeId],[tradeClosedDateTime],[pipsProfit])VALUES(124,'Aug 24 2010 7:00:00:000PM',185.000000)
I am trying to write a query to produce extra columns that will show me for each tradeId the number of previous consecutive losers when ordered by tradeClosedDateTime DESC. A winner is defined by pipsProfit being >= 0 and a loser is defined by pipsProfit < 0. If the previous trade was a winner then the consecutiveLosers value would be 0. For example the first 15 rows should look like this (csv format):
tradeId,tradeClosedDateTime,pipsProfit,consecutiveLosers
184,2010-11-05 19:00,129,0
62,2010-09-30 15:00,31,0
37,2010-09-28 15:00,168,0
160,2010-09-24 19:00,310,0
173,2010-09-24 15:00,53,1
98,2010-09-24 15:00,-364,0
38,2010-09-23 19:00,837,0
138,2010-09-23 03:00,226,0
36,2010-09-20 11:00,71,0
159,2010-09-16 11:00,126,0
172,2010-09-16 03:00,385,0
125,2010-09-16 03:00,314,2
16,2010-09-10 07:00,-111,1
61,2010-09-09 11:00,-35,0
77,2010-09-08 15:00,96,0
Any ideas on how to write this query?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26967 More actions · Answer 1

Try this. You could also use the 'quirky update' method from here

http://www.sqlservercentral.com/articles/T-SQL/68467/

which would be very fast.

WITH CTE1 AS (

SELECT tradeId,

tradeClosedDateTime,

pipsProfit,

CASE WHEN pipsProfit>=0 THEN 1 ELSE 0 END AS wl,

ROW_NUMBER() OVER(ORDER BY tradeClosedDateTime,tradeId) AS rn,

ROW_NUMBER() OVER(ORDER BY tradeClosedDateTime,tradeId)-ROW_NUMBER() OVER(PARTITION BY CASE WHEN pipsProfit>=0 THEN 1 ELSE 0 END ORDER BY tradeClosedDateTime,tradeId) AS rnDiff

FROM tradeView),

CTE2 AS (

SELECT rn,

ROW_NUMBER() OVER(PARTITION BY rnDiff ORDER BY rn) AS GrpNum

FROM CTE1

WHERE wl=0)

SELECT c1.tradeId,

c1.tradeClosedDateTime,

c1.pipsProfit,

COALESCE(c2.GrpNum,0) AS consecutiveLosers

FROM CTE1 c1

LEFT OUTER JOIN CTE2 c2 ON c1.rn=c2.rn+1

ORDER BY c1.tradeClosedDateTime DESC,c1.tradeId DESC;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

WayneS SSC Guru Points: 95460 More actions · Answer 2

Thanks for providing the table DDL and sample data.

Here's the quirky update method.

-- First, we need a different clustered index.

-- So, dump the data into a temp table with the proper clustered index.

IF OBJECT_ID('tempdb..#table','U') IS NOT NULL DROP TABLE #table;

CREATE TABLE #table (

tradeId INT,

tradeClosedDateTime Datetime,

pipsProfit INT,

consecutiveLosers INT,

PRIMARY KEY CLUSTERED (tradeClosedDateTime, tradeID));

INSERT INTO #table (tradeId, tradeClosedDateTime,pipsProfit)

SELECT tradeId, tradeClosedDateTime, pipsProfit

FROM tradeView;

-- This form of the UPDATE statement has some particular rules.

-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/

-- for a complete discussion of how this works, and all of the rules for utilizing it.

-- Also, for a discussion of the Safety Check enhancements, see this post:

-- http://www.sqlservercentral.com/Forums/FindPost1001833.aspx

-- declare and initialize variables needed for this update statement.

DECLARE @tradeClosedDateTime datetime,

@consecutiveLosers INT,

@prevpipsProfit INT,

@Sequence INT;

SET @Sequence = 0;

SET @prevpipsProfit = 1;

WITH SafeTable AS

(

-- Build a common table expression with the necessary columns from the base table

-- Add in the safety check sequence number to verify that the update is being

-- performed in the proper order.

-- The columns in the ROW_NUMBER() ORDER BY clause MUST be the same columns,

-- and in the same sort order as the clustered index!

SELECT t.tradeId, t.tradeClosedDateTime, t.pipsProfit, t.consecutiveLosers,

Sequence = ROW_NUMBER() OVER (ORDER BY tradeClosedDateTime, tradeID)

FROM #table t

)

UPDATE s

-- update

SET @consecutiveLosers = consecutiveLosers = CASE WHEN Sequence = @Sequence + 1 THEN

CASE WHEN @prevpipsProfit > 0 THEN 0

ELSE @consecutiveLosers + 1 END

ELSE 1/0 END,

@Sequence = CASE WHEN Sequence = @Sequence + 1 THEN @Sequence + 1

ELSE 1/0 END, -- safety check to ensure processing in proper order

@tradeClosedDateTime = tradeClosedDateTime, -- anchor column

@prevpipsProfit = pipsProfit -- get current value - on the next row,

-- it will be the previous value

FROM SafeTable s WITH (TABLOCKX) -- ensure no one else can get to the table

-- (Not needed for temp table, but it

-- must be present on permanent tables!)

OPTION (MAXDOP 1); -- ensure no parallelism occurs

-- show the results.

SELECT *

FROM #table

ORDER BY tradeClosedDateTime DESC;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2