Tournament Bust out hands or last hand tournament

Discuss how to create custom stats, reports and HUD profiles and share your creations.

Moderators: WhiteRider, kraada, Flag_Hippo, morny, Moderators

Re: Tournament Bust out hands or last hand tournament

Postby kraada » Fri Dec 06, 2013 4:46 pm

What stat were you trying to add? The site id is in the tourney_summary, tourney_results and tourney_hand_summary tables.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Tournament Bust out hands or last hand tournament

Postby Dutchraise72 » Fri Dec 06, 2013 4:51 pm

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 .
Dutchraise72
 
Posts: 158
Joined: Fri Jul 10, 2009 7:32 pm

Re: Tournament Bust out hands or last hand tournament

Postby PJs Ronin » Fri Dec 06, 2013 9:38 pm

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?
PJs Ronin
 
Posts: 978
Joined: Fri Dec 25, 2009 11:55 pm

Re: Tournament Bust out hands or last hand tournament

Postby Dutchraise72 » Fri Dec 06, 2013 9:53 pm

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.
Dutchraise72
 
Posts: 158
Joined: Fri Jul 10, 2009 7:32 pm

Re: Tournament Bust out hands or last hand tournament

Postby PJs Ronin » Fri Dec 06, 2013 10:18 pm

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.
PJs Ronin
 
Posts: 978
Joined: Fri Dec 25, 2009 11:55 pm

Re: Tournament Bust out hands or last hand tournament

Postby pt4pt4pt4 » Sat Dec 07, 2013 6:35 am

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.
pt4pt4pt4
 
Posts: 1097
Joined: Fri Feb 03, 2012 12:17 am

Re: Tournament Bust out hands or last hand tournament

Postby PJs Ronin » Sat Dec 07, 2013 7:06 am

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.
PJs Ronin
 
Posts: 978
Joined: Fri Dec 25, 2009 11:55 pm

Re: Tournament Bust out hands or last hand tournament

Postby BillGatesIII » Sat Dec 07, 2013 8:01 am

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 15393 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 15393 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
BillGatesIII
 
Posts: 740
Joined: Fri Dec 16, 2011 6:50 pm

Re: Tournament Bust out hands or last hand tournament

Postby kraada » Sat Dec 07, 2013 9:26 am

I will look into the back to front issue and get back to you.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Tournament Bust out hands or last hand tournament

Postby Dutchraise72 » Sat Dec 07, 2013 10:27 am

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.
Dutchraise72
 
Posts: 158
Joined: Fri Jul 10, 2009 7:32 pm

PreviousNext

Return to Custom Stats, Reports and HUD Profiles

Who is online

Users browsing this forum: No registered users and 11 guests

cron
highfalutin