Page 2 of 4

Re: Tournament Bust out hands or last hand tournament

PostPosted: Fri Dec 06, 2013 4:46 pm
by kraada
What stat were you trying to add? The site id is in the tourney_summary, tourney_results and tourney_hand_summary tables.

Re: Tournament Bust out hands or last hand tournament

PostPosted: Fri Dec 06, 2013 4:51 pm
by Dutchraise72
tourney_hand_summary.hand_no i created a copy of this one i tried to ad a Max value. Without max value it shows rows .

Re: Tournament Bust out hands or last hand tournament

PostPosted: Fri Dec 06, 2013 9:38 pm
by PJs Ronin
Lateral thinking here ... (courtesy a Scotch and Dry)

Just created a quick Tourney report that just listed all hands played, and nothing else. I added 2 stats to the report, "Chips Won" (obvious) and "Chips" (which is hero starting chips at commencement of hand). Then added the quick filter:

#Chips Won#=(-#Chips#) (note the minus sign)

Logic being that if hero lost all available chips, then he must be busted. I also selected a quick filter to limit to "this month"

The report gave me 8 hands.
View Stats > T > Results > Overview > By Tournament yields 10 Tourneys for this month.
Dissection is that I won 2 tourneys and didn't win 8 others.

Note: I mean "didn't win" and not "lost".

So the above expression finds the bustout hands... your job, should you choose to accept it, is to link that back to the tourney id.

Then again, double clicking on each of those lines brings up the replayer. Maybe that's all you want?

Re: Tournament Bust out hands or last hand tournament

PostPosted: Fri Dec 06, 2013 9:53 pm
by Dutchraise72
No this report is the same as i use this expression

(tourney_hand_player_statistics.amt_before = tourney_hand_player_statistics.amt_bet_ttl AND tourney_hand_player_statistics.flg_won_hand = false)

I get 17 tourneys and 25 hands. This is because i play a lot of rebuy sattelites and rebuys is seen as a bust hand and i don't want that.

Re: Tournament Bust out hands or last hand tournament

PostPosted: Fri Dec 06, 2013 10:18 pm
by PJs Ronin
For this year I entered 16 rebuy tourneys but only rebought in 2 of them (didn't win any of them)... so according to your code I should end up with 18 busto hands, but my script only gives 14 hands.

There might be a (subtle) difference between your code and my script.

Re: Tournament Bust out hands or last hand tournament

PostPosted: Sat Dec 07, 2013 6:35 am
by pt4pt4pt4
I use this in the expression filter for my report:

Code: Select all
- amt_won - amt_chips = 0


Now you can customize the games with quick filters, so if you play SNGs and Satellite / MTT Rebuys, have a quick filter for the SNG description. Also have a OR for the Rebuy flag description, but include greater then for the the blind level after the Rebuys/addons are over.

Re: Tournament Bust out hands or last hand tournament

PostPosted: Sat Dec 07, 2013 7:06 am
by PJs Ronin
pt4pt4pt4 wrote:I use this in the expression filter for my report:

Code: Select all
- amt_won - amt_chips = 0


Now you can customize the games with quick filters, so if you play SNGs and Satellite / MTT Rebuys, have a quick filter for the SNG description. Also have a OR for the Rebuy flag description, but include greater then for the the blind level after the Rebuys/addons are over.

That doesn't acount for rebuys where it will list multiple busto hands hands in the same rebuy MTT. Dutch only wants the last hand.

Re: Tournament Bust out hands or last hand tournament

PostPosted: Sat Dec 07, 2013 8:01 am
by BillGatesIII
First of all, PJs Ronin, your comments make me smile :) Secondly, this post is going to be much longer than I thought it would be.

The answer to the question 'give me the last hand of any tournament' is quite simple. For example, this is a fast way to do this in PostgreSQL.
Code: Select all
select distinct on (id_tourney) id_hand
from tourney_hand_summary
order by id_tourney, id_hand desc

Unfortunately, you can't use this code in a report filter expression because PT4 messes up the order by clause resulting in an error.

Another way to accomplish this is by using a subquery which is painfully slow if you don't filter for Today or This Month. Something like this.
Code: Select all
id_hand = (select max(ths.id_hand) from tourney_hand_summary ths where ths.id_tourney = tourney_hand_player_statistics.id_tourney)

So I think the best solution to get the last hand in PT4 is to use a filter expression with a window function, for example.
Code: Select all
id_hand in (
  select s.id_hand
  from (
    select ths.id_hand, max(ths.id_hand) over (partition by ths.id_tourney) last_hand
    from tourney_hand_summary ths) as s
  where s.id_hand = s.last_hand)


And now things are getting weird.
max id_hand.png
max id_hand.png (13.89 KiB) Viewed 15389 times

How can the last hand in a tourney be a hand where I folded preflop :?

So I grab a nice cup of Colombia Organic coffee, fire up pgAdmin and run some queries.
tourney summary.png
tourney summary.png (10.15 KiB) Viewed 15389 times

How sweet. The column id_hand is not in the same order the hands were played. It seems like if you import hands manually, they are imported from last to first.

If this is true, and most of the time databases don't lie, I'll have to rewrite a lot of my advanced custom stats (and have to inform some other people as well).
@PT4 Devs, could you please confirm (or deny) this behaviour?


Anyway, getting back to the original question, I think this expression will work.
Code: Select all
exists (
  select 1
  from (
    select ths.id_tourney, max(ths.date_played) over (partition by ths.id_tourney) last_played
    from tourney_hand_summary ths) as ths_hands
  where tourney_hand_summary.date_played = ths_hands.last_played
  and tourney_hand_summary.id_tourney = ths_hands.id_tourney)

If you put this into an Expression Filter, you'll get every last hand played of a tourney.

Lastly, if you only want the bust out hands, add a simple filter 'Player Lost Hand'.
simple filter.png

Re: Tournament Bust out hands or last hand tournament

PostPosted: Sat Dec 07, 2013 9:26 am
by kraada
I will look into the back to front issue and get back to you.

Re: Tournament Bust out hands or last hand tournament

PostPosted: Sat Dec 07, 2013 10:27 am
by Dutchraise72
BillGatesIII wrote:
Anyway, getting back to the original question, I think this expression will work.
Code: Select all
exists (
  select 1
  from (
    select ths.id_tourney, max(ths.date_played) over (partition by ths.id_tourney) last_played
    from tourney_hand_summary ths) as ths_hands
  where tourney_hand_summary.date_played = ths_hands.last_played
  and tourney_hand_summary.id_tourney = ths_hands.id_tourney)

If you put this into an Expression Filter, you'll get every last hand played of a tourney.

Lastly, if you only want the bust out hands, add a simple filter 'Player Lost Hand'.
simple filter.png


Thank you very much. This is what i was looking for.

highfalutin