stat having non hero player stack size

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

Moderators: WhiteRider, kraada, Flag_Hippo, morny, Moderators

stat having non hero player stack size

Postby PetarM » Sat Feb 19, 2022 11:23 am

Hello,

I am trying to build a stat showing the open shove percentage of SB against BB, taking into account the sizes of the stacks and without using expression filters.
However, I do not know how to define the stack size of non hero player, in this example the BB:

sum(if[tourney_hand_player_statistics.amt_p_raise_made >= tourney_hand_player_statistics.amt_p_effective_stack and
tourney_hand_player_statistics.flg_p_first_raise and
tourney_hand_player_statistics.flg_p_open_opp and
(tourney_hand_player_statistics.amt_before / tourney_blinds.amt_bb) between 8 and 12 and
tourney_hand_player_statistics.position = 9, 1, 0])

Could you please assist
Thanks
PetarM
 
Posts: 58
Joined: Wed Oct 18, 2017 4:25 pm

Re: stat having non hero player stack size

Postby Flag_Hippo » Sat Feb 19, 2022 1:38 pm

If you are building a statistic for one player but want to query the stack size of another player then you will need to use a subquery 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 = 8 and (thps.amt_before / tb.amt_bb ) between 8 and 12)

This will test for hands where the BB had a stack between 8bb and 12bb and you can edit that accordingly for other positions/stack sizes and for more on how SELECT works see here.
Flag_Hippo
Moderator
 
Posts: 17136
Joined: Tue Jan 31, 2012 7:50 am

Re: stat having non hero player stack size

Postby PetarM » Sat Feb 19, 2022 4:55 pm

Hello,
The following statement integrating your suggestion is not accepted as a valid SQL:

sum(if[tourney_hand_player_statistics.amt_p_raise_made >= tourney_hand_player_statistics.amt_p_effective_stack and
tourney_hand_player_statistics.flg_p_first_raise and
tourney_hand_player_statistics.flg_p_open_opp and
(tourney_hand_player_statistics.amt_before / tourney_blinds.amt_bb) between 8 and 12 and
tourney_hand_player_statistics.position = 9 and
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)), 1, 0])

can you have a look please
PetarM
 
Posts: 58
Joined: Wed Oct 18, 2017 4:25 pm

Re: stat having non hero player stack size

Postby Flag_Hippo » Sun Feb 20, 2022 4:55 am

If you find that doesn't work try CASE WHEN instead:

Code: Select all
sum(CASE WHEN tourney_hand_player_statistics.amt_p_raise_made >= tourney_hand_player_statistics.amt_p_effective_stack and tourney_hand_player_statistics.flg_p_first_raise and tourney_hand_player_statistics.flg_p_open_opp and (tourney_hand_player_statistics.amt_before / tourney_blinds.amt_bb) between 8 and 12 and tourney_hand_player_statistics.position = 9 and 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) THEN 1 ELSE 0 END)
Flag_Hippo
Moderator
 
Posts: 17136
Joined: Tue Jan 31, 2012 7:50 am

Re: stat having non hero player stack size

Postby PetarM » Tue Feb 22, 2022 5:44 am

It works like this, thanks
However, PT says that the column "the column can not be cached", what does that mean
PetarM
 
Posts: 58
Joined: Wed Oct 18, 2017 4:25 pm

Re: stat having non hero player stack size

Postby PetarM » Tue Feb 22, 2022 6:45 am

Another issue is that apparently the simple filters do not impact the result, whatever simple filter I put, the result is the same
PetarM
 
Posts: 58
Joined: Wed Oct 18, 2017 4:25 pm

Re: stat having non hero player stack size

Postby Flag_Hippo » Tue Feb 22, 2022 12:11 pm

PetarM wrote:It works like this, thanks
However, PT says that the column "the column can not be cached", what does that mean

Custom columns that use a subquery cannot use the database cache so that data must be calculated independently each time the HUD updates - see this guide.
PetarM wrote:Another issue is that apparently the simple filters do not impact the result, whatever simple filter I put, the result is the same

If you are using both built-in filters and expression filters and aren't getting correct results put everything you need in the expression filter so you can ensure everything is grouped correctly otherwise please give specific details about exactly what you are doing here.
Flag_Hippo
Moderator
 
Posts: 17136
Joined: Tue Jan 31, 2012 7:50 am


Return to Custom Stats, Reports and HUD Profiles

Who is online

Users browsing this forum: No registered users and 0 guests