Can't find facing version for preflop effective stack size

[PFortune]

Hey there,

As far as I'm aware amt_p_effective_stack applies to the villain or person that the stat applies to

Is there a way to refer to the effective stack size the villain is facing when in a column.

Here is an example of the application to clarify:

I'm wondering if there's basically a "facing" version for effective stack size so that when -facing- an agressive action one can account for what it means relative to the stack size of the person making the aggressive action

Effective stack size isn't universal because often everyone has a different maximum amount they can lose when there are players left to act behind. For example if you had a BB with 50BB a SB with 100BB and a BTN with 10BB. When the button raises it's clear that from his perspective the effective stack is 10. When the SB faces this raise his effective stack is 50 because he can potentially lose 50BB to the BB. In this scenario I want a SB stat that will take into account only the effective stack size from the BTN's perspective. How would I do this?

Thanks!

[Flag_Hippo]

Anything using tourney_hand_player_statistics is specific to that player so if you are writing a statistic for a player but want to test the size of a different players effective stack then that will require a subquery similar to this. For more on how SELECT works see here however these types of queries will disable the use of the database cache.

[PFortune]

I'm trying to build a Fold to Jam stat and Fold to 3Bet Jam stat. Two things which are kind of essential for online MTTs.

I saw on another post from someone (it seems this sort of thing is asked about all the time) you mentioned a "subquery to test the size of the 2bet against the 2bettors effective stack"

Now in order to have any shot of doing this I'd need to actually know what the pt4 database is and what it looks like, then there'd be a chance of me being able to work out what to do

So if this subqueury is too complicated to be provided, where can I find documentation on how th pt4 database is made and what the tables look like. Unfortuntely without this I can't apply the knowledge in the link about select you mentioned and I'm not coding savvy enough to infer the nature of the database from that one example you gave

Also a table of what these definitions are what they mean would be great.

This was your example spread out

tourney_hand_player_statistics.id_hand in

(
select thps.id_hand
from
tourney_hand_player_statistics thps, player p, tourney_blinds tb
where
thps.id_player = p.id_player and thps.id_blinds = tb.id_blinds
and
thps.position = 8
and
(thps.amt_before / tb.amt_bb ) between 8 and 12
)

This still doesn't make any sense to me after reading through the article on select.

Could you help me understand what the following are referring to?
tourney_hand_player_statistics.id_hand
in
The difference between tourney_hand_player_statistics and thps
thps
tb
tourney_blinds tb

All I can get so far is:


tourney_hand_player_statistics.id_hand in

(
select thps.id_hand
from
tourney_hand_player_statistics thps, player p,
where

and

and
(thps.amt_effective_stack / amt_facing_2bet) >= 0.8
)

I have no idea how to refer to the person making the 2bet

[Flag_Hippo]

Now in order to have any shot of doing this I'd need to actually know what the pt4 database is and what it looks like, then there'd be a chance of me being able to work out what to do

So if this subqueury is too complicated to be provided, where can I find documentation on how th pt4 database is made and what the tables look like. Unfortuntely without this I can't apply the knowledge in the link about select you mentioned and I'm not coding savvy enough to infer the nature of the database from that one example you gave

Also a table of what these definitions are what they mean would be great.

We have not published the schema like we did for PokerTracker 3 however the meaning of database fields haven't changed in any significant way. Also forum member 'Bininu' made their own schema document for PokerTracker 4 available in this thread.

Could you help me understand what the following are referring to?
tourney_hand_player_statistics.id_hand
in

I am not an SQL expert but basically when querying something that's not directly stored in the data for the player you are creating the statistic for then that needs a subquery to test from all the other players perspectives to find the data from hands that meet your desired criteria.

The difference between tourney_hand_player_statistics and thps
thps
tb
tourney_blinds tb

thps is an abbreviation of tourney_hand_player_statistics.
tb is an abbreviation of tourney_blinds.

I have no idea how to refer to the person making the 2bet

If a player makes a 2Bet the following is true:

Code: Select all

tourney_hand_player_statistics.flg_p_first_raise

A subquery that tests the effective stack of a player on the button making a 2bet would look like this:

Code: Select all

tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps, player p, tourney_blinds tb where thps.id_player = p.id_player and thps.id_blinds = tb.id_blinds and thps.position = 0 and thps.flg_p_first_raise and (thps.amt_p_effective_stack / tb.amt_bb between x and y))

where 'x' and 'y' are the lower/upper boundaries of the players effective stack in big blinds.

[PFortune]

I want it to test the size of the 2bet relative to the person making the 2bet. Their position could be anywhere so referencing that isn't right for the stat I'm trying to create.

So I have to start with tourney_hand_player_statistics.flg_p_first_raise. Now used on it's own this wouldn't do anything if the person the stat is being applied to doesn't 2Bet. So I have to find a way to first get pt4 to apply this queury to everyone in the hand and then I have to make the person who returns this as true into the playr id



Therefore I have to assume that I have to make it do this check against everyone in the hand.

So starting from the code you gave me spaced out I'm trying to remove the parts that aren't relevant to the stat I want to create since it's possible what I want is simpler than your example (even though it might

tourney_hand_player_statistics.id_hand in
(
select
thps.id_hand
from tourney_hand_player_statistics "thps", player "p", tourney_blinds "tb"
where
thps.id_player = p.id_player
and
thps.id_blinds = tb.id_blinds
and
thps.flg_p_first_raise
and
amt_p_2bet_facing >= thps.amt_p_effective_stack*0.8
)

I can tell this isn't right because why would it be ahaha. However this is as far as I've gotten. Perhaps if you could explain what each line actually does I'd have a shot.

[Flag_Hippo]

I want it to test the size of the 2bet relative to the person making the 2bet. Their position could be anywhere so referencing that isn't right for the stat I'm trying to create.

If you want to make a direct comparison between different players effective stacks then unfortunately that's beyond my own SQL knowledge and I don't know if that is even possible.