Page 1 of 2

new columns and stats for tracking allin pf

PostPosted: Tue Apr 13, 2021 6:12 pm
by ccamp81318
Before I get to the subject would someone please tell me how to get the hand replayer to go through the hands from the beginning of the tournament (or session) to the end instead of from the end back to the beginning?

I have created 4 new columns and two new stats to track the percentage of times a player raises allin preflop when they have the opportunity and the average equity when it is known.
I limit hands to those with 5+ players and to the player having > 10 BB, this is easily changed.

Just checking on bullies.

The test "tourney_hand_player_statistics.id_blinds = tourney_blinds.id_blinds" seems unnecessary. Does PT4 do these joins automatically?

The test "tourney_hand_player_statistics.enum_face_allin SIMILAR TO '(N|p)' " seems redundant, I include it in the hope of speeding up the query. I'm new to PT4 so I could definitely be wrong on this point. it may not speed up the query and its apparent redundancy could be a coincidence.

If any mistakes are noticed please reply, otherwise your welcome to the code. The code has been tested and I'm pretty sure it is correct. It is my first attempt though so please check my work before using.

COLUMNS

cnt_p_allin_raiser_5plusP_10plusBB

sum( if[tourney_hand_player_statistics.id_blinds = tourney_blinds.id_blinds AND
tourney_hand_player_statistics.enum_allin = 'P' AND
tourney_hand_player_statistics.enum_face_allin SIMILAR TO '(N|p)' AND
tourney_hand_player_statistics.cnt_players>4 AND
(tourney_hand_player_statistics.amt_before /
tourney_blinds.amt_BB)>10 AND
tourney_hand_player_statistics.amt_bet_p > 0 AND
tourney_hand_player_statistics.amt_before =
( tourney_hand_player_statistics.amt_blind +
tourney_hand_player_statistics.amt_ante +
tourney_hand_player_statistics.amt_bet_p) AND
tourney_hand_player_statistics.amt_bet_p = tourney_hand_player_statistics.amt_p_raise_made, 1, 0] )

opp_p_allin_raiser_5plusP_10plusBB

sum( if[tourney_hand_player_statistics.id_blinds = tourney_blinds.id_blinds AND
tourney_hand_player_statistics.enum_face_allin <> 'P' AND
tourney_hand_player_statistics.cnt_players > 4 AND
( tourney_hand_player_statistics.amt_before /
tourney_blinds.amt_BB) > 10, 1, 0] )

val_p_allin_raiser_5plusP_10plusBB

sum( if[tourney_hand_player_statistics.id_blinds = tourney_blinds.id_blinds AND
tourney_hand_player_statistics.enum_allin = 'P' AND
tourney_hand_player_statistics.enum_face_allin SIMILAR TO '(N|p)' AND
tourney_hand_player_statistics.cnt_players>4 AND
(tourney_hand_player_statistics.amt_before /
tourney_blinds.amt_BB)>10 AND
tourney_hand_player_statistics.amt_bet_p > 0 AND
tourney_hand_player_statistics.amt_before =
( tourney_hand_player_statistics.amt_blind +
tourney_hand_player_statistics.amt_ante +
tourney_hand_player_statistics.amt_bet_p) AND
tourney_hand_player_statistics.amt_bet_p = tourney_hand_player_statistics.amt_p_raise_made,
tourney_hand_player_statistics.val_equity, 0] )

cnt_val_p_allin_raiser_5plusP_10plusBB

sum( if[tourney_hand_player_statistics.id_blinds = tourney_blinds.id_blinds AND
tourney_hand_player_statistics.enum_allin = 'P' AND
tourney_hand_player_statistics.enum_face_allin SIMILAR TO '(N|p)' AND
tourney_hand_player_statistics.cnt_players>4 AND
(tourney_hand_player_statistics.amt_before /
tourney_blinds.amt_BB)>10 AND
tourney_hand_player_statistics.amt_bet_p > 0 AND
tourney_hand_player_statistics.amt_before =
( tourney_hand_player_statistics.amt_blind +
tourney_hand_player_statistics.amt_ante +
tourney_hand_player_statistics.amt_bet_p) AND
tourney_hand_player_statistics.amt_bet_p = tourney_hand_player_statistics.amt_p_raise_made AND
tourney_hand_player_statistics.val_equity > 0, 1, 0] )

