effective stack size for a particular villain

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

Moderators: WhiteRider, kraada, Flag_Hippo, morny, Moderators

effective stack size for a particular villain

Postby oldgold » Sun Jul 24, 2016 4:55 pm

I'm trying to create a stat that calculates (among other things) the effective stack size in big blinds of a villain at a particular position. We'll use the button as an example. This would be for the general case where the "player" and the villain on the button could have different effective stacks because of the presence of at least one other player that has a larger stack than both.

I've tried things along the lines of the following column expression which is meant to count hands where a villain on the button has at least 10 bb, although I haven't been able to produce valid SQL.
Code: Select all
sum(if[(select amt_p_effective_stack from cash_hand_player_statistics as button
where (cash_hand_player_statistics.id_hand = button.id_hand
and cash_hand_player_statistics.id_player <> button.id_player
and button.cnt_players_lookup_position = 0)
/ cash_limit.amt_bb) >= 10, 1, 0])

It seems that the table I've selected and named "button" needs to include all columns rather than just amt_p_effective_stack so that the subsequent comparisons can be made, but I'm not sure how to do that (can you use "*" to indicate all columns?) while also grabbing the effective stack amount itself. Thanks for any direction you can provide.
oldgold
 
Posts: 10
Joined: Sun Aug 23, 2015 10:27 am

Re: effective stack size for a particular villain

Postby kraada » Mon Jul 25, 2016 9:08 am

You need to specify which table in your query as it's going to end up nested as a subquery and when you use "select amt_p_effective_stack from" it's unclear to the parser which you want -- "select button.amt_p_effective_stack from" to start things off. Also instead of "button.cnt_players_lookup_position = 0" you should use "button.position = 0". It should work properly for you once you've made those two changes.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: effective stack size for a particular villain

Postby oldgold » Tue Jul 26, 2016 11:08 am

Thanks, that did work. Also I needed to add another set of parenthesis because the way I had it was just dividing the "where" part by the amt_bb instead of the whole "select...where" result.

I noticed, however, that it seems to work with "button.cnt_players_lookup_position = 0" as well as "button.position = 0". I'm not questioning your direction, just asking for understanding... Is there a reason why "button.position = 0" is better? I didn't even know "position" on its own was a column in one of the tables.
oldgold
 
Posts: 10
Joined: Sun Aug 23, 2015 10:27 am

Re: effective stack size for a particular villain

Postby kraada » Tue Jul 26, 2016 1:13 pm

position is a column in the _hand_player_statistics table and stores the player's position in the hand. It's identical to cnt_players_lookup_position unless there is a hand without the small blind - in that case cnt_players_lookup_position adds one to the position value.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: effective stack size for a particular villain

Postby oldgold » Tue Jul 26, 2016 1:17 pm

I see. Thanks very much!
oldgold
 
Posts: 10
Joined: Sun Aug 23, 2015 10:27 am

Re: effective stack size for a particular villain

Postby b4rr4o » Sat Sep 24, 2016 7:01 pm

kraada wrote:position is a column in the _hand_player_statistics table and stores the player's position in the hand. It's identical to cnt_players_lookup_position unless there is a hand without the small blind - in that case cnt_players_lookup_position adds one to the position value.


Hello! I posted this question some days ago but nobody anwsered me, i have seen this post and i want a similar stat...

4bet vs a Short stack, not taking into account the player doing the 4bet but the 3bettor. I can´t figure it out :( Will you please help me??

This is what i tried with no success

sum(if[cash_hand_player_statistics.flg_p_first_raise and cash_hand_player_statistics.flg_p_4bet AND
select 3bet.amt_p_effective_stack from cash_hand_player_statistics as 3bet
where (cash_hand_player_statistics.id_hand = 3bet.id_hand
and cash_hand_player_statistics.id_player <> 3bet.id_player
and 3bet.flg_p_3bet)
/ cash_limit.amt_bb) <= 25
, 1, 0])

Thank you!
b4rr4o
 
Posts: 20
Joined: Mon Feb 11, 2008 11:25 am

Re: effective stack size for a particular villain

Postby WhiteRider » Sun Sep 25, 2016 3:44 am

The effective stack for each player is determined at their first action in the hand so we don't have a value based on only players remaining in the hand at the time of the 4bet.
WhiteRider
Moderator
 
Posts: 53961
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: effective stack size for a particular villain

Postby b4rr4o » Sun Sep 25, 2016 8:28 am

WhiteRider wrote:The effective stack for each player is determined at their first action in the hand so we don't have a value based on only players remaining in the hand at the time of the 4bet.


no no, i don't want to know the effective stack after the action. I want a simple check for the stack of the player doing the 3bet at the start of the hand.

If i use a stat like this one, it will only check for the stack of the player doing the 4bet and not the real effective stack, i have tested it

cash_hand_player_statistics.flg_p_first_raise and
cash_hand_player_statistics.flg_p_4bet and
cash_hand_player_statistics.amt_p_effective_stack/cash_limit.amt_bb <= 25
b4rr4o
 
Posts: 20
Joined: Mon Feb 11, 2008 11:25 am

Re: effective stack size for a particular villain

Postby WhiteRider » Sun Sep 25, 2016 12:16 pm

Take a look at this example - I think that should give you the right direction. You'd need to replace "chps.flg_p_first_raise" with "chps.flg_p_3bet", and change the stack size test.
WhiteRider
Moderator
 
Posts: 53961
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK


Return to Custom Stats, Reports and HUD Profiles

Who is online

Users browsing this forum: No registered users and 25 guests

cron