Number of times player stacked with hand 1P-

Forum for users that want to write their own custom queries against the PT database either via the Structured Query Language (SQL) or using the PT3 custom stats/reports interface.

Moderator: Moderators

Number of times player stacked with hand 1P-

Postby renskav » Mon Feb 16, 2009 6:22 pm

I am trying to build a read based stat in accordance with the advanced HUD config, i want it to show the number of times the player was stacked (won less than -60 PTBB), and his final holding was a pair or less and no OESDs or FDs, and i am feeling somewhat overwhelmed..

Here's what i got so far:

sum(if[holdem_hand_player_statistics.amt_won / (holdem_limit.amt_bb * holdem_limit.val_nlpl_mult)<-60 AND holdem_hand_player_statistics.id_final_hand>290,1,0])

Now i want to exclude all the hands where the player held an FD, or an OESD (really what im trying to do is isolate the people who stack with TPTK-). Pointers on what database fields to look into would be greatly appreciated.

Thanks. /Ren
renskav
 
Posts: 11
Joined: Fri Feb 13, 2009 12:15 pm

Re: Number of times player stacked with hand 1P-

Postby WhiteRider » Mon Feb 16, 2009 7:03 pm

I don't have time to dig into this just now (it's bedtime for me!) but Kraada has done some work on filters of this kind and he'll be back tomorrow and I'm sure he'll be able to help you.
WhiteRider
Moderator
 
Posts: 54025
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Number of times player stacked with hand 1P-

Postby Tru80 » Tue Feb 17, 2009 4:28 pm

Thanks for the info in the other thread. Where did you find the numbers for hand ranks?

For your stat, to exclude FDs and OESDs you could use

AND NOT (holdem_hand_player_combinations.flg_f_flush_draw OR holdem_hand_player_combinations.flg_f_straight_draw)
AND NOT (holdem_hand_player_combinations.flg_t_flush_draw OR holdem_hand_player_combinations.flg_t_straight_draw)

you might also want to include double belly busters (holdem_hand_player_combinations.flg_x_2gutshot_draw)

Using amount won <-60 will exclude the times they get it in with TP and have the best hand and hold and if id_final_hand is the hand value at showdown, it will exclude the times they get it in with TP and improve on the turn or river. If that's the case, you might want to do it street by street and use the enum_allin stats for that street. Something like:

(holdem_hand_player_statistics.enum_allin = 'F'
OR (holdem_hand_player_statistics.enum_face_allin = 'F'
AND (holdem_hand_player_statistics.enum_face_allin_action = 'C' OR holdem_hand_player_statistics.enum_face_allin_action = 'R')))
AND (some stat for a 1 pair hand on the flop ie. holdem_hand_player_combinations.flg_f_1pair or holdem_hand_player_combinations.id_f_hand_strength > #)

OR (duplicate for each street)

To exclude the times they get it in w/TP vs a shortstack you could use

holdem_hand_summary.amt_f_effective_stack > 60 (or whatever other #)

Problems with this might be if there is a shortstack in the hand on the flop and the player still gets it in with TP vs a normal stack I don't think it will be counted. Also, I'm not sure if the enum_face_allin stat would be counted in a situation where the player with TP is covered by his opponent who doesn't go all in but bets enough to put the TP guy all in. You could also use stats like amt_x_bet, amt_x_bet_facing (along with call or raise), amt_x_raise and amt_x_raise_facing (along with call or raise) if you're not as concerned with how often someone gets their whole stack in with TP but how often someone will get > X bbs in with it.

I haven't used these stats much so I could be way off and there's probably a more efficient way to do it but hopefully there's some stuff here you can try.
Tru80
 
Posts: 22
Joined: Mon May 12, 2008 2:01 am

Re: Number of times player stacked with hand 1P-

Postby renskav » Tue Feb 17, 2009 5:17 pm

Great stuff, many good leads, will experiment with it tomorrow!

I found the hand ranking numbers by checking directly in the database.. you can start pgadmin III (under postgresql in your start menu) > connect to your database > databases > your pt3database > schemas > public > tables > right click on 'lookup_hand_ranks', choose view data > view all rows.
renskav
 
Posts: 11
Joined: Fri Feb 13, 2009 12:15 pm

Re: Number of times player stacked with hand 1P-

Postby kraada » Tue Feb 17, 2009 6:20 pm

Tru80's information is good, and I'd also consider looking at it street by street to make sure you're getting exactly what you want. If the board on the river reads:
K73T3 and your opponent has AK his hand rank will be 233 (kings and threes), which isn't really what you want to be looking at (since it's very different than K3 on a K73T6 board). It'll end up being an awfully long list of conditions, but that's really the whole idea behind read based stats as I imagined them: find very specific circumstances like these, then be able to find out the values quickly at the table while playing. So I wholeheartedly approve of your endeavor and if you need a hand refining things in the next stage, I'll be around pretty much all the time for a while, so feel free to ask. And if you'd be willing to share your read based stat when you're done please let me know and I'll add it in the next version of the Advanced HUD.
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Number of times player stacked with hand 1P-

Postby renskav » Wed Feb 18, 2009 4:05 pm

I will definetly share this stat once it is finished, and you are more than welcome to include it in the advanced HUD if i ever get it to work.

I feel i have come a bit of the way, thanks to tru80 and kraada's nudge in the right direction.

I have made four stats under the section holdem_cash hand:

The first three flag the hands that i am interested in: "TPTK type hands or less", made with 0 or 1 hole cards (which should eliminate PPs, and hands such as those kraada mentioned in the last post), and no straight, flush or double-gutter draws. One stat/flag for each street. Possibly you might want to include underpairs in this, as technically they constitute the TPTK- range in my head atleast, but i am leaving that out for later modifications.

'flg_f_tptk_type_hand_or_less':

((holdem_hand_player_combinations.flg_f_highcard or holdem_hand_player_combinations.flg_f_1pair) and (holdem_hand_player_combinations.val_f_hole_cards_used = 0 or holdem_hand_player_combinations.val_f_hole_cards_used = 1) and not (holdem_hand_player_combinations.flg_f_straight_draw or holdem_hand_player_combinations.flg_f_2gutshot_draw or holdem_hand_player_combinations.flg_f_flush_draw))

'flg_t_tptk_type_hand_or_less':

((holdem_hand_player_combinations.flg_t_highcard or holdem_hand_player_combinations.flg_t_1pair) and not (holdem_hand_player_combinations.flg_t_straight_draw or holdem_hand_player_combinations.flg_t_2gutshot_draw or holdem_hand_player_combinations.flg_t_flush_draw) and (holdem_hand_player_combinations.val_t_hole_cards_used = 0 or holdem_hand_player_combinations.val_t_hole_cards_used = 1))

'flg_r_tptk_type_hand_or_less':

((holdem_hand_player_combinations.flg_r_highcard or holdem_hand_player_combinations.flg_r_1pair) and (holdem_hand_player_combinations.val_r_hole_cards_used = 0 or holdem_hand_player_combinations.val_r_hole_cards_used = 1))

And finally a stat 'flg_overplayed_tptk' which combines the three previous stats, and checks if you've played for more than 60PTBB in total up to that street:

(flg_f_tptk_type_hand_or_less and (amt_bet_f / (holdem_limit.amt_bb * holdem_limit.val_nlpl_mult) > 60)) or (flg_t_tptk_type_hand_or_less and ((amt_bet_f + amt_bet_t) / (holdem_limit.amt_bb * holdem_limit.val_nlpl_mult) > 60)) or (flg_r_tptk_type_hand_or_less and ((amt_bet_f + amt_bet_t+amt_bet_r) / (holdem_limit.amt_bb * holdem_limit.val_nlpl_mult) > 60))

(happily, amt_bet_f seems to include all types of money in pot, both bets calls and raises, which saved me a bit of trouble, maybe it should be called amt_put_in_pot_f instead - seems more logical)

I am pretty content up to this point that i have a flag that flags for what i am interested in, but now that i am trying to sum this all up under section holdem_cash_player_statistics i have hit my head against the wall again. Is there any way i can use stats from section holdem_cash_hand or the database table holdem_hand_player_combinations in section holdem_cash_player_statistics? I cant seem to find them under 'insert', and when i try to type it in manually the SQL-validation fails. Have i walked in to a dead end?

Another nudge would be awesome.

Cheers //ren.
renskav
 
Posts: 11
Joined: Fri Feb 13, 2009 12:15 pm

Re: Number of times player stacked with hand 1P-

Postby kraada » Wed Feb 18, 2009 4:44 pm

All of those flags look good, but the thing is you can't access Holdem Cash Hand flags in Holdem Cash Player Statistics.

But the good news is you should still be able to make this stat.

In HCPS what you do is you build each stat as a sum of an if statement incrementing each time the if statement is true. You can't quite use one column in another quite as easily in HCPS so you have to actually combine. I'll show you how to do the flop column and the rest should be simple enough. Then you just build the stat to display the sum of the columns.

So, cnt_f_overplayed_tptk_type_hand_or_less:

sum(if[ ((holdem_hand_player_combinations.flg_f_highcard or holdem_hand_player_combinations.flg_f_1pair) and (holdem_hand_player_combinations.val_f_hole_cards_used = 0 or holdem_hand_player_combinations.val_f_hole_cards_used = 1) and not (holdem_hand_player_combinations.flg_f_straight_draw or holdem_hand_player_combinations.flg_f_2gutshot_draw or holdem_hand_player_combinations.flg_f_flush_draw) and (holdem_hand_player_detail.amt_bet_f / (holdem_limit.amt_bb * holdem_limit.val_nlpl_mult) > 60)), 1, 0])

This will increment each time they've overplayed a tptk type hand in the manner you've described.

Then in the final stat cnt_f_overplayed_tptk_type_hand_or_less + cnt_t_overplayed... + cnt_r_overplayed... will tell you how many times they've done it total.

One question though: are you sure you don't want to include hands like overplaying TT on a QJ3 board? That's a pretty poor play imho, and might be something to consider . . .
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Number of times player stacked with hand 1P-

Postby renskav » Wed Feb 18, 2009 5:48 pm

Very good, I now have a semi working version of the stat, and I am dismayed to see that i have overplayed 'TPTK or less' atleast 14 times ;) Instead of splitting it up into three cnt's like you, I made one huge blob. Not very ledgible, but the principles should be clear from my earlier posts.