STATS

ALL-in Preflop 5+P 10+BB

(cnt_p_allin_raiser_5plusp_10plusBB / opp_p_allin_raiser_5plusp_10plusBB) * 100

Avg Equity All-in Preflop 5+P 10+BB

(val_p_allin_raiser_5plusp_10plusBB / cnt_val_p_allin_raiser_5plusp_10plusBB) * 100

Re: new columns and stats for tracking allin pf

PostPosted: Thu Apr 15, 2021 7:18 am
by Flag_Hippo
ccamp81318 wrote:Before I get to the subject would someone please tell me how to get the hand replayer to go through the hands from the beginning of the tournament (or session) to the end instead of from the end back to the beginning?

Reverse the sorting of the hands in the report so the first hand is at the top before loading them into the replayer. You can do that by clicking on the column headers so in this example you would want to do it by 'Hand #' or 'Date'.
ccamp81318 wrote:The test "tourney_hand_player_statistics.id_blinds = tourney_blinds.id_blinds" seems unnecessary. Does PT4 do these joins automatically?

It's required for these columns.
ccamp81318 wrote:The test "tourney_hand_player_statistics.enum_face_allin SIMILAR TO '(N|p)' " seems redundant, I include it in the hope of speeding up the query. I'm new to PT4 so I could definitely be wrong on this point. it may not speed up the query and its apparent redundancy could be a coincidence.

This isn't needed.
ccamp81318 wrote:tourney_hand_player_statistics.amt_before =
( tourney_hand_player_statistics.amt_blind +
tourney_hand_player_statistics.amt_ante +
tourney_hand_player_statistics.amt_bet_p) AND
tourney_hand_player_statistics.amt_bet_p = tourney_hand_player_statistics.amt_p_raise_made

amt_bet_p includes amt_blind so this wouldn't return any data for players who posted a blind and amt_p_raise_made is the size of the first raise made by a player so if they 2Bet and then 4Bet this wouldn't be true (amt_p_raise_made_2 is the size of the second raise made by a player). There may also be occasions when a player with a larger stack makes a raise that can effectively put another player still involved all-in but they do not put their entire stack in when making that raise - presumably you would want to cover these situations as well? I'd suggest:

cnt_p_allin_raiser_5plusP_10plusBB
Code: Select all
sum(if[tourney_hand_player_statistics.id_blinds = tourney_blinds.id_blinds AND (tourney_hand_player_statistics.amt_before /
tourney_blinds.amt_bb) > 10 AND tourney_hand_player_statistics.cnt_players > 4 AND tourney_hand_player_statistics.amt_bet_p >= tourney_hand_player_statistics.amt_p_effective_stack AND lookup_actions_p.action LIKE '%R', 1, 0])

Since I removed tourney_hand_player_statistics.amt_bet_p = tourney_hand_player_statistics.amt_p_raise_made I added a test to ensure the players last preflop action was a raise.

opp_p_allin_raiser_5plusP_10plusBB
Code: Select all
sum(if[tourney_hand_player_statistics.id_blinds = tourney_blinds.id_blinds AND (tourney_hand_player_statistics.amt_before /
tourney_blinds.amt_bb) > 10 AND tourney_hand_player_statistics.cnt_players > 4, 1, 0])

Re: new columns and stats for tracking allin pf

PostPosted: Thu Apr 15, 2021 10:53 pm
by ccamp81318
Thanks for the help!

There may also be occasions when a player with a larger stack makes a raise that can effectively put another player still involved all-in but they do not put their entire stack in when making that raise - presumably you would want to cover these situations as well? I'd suggest:

