[SOLVED] Consecutive Positive Numbers

[borabora12]

I have a table that contains daily profit and loss. I was wondering how it would be possible to allow either PHP or MYSQL to calculate the number of consecutive up days. I can imagine this is pretty hard. I'm assuming you would use an a "while" loop somehow... thanks in advance.

 

Its for a trading database, im getting the PHP to calculate % positive days, % negative day, total return, etc... (I already figured out how to do those) but just a bit stuck on how would it be possible to calculate consecutive positive/negative days...

[PFMaBiSmAd]

The following thread contains two different ways of getting a count of consecutive values that you can probably make use of to accomplish what you want -

http://www.phpfreaks.com/forums/index.php/topic,227149.15.html

 

[fenway]

I don't know what you're getting at.

[borabora12]

+9

-6

-2

-7

+1

+4

+2

+5

+8

-9

-4

+2

-5

+7

 

 

As you can see the largest set of consecutive positive numbers is 5 whereas the largest set of consecutive negative numbers is 3. I hope this makes sense more than before =/

 

[corbin]

+9

-6

-2

-7

+1

+4

+2

+5

+8

-9

-4

+2

-5

+7

 

 

As you can see the largest set of consecutive positive numbers is 5 whereas the largest set of consecutive negative numbers is 3. I hope this makes sense more than before =/

 

 

 

The following thread contains two different ways of getting a count of consecutive values that you can probably make use of to accomplish what you want -

http://www.phpfreaks.com/forums/index.php/topic,227149.15.html

 

 

 

Did you read his link?

[borabora12]

I don't know what you're getting at.

 

Did you read his link?

 

I was just trying to answer his question as I am still a bit confused on the link he provided =/

[borabora12]

The following thread contains two different ways of getting a count of consecutive values that you can probably make use of to accomplish what you want -

http://www.phpfreaks.com/forums/index.php/topic,227149.15.html

 

I looked at the code but, at least to my understanding, your matching up the user ID and then just making sure that he didn't bid more than 4 times in the last 4 overall bids...

 

But my problem is not comparing the user to the last 4 bids but more to the extent that it is checking the whole column to find the largest consecutive set of either positive numbers or negative numbers.

[borabora12]

I was wondering if this could be a solution but I need help actually making it:

 

Loading all the column data into an array and then change the numbers in the array into a number 1 if it is negative and then somehow make it look at the sets of 1s and just count the largest one...

[corbin]

The following thread contains two different ways of getting a count of consecutive values that you can probably make use of to accomplish what you want -

http://www.phpfreaks.com/forums/index.php/topic,227149.15.html

 

I looked at the code but, at least to my understanding, your matching up the user ID and then just making sure that he didn't bid more than 4 times in the last 4 overall bids...

 

But my problem is not comparing the user to the last 4 bids but more to the extent that it is checking the whole column to find the largest consecutive set of either positive numbers or negative numbers.

 

 

Yes, but it's the same concept.

 

mysql> SELECT * FROM consec;

+------+

| c    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|  -3 |

|    3 |

|    3 |

|    3 |

|    3 |

|    3 |

|  -5 |

|  -6 |

|  -7 |

+------+

13 rows in set (0.00 sec)

 

mysql> SELECT @count := 0; SELECT @count := IF(c > 0, @count + 1, 0) as cnt FROM consec ORDER BY cnt DESC LIMIT 1;

+-------------+

| @count := 0 |

+-------------+

|          0 |

+-------------+

1 row in set (0.00 sec)

 

+------+

| cnt  |

+------+

|    5 |

+------+

1 row in set (0.00 sec)

 

mysql> SELECT @count := 0; SELECT @count := IF(c < 0, @count + 1, 0) as cnt FROM consec ORDER BY cnt DESC LIMIT 1;

+-------------+

| @count := 0 |

+-------------+

|          0 |

+-------------+

1 row in set (0.00 sec)

 

+------+

| cnt  |

+------+

|    3 |

+------+

1 row in set (0.00 sec)

 

It would be better to store it precalculated though, especially if you plan on calculating it often.

[borabora12]

SELECT @count := 0; SELECT @count := IF(daily_difference < 0, @count + 1, 0) as cnt FROM the_table ORDER BY cnt DESC LIMIT 1

 

 

corbin thank you sooo much, that worked out great! your a genius!

[corbin]

I didn't do anything but adapt PFMaBiSmAd's solution.

 

But, no problem ;p.