holdem_cash_player_statistics/cnt_overplayed_tptk:

sum(if[(((holdem_hand_player_combinations.flg_f_highcard or holdem_hand_player_combinations.flg_f_1pair) and (holdem_hand_player_combinations.val_f_hole_cards_used = 0 or holdem_hand_player_combinations.val_f_hole_cards_used = 1) and not (holdem_hand_player_combinations.flg_f_straight_draw or holdem_hand_player_combinations.flg_f_2gutshot_draw or holdem_hand_player_combinations.flg_f_flush_draw)) and (holdem_hand_player_detail.amt_bet_f / (holdem_limit.amt_bb * holdem_limit.val_nlpl_mult) > 40)) or (((holdem_hand_player_combinations.flg_t_highcard or holdem_hand_player_combinations.flg_t_1pair) and not (holdem_hand_player_combinations.flg_t_straight_draw or holdem_hand_player_combinations.flg_t_2gutshot_draw or holdem_hand_player_combinations.flg_t_flush_draw) and
(holdem_hand_player_combinations.val_t_hole_cards_used = 0 or holdem_hand_player_combinations.val_t_hole_cards_used = 1)) and ((holdem_hand_player_detail.amt_bet_f + holdem_hand_player_detail.amt_bet_t) / (holdem_limit.amt_bb * holdem_limit.val_nlpl_mult) > 40)) or (((holdem_hand_player_combinations.flg_r_highcard or holdem_hand_player_combinations.flg_r_1pair) and (holdem_hand_player_combinations.val_r_hole_cards_used = 0 or holdem_hand_player_combinations.val_r_hole_cards_used = 1)) and ((holdem_hand_player_detail.amt_bet_f + holdem_hand_player_detail.amt_bet_t+holdem_hand_player_detail.amt_bet_r) / (holdem_limit.amt_bb * holdem_limit.val_nlpl_mult) > 40)),1,0])

I changed the magic number for overplayage to 40PTBB, which still seems to yield good results as far as i can see, lowering it further to 30PTBB might be reasonable.

A few more mods still required:
* including underpairs (this will be a bit tough, I probably have to look up the board cards from holdem_hand_summary, figure out which one is biggest and compare that with the PP-rank, as i dont want to consider pushing overpairs as overplaying). I will take a look at this tomorrow I hope.
* investigating where a good boundary for PTBB is.
* creating the front end for the read-based HUD-stat.

And a few concerns:
- I have some concern the stat might be including overplays where you are in the hand, but not the one with the weak hand - using the 'flg_f_tptk_type_hand_or_less' stat above to generate a HCH-report yields some wierd results with hands being included where i am the one with the strong hand - any ideas if this is the stat or the report that is the culprit? Also my db hangs when i try to use 'flg_overplayed_tptk' to generate a HCH-report, which is a tad discomforting, the cnt-based stat seems to yield good results, but i can't tell if there are any doubles generated, or if i am missing some hands.
- I would be grateful if anyone could try this stat out in a report towards their db and see if the results seem reasonable.

Thanks for all the help so far!
Last edited by renskav on Wed Feb 18, 2009 6:05 pm, edited 3 times in total.
renskav
 
Posts: 11
Joined: Fri Feb 13, 2009 12:15 pm

Re: Number of times player stacked with hand 1P-

Postby kraada » Wed Feb 18, 2009 6:01 pm

renskav wrote:A few more mods still required:
* including underpairs (this will be a bit tough, I probably have to look up the board cards from holdem_hand_summary, figure out which one is biggest and compare that with the PP-rank, as i dont want to consider pushing overpairs as overplaying). I will take a look at this tomorrow I hope.
* investigating where a good boundary for PTBB is.
* creating the front end for the read-based HUD-stat.


This post will help for dealing with underpairs. I'm not sure what a good boundary for size is, but personally I'd consider even putting it as low as 20PTBBs. That way you get the people who buyin for the default sizes and go broke with 1P- hands. That might be a bit much, though, so I'm not sure. Obviously we don't want to get too low, but just something to consider.

The front end should be fairly simple, at least, once you've decided on the rest of the stuff :)


renskav wrote:And a few concerns:
- I have some concern the stat might be including overplays where you are in the hand, but not the one with the weak hand - using the 'flg_f_tptk_type_hand_or_less' stat above to generate a HCH-report yields some wierd results with hands being included where i am the one with the strong hand - any ideas if this is the stat or the report that is the culprit? Also my db hangs when i try to use 'flg_overplayed_tptk' to generate a HCH-report, which is a tad discomforting, the cnt-based stat seems to yield good results, but i can't tell if there are any doubles generated, or if i am missing some hands.
- I would be grateful if anyone could try this stat out in a report towards their db and see if the results seem reasonable.

Thanks for all the help so far!


You might want to also add something like holdem_hand_player_statistics.flg_showdown as a general constraint -- meaning that the player having the hand in question got to showdown. Since for opponents we're not seeing their cards without showdown it'll work fine for them. For you this would preclude times you tilt off a bunch of money then fold . . . but that doesn't happen often, right? ;)
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Number of times player stacked with hand 1P-

Postby renskav » Sat Feb 21, 2009 1:09 pm

I have been working a bit on the TPTK+underpairs bit, but I am stuck once again. The holdem_hand_player_combinations.id_x_hand_strength doesn't seem to work exactly like you've described in your other post, strength =4 or 5 seems to incude overpairs aswell as some wierd 2-paired with board as far as i can tell.

Here's what i've got for the river TPTK+underpair flag (turn and flop are basically the same).

Code: Select all
(             %this part is to tag TPTK-
((holdem_hand_player_combinations.flg_r_highcard or holdem_hand_player_combinations.flg_r_1pair) and (holdem_hand_player_combinations.val_r_hole_cards_used = 0 or holdem_hand_player_combinations.val_r_hole_cards_used = 1))
or
(         %this part is to tag underpairs
holdem_hand_player_combinations.flg_r_1pair and holdem_hand_player_combinations.val_r_hole_cards_used = 2 and
(holdem_hand_player_combinations.id_r_hand_strength = 4 or holdem_hand_player_combinations.id_r_hand_strength = 5)
)
or
(         %this part is to tag underpairs (which make 2pair with a paired board)
holdem_hand_player_combinations.flg_r_2pair and holdem_hand_player_combinations.val_r_hole_cards_used = 2 and
(holdem_hand_player_combinations.id_r_hand_strength = 4 or holdem_hand_player_combinations.id_r_hand_strength = 5)
)


I have experimented a bit with the sweet spot for the PTBB level, and around 25 seems to be a good tradeoff (atleast for FR micro-stakes) between false positives and missing plays, for higher levels probably 20PTBB is better.

On another note i am now pretty certain that the stat is flawed in another way too, basically it counts the number of pots where either of the players involved held something weaker than TPTK. The stat is still pretty useful in marking out people who tend to go overboard with TPTK, but it doesn't seem to be very exact.

I have also made the front end, which as you said was a peice of cake.

The front end is attatched to this post, it requires the column cnt_overplayed_tptk in HCPS as described above.

Another pretty useful measure (i actually put it in my regular hud) is cnt_overplayed_tptk / cnt_hands * 1000 , which really marks out the people who overplay alot.

The read based stat format string:
Code: Select all
if(cnt_overplayed_tptk > 0, format('Overplayed TPTK or less {1} times.', format_number(cnt_overplayed_tptk, 0, false, false)), '')

TPTK Overplayed/hands stat:
Code: Select all
cnt_overplayed_tptk / cnt_hands * 1000
Last edited by renskav on Sat Feb 21, 2009 1:26 pm, edited 1 time in total.
renskav
 
Posts: 11
Joined: Fri Feb 13, 2009 12:15 pm

Next

Return to Custom Stats, Reports, and SQL [Read Only]

Who is online

Users browsing this forum: No registered users and 0 guests