3

I have a mySQL poker database called TournamentXPlayer that contains a primary index called TournamentXPlayerID and TournamentID, PlayerID, Finish, and Payout. I've been looking at ways to count each players longest streak of finishing with a cash prize. Later I would like to include other things like a players personal streak (Not all player play every game but some do really well when they do play), longest winning streaks, and even longest streak without winning a prize. However at the moment I can't work out how best to count a streak at all. Can this be done?

Thanks Terry

Terry Day
  • 53
  • 5

2 Answers2

1

I assumed that tournamentID is automatically incremented so it provides the chronology of the data.

This is the classic problem of the order by inside a group. For that purpose, you need to consider variables such as :

mysql> set @p_id:=-1; set @streak:=0;
mysql> select playerID,max(streak) from (select playerID,@streak:=if(Payout=0,0,if(@p_id=playerID,@streak+1,1)) streak, @p_id:=playerID from (select playerID,TournamentID,Payout from table order by 1,2) a) a group by 1;

In this example, the etaps are :

  • sort by player then tournament
  • for each player :
    • increment the streak variable if there was a payout
    • set to 0 if not
  • set streak to 0 if there was a change of player. p_id!=playerID. p_id encapsuled the information of the last player considered.
Guillaume Thomas
  • 2,220
  • 2
  • 24
  • 33
  • Thanks. I'm going to give this ago a bit later. I'll let you know how I get on. – Terry Day Jul 26 '11 at 11:19
  • This is great and does the job when run as a stand alone query. My problems is that mySQL won't let me save it as a view due to the embedded queries and I can't seem to separate the embedded queries due to the user-defined variables. I've tried converting it to PHP and running it on a webpage but it displays max(streak) as 1. I believe this is because I don't define the variables first. Do you know either A. How this can be re-written as separate querys that can be saved as views. Or B. how to covert it in its entirety to PHP code? – Terry Day Jul 26 '11 at 12:37
  • I guess it could work if you create a view for each subquery. – Guillaume Thomas Jul 26 '11 at 13:21
  • if you're using PDO, there is some pre processing on the query so the results might be different as a stand alone query vs the query in php. If you don't want the pre processing you can do setAttribute(PDO::ATTR_EMULATE_PREPARES, false); though there are be some other drawbacks to doing this – sk8asd123 Jan 24 '14 at 19:04
0

When You are going to calculate streaks, You need to know the time, when a player finished the tournament. Otherwise, You will get wrong results if a players is multitabling, because, he might be playing one superstack tourney for 15 hours and finish in the money and in the meanwhile register and drop out of multiple hyperturbo tourneys. You can sort the tournaments by ID-s (relative to starting time), but You'll never get the right result, if You don't have the time, when player finished.

If we assume, that players don't multitable at all, then use the following algorithm:

  1. Read all one player's tournaments.
  2. Sort them by Tournament ID
  3. Search for longest streak
  4. Output the longest streak

Leave a comment, if You got questions, I will edit/complete my answer

bogatyrjov
  • 5,317
  • 9
  • 37
  • 61
  • Thanks for this. Luckily it a live tournament database so multitabling isn't possible and players finish in the order they go out. – Terry Day Jul 26 '11 at 11:19