Not really. I would only be interested in a non-allin bet if it covered all players remaining in the pot. Then the bet would still remove all implied odds.
Something like:

writing in sql format to use aliases for brevity.

select ...
from tourney_hand_player_statistics t1
where (t1.enum_allin <> 'P' and
t1.amt_bet_p => (select max(amt_before)
from tourney_hand_player_statistics t2
where t1.id_hand = t2.id_hand
and t1.id_player <> t2.id_player))
OR
above code
all wrapped with the sum and if sysntax.

I would need to test whether the amt_ante needed to be added to one side or the other.
I also don't know how to restrict the subselect to just the players still active in the hand. The above could miss desired rows if the players with more chips were already out of the hand.

Can I even do stuff like SUBSELECTs or UNIONs via PT4?

I'm not certain that the added accuracy would be worth the computation time. The subselect doesn't return results in my little DB of 5989 hands. Maybe it would with some players already out of action.
If there is a way to make the necessary tests to check only the remaining players I'd be willing to try it and see if it is fast enough to be used as a HUD stat.

Re: new columns and stats for tracking allin pf

PostPosted: Fri Apr 16, 2021 5:29 am
by Flag_Hippo
ccamp81318 wrote:Not really. I would only be interested in a non-allin bet if it covered all players remaining in the pot.

Maybe I am misunderstanding what you want but that appears to still be exactly what I suggested:

tourney_hand_player_statistics.amt_bet_p >= tourney_hand_player_statistics.amt_p_effective_stack AND lookup_actions_p.action LIKE '%R'

This means that the players last action was a raise and the total amount of chips invested was equal to or greater than their effective stack. When a player makes a raise that is larger then their effective stack (even if it is less than their actual stack) then by definition it covers the remaining players. The effective stack for each player is determined at their first action in the hand and it's the amount that player could potentially lose in a pot.

Re: new columns and stats for tracking allin pf

PostPosted: Fri Apr 16, 2021 5:51 pm
by ccamp81318
Thanks for the reply. Either I communicated poorly, a frequent occurrence, or one of us, probably me, is wrong.

The '%R' addition does solve another problem that I hadn't worked my way through. How to tell if the enum_face_allin = 'P' happened before or after player's enum_allin = 'P'. I hadn't worried about that until your explanation of enum_face_allin values in another post. Thanks muchly for that part of your help. Even though I noticed that all of player's actions were '%R' I might have never thought to use it to exclude this condition.

My guess was that PT4 is using the smallest stack as the effective stack size. If that is the case and player raises amt_p_effective_stack <= raise < amt_before the smallest stack may be the only one that would be covered and then only if it was still in the pot.

I have never read the definition of effective stack size that you say is used in PT4. The only definition that I have ran across for effective stack size is the lower of the two stack sizes in a HU pot. You can only know the effective stack size in a multiway pot for the player with the smallest stack. You could say its still the smallest stack remaining in the hand and that would be a workable starting point.

I have never read anyone that suggests it be defined as the most money a player could lose in a pot. It is generally given in words as the least money that is available for a player to win. Those statements are equivalent only in two player pots or when player's stack is the smallest in a multiway pot.

I think the calculation for the way you say PT4 does it would be min(max(stack excluding player's stack), the player's stack). Do you agree? I think the standard definition would use min(stack) if it used anything at all.

Using your definition, whether the player's stack or a smaller stack's value was used for the effective stack size at the beginning of the street the mid street value should either be the same or smaller. It seems to me that given my understanding of your definition of effective stack your code would miss hands where player's raise was less than PT4's effective stack size calculated at the beginning of the street but greater than everyone's stack, other than player's, still in the pot when the raise was made. This would occur when all of the hands with bigger stacks folded and player's stack is no longer the lower limit but instead is now the largest stack and player's raise covered the new mid street effective stack. It would also miss hands where player's stack was the biggest and player raised to (third stack <= raise < second stack) after second stack had folded. Do you agree?

My reasons for looking for pf allin raisers to test for bullies is for three reasons:
1) They push people off pots by over betting
2: They remove other player's possible (probable HAHAHA) skill advantage
3) They remove implied odds changing which hands are playable

I like the idea of catching the not allin raises that accomplish all of these things, especially as someone that knows allin bets are being checked for could bet (allin -1) and accomplish the same thing without being caught as raising allin.

Flag_Hippo, thanks in advance for help already given and hopefully yet to come! Sorry if anything I said now or say in the future comes off as rude. I am terrible at seeing that in my conversations. Luckily I've never been so bad as to get sucker punched.

Re: new columns and stats for tracking allin pf

PostPosted: Sat Apr 17, 2021 6:13 am
by Flag_Hippo
ccamp81318 wrote:My guess was that PT4 is using the smallest stack as the effective stack size.

Effective stack in PokerTracker 4 is the maximum amount a player could lose in a pot as of their first action in a hand and this definition allows it to scale for multiway pots. For example:

CO 5BB
BTN 100BB
SB 100BB
BB 100BB

If the smallest stack size was used for effective stacks in multiway hands then every player here would have an effective stack of 5BB and that wouldn't work well.
ccamp81318 wrote:I have never read the definition of effective stack size that you say is used in PT4.

Using a definition like this is typical for multiway hands (Link 1 Link 2)
ccamp81318 wrote:It seems to me that given my understanding of your definition of effective stack your code would miss hands where player's raise was less than PT4's effective stack size

It wouldn't:

Code: Select all
tourney_hand_player_statistics.amt_bet_p >= tourney_hand_player_statistics.amt_p_effective_stack AND lookup_actions_p.action LIKE '%R'

The expression specifies 'greater than or equal to' so it's not possible for that raise to be less than the effective stack set for that player. For example:

CO 200BB
BTN 100BB
SB 50BB
BB 10BB

CO (first to act) has an effective stack of 100BB.

BTN has an effective stack of 100BB if CO VPIPs. If CO folds then BTN has an effective stack of 50BB.

SB has an effective stack of 50BB if CO and/or BTN VPIP. If CO & BTN fold then SB effective stack is 10BB.

Re: new columns and stats for tracking allin pf

PostPosted: Sun Apr 18, 2021 3:41 pm
by ccamp81318
Thank You Flag_Hippo for continuing my education!

I read the pages supplied by the links. I'm sorry but I think they say exactly what I said. Until you know which if any of the players remaining in the hand you will be facing you have to consider that it might be the smallest stack and play accordingly. As to your statement about everyone in the example having an effective stack size of 5BB and that not working well, in my opinion while it might not work well for some calculations that someone had in mind it would have the advantage of being correct. Again my opinion and how I interpret what other people say.

While this discussion is interesting I guess it is beating a dead horse. It doesn't matter that I disagree with PT4s definition of effective stack. It is the definition that they use to arrive at amt_p_effective_stack.

There is still a hole in your code unless I still don't understand. Using PT4's definition if the pf betting goes around the table multiple times the effective stack at the time of player's last pf raise might be < amt_p_effective_stack so player could raise < amt_p_effective_stack and still cover the remaining players, unless, of course PT4 recalculates so that amt_p_effective_stack holds the amt prior to player's last chance to act pf. That would be a different philosophy than PT4 uses for enum_face_allin so I guess that is unlikely.

Besides beating a dead horse it may be irrelevant. Rather than tracking pf allin bets or pf cover bets I am considering tracking bets where player's pf raise is > (amt_before * .25). I'm not interested in player's implied odds. I'm only interested in how his bets affect his opponents' implied odds. The .25 is just my first estimate. I guess it may be as high as .55.

Unless you have something to add explaining how I still don't understand, I still have the one unanswered question. Can I do things like SUBSELECTS and UNIONS? I guess that UNIONS are more likely to be available as they don't require aliases. If SUBSELECTS are available I would appreciate a code snippet as an example because I have no clue. An example of a UNION if it requires something special would also be appreciated.

Thanks Again!!
C

Re: new columns and stats for tracking allin pf

PostPosted: Mon Apr 19, 2021 3:51 am
by ccamp81318
More last questions.

lookup_actions_p.action Where does this come from?

Why can't I join tourney_hand_player_statistics.id_action_p = lookup_actions.id_action in a PT4 column definition?

Are there other functions, DB fields or whatever lookup_action_p is and is there a place to get the list?

Are the differences between sql statements and PT4 definitions explained somewhere?

I think I'm starting to get the hang of this.
Flag_Hippo thank you for your help and patience!
C

Re: new columns and stats for tracking allin pf

PostPosted: Mon Apr 19, 2021 5:55 am
by Flag_Hippo
ccamp81318 wrote:There is still a hole in your code unless I still don't understand. Using PT4's definition if the pf betting goes around the table multiple times the effective stack at the time of player's last pf raise might be < amt_p_effective_stack so player could raise < amt_p_effective_stack and still cover the remaining players, unless, of course PT4 recalculates so that amt_p_effective_stack holds the amt prior to player's last chance to act pf.

Yes that is possible. The effective stack for a player is determined as of their first action and doesn't change if the action goes around the table multiple times.
ccamp81318 wrote:lookup_actions_p.action Where does this come from?

It comes from the lookup_actions table which records the players actions in order on each street where:

B = bet
C = call
F = fold
R = raise
X = check
ccamp81318 wrote:Can I do things like SUBSELECTS and UNIONS? I guess that UNIONS are more likely to be available as they don't require aliases. If SUBSELECTS are available I would appreciate a code snippet as an example because I have no clue. An example of a UNION if it requires something special would also be appreciated.

Unfortunately I am not a PostgreSQL expert but there are examples of subqueries if you search this sub-forum using 'chps' or 'thps'. I don't know anything about UNIONS and have never come across it being used in this forum.
ccamp81318 wrote:Why can't I join tourney_hand_player_statistics.id_action_p = lookup_actions.id_action in a PT4 column definition?

Joins are not something I am familiar with but if I learn anything about that I will let you know.
ccamp81318 wrote:Are there other functions, DB fields or whatever lookup_action_p is and is there a place to get the list?

Are the differences between sql statements and PT4 definitions explained somewhere?

The PT4 schema documentation has not been published as it was for PokerTracker 3 however the meaning of database fields haven't changed in any significant way. You can find the files used to create the database in your PokerTracker4 installation folder:

C:\Program Files (x86)\PokerTracker 4\Data\Schemas

and forum member 'Bininu' made their own schema document for PokerTracker 4 available in this thread.

Re: new columns and stats for tracking allin pf

PostPosted: Mon Apr 19, 2021 1:40 pm
by ccamp81318
Flag_hippo, thanks again.

lookup_actions_p.action doesn't come directly from the lookup_actions table. If it did it wouldn't say lookup_actions_p as the object name. lookup_actions has two columns (id_action, action) and to access the correct action from the database requires a join between %_hand_player_statistics (hum, I wonder how it knows which on to look at, cash or tourney) and lookup_actions which generates an error when used as part of a PT4 column definition while lookup_actions_p.action works fine in a column definition and generates an error in psql.

I had found a forum post from years ago that lookup_action_% objects were created to do the above join and I understood the object name to be 'DB FIELD' but I could easily be wrong on that point as what I found was almost an afterthought in a larger post. What I was asking, in my never to speak clearly way, was if there were other constructs like that. I am also confused why some joins work in PT4 column definitions and some don't.

Thanks for pointing out the old PT3 schema the one by bininu and the one used to create PT4. I have had them loaded on my desktop since I started trying to look under the covers. Between the three of the I can frequently determine the what if not the why.

You have been very helpful, don't let my frustration at communicating my thoughts make you think otherwise.
You are a great resource and lowered my learning curve significantly.

C

highfalutin