enum_face_allin

[kraada]

Did you add AND stat.amt_p_2bet_facing <stat.amt_before
AND stat.amt_p_effective_stack=stat.amt_before to your second query? I'm confused where the problem is. And nothing came pasted through on your second post.

[ibyoar]

I cant edit my posts, sorry

After importing some hands into a new database i get these results:
SELECT COUNT (DISTINCT stat.id_hand)
FROM public.tourney_hand_player_statistics stat
WHERE stat.enum_face_allin='P'
AND stat.position=0
AND stat.cnt_players>=4
AND stat.amt_p_2bet_facing <stat.amt_before
AND stat.amt_p_effective_stack=stat.amt_before

Result:97

SELECT COUNT (DISTINCT stat.id_hand)
FROM public.tourney_hand_player_statistics stat
WHERE stat.enum_face_allin='p'
AND stat.position=0
AND stat.cnt_players>=4

Result:734

If i add amt_p_3_bet_facing/amt_p_4_bet_facing to cover possible 3bets or bets in the first query:
SELECT COUNT (DISTINCT stat.id_hand)
FROM public.tourney_hand_player_statistics stat
WHERE stat.enum_face_allin='P'
AND stat.position=0
AND stat.cnt_players>=4
AND (stat.amt_p_2bet_facing <stat.amt_before OR stat.amt_p_3bet_facing <stat.amt_before OR stat.amt_p_4bet_facing <stat.amt_before)
AND stat.amt_p_effective_stack=stat.amt_before

Result:919

If i just use amt_p_raise_facing:
SELECT COUNT (DISTINCT stat.id_hand)
FROM public.tourney_hand_player_statistics stat
WHERE stat.enum_face_allin='P'
AND stat.position=0
AND stat.cnt_players>=4
AND (stat.amt_p_raise_facing) <stat.amt_before
AND stat.amt_p_effective_stack=stat.amt_before

Result:64


The difference now is not so huge, but i am bit missed because all the hands i have imported are from sng Superturbo, (3bets are relatively unusual and 4bets could be science fiction) so i have 2 questions:
1.i could understand that the result of the 3rd query could be different from the second query, but it never should be less.Why this difference?
2.given that all data is from SnG superTurbo when the usual move is openpush, can you confirm if i am right in this example:
Example:
Blinds:5/10, player1 raises to 20,player2 raises to 30-->amt_p_2bet_facing=20,amt_p_3bet_facing=30,amt_p_raise_facing=30.is that right?

[kraada]

(1) I don't know that is particularly odd. I'll ask the developer who worked on this if he has any insight.

(2) It varies from opponent to opponent. player1 has amt_p_2bet_facing = 0 as they never faced one. player2 has amt_p_raise_facing = 20 (as that's what the raise was facing him). It's unique to each player's situation.

[ibyoar]

Thanks

(2), i forgot to mention that i was referring to situation of player3
-------------------------Player1-----Player2----Player3
amt_p_2bet_facing------0-----------20----------20
amt_p_3bet_facing------0-----------0-----------30
amt_p_raise_facing-----0-----------20----------30

is it right?

[kraada]

Regarding (1), your third query has a lowercase p - so it's times you faced a shove as a big stack, and the fourth query has a capital P - so it's times you faced a shove as a small stack.

Clearly in your database you're facing shoves from larger stack sizes more than you are from smaller stack sizes.

This also makes sense because the big stacks are going to throw their weight around in these kinds of tournaments and when everyone folds everyone faces one of these kinds of shoves. When really small stacks go in it's more likely that someone calls and they either get bigger or go home so those kinds of shoves are less repetitive.

[ibyoar]

Thanks,
no,third query has uppercase, it is second which has lowercase
Maybe my post is confusing, lets recapitulate(the queries are a bit different just to simplify):

Query 1:SELECT COUNT (DISTINCT stat.id_hand) FROM public.tourney_hand_player_statistics stat WHERE stat.enum_face_allin='p' AND stat.cnt_players>=4
---LowerCase: it is collecting all the hands when i face a shove as big stack, and when i have chips that someone else can win.-->I covers the pusher and i am covered behind.Result:2601

Query 2:SELECT COUNT (DISTINCT stat.id_hand) FROM public.tourney_hand_player_statistics stat WHERE stat.enum_face_allin='P' AND stat.cnt_players>=4 AND stat.amt_p_raise_facing <stat.amt_before
---Uppercase: so it's times i face a shove as a small stack, but this is avoided with the condition:stat.amt_p_raise_facing <stat.amt_before,so the result of this query should be 0. Result:1003

Query 3:SELECT COUNT (DISTINCT stat.id_hand) FROM public.tourney_hand_player_statistics stat WHERE stat.enum_face_allin='p' AND stat.cnt_players>=4 AND stat.amt_p_effective_stack=stat.amt_before
---Same query as 1, but i have added the condition stat.amt_p_effective_stack=stat.amt_before which should return the same result as 1.-->lowercase indicates i covers the pusher and if i am covered behind the effective stack should be my own stack. Result:734

What i am doing wrong?

[kraada]

Try this query:

SELECT COUNT (DISTINCT stat.id_hand) FROM public.tourney_hand_player_statistics stat WHERE stat.enum_face_allin='P' AND stat.cnt_players>=4 AND stat.amt_p_raise_facing < (stat.amt_before - stat.amt_blind - stat.amt_ante)

That should be 0 - amt_before is before any blinds or antes come out. But when someone shoves for 200 and the BB and the BB is 100 amt_p_raise_facing is only 100 for the BB (they only have 100 to call).

[ibyoar]

Thanks again Kraada,
the result of the query you have send me is:340
I think the best way to avoid blinds or antes could be:
SELECT COUNT (DISTINCT stat.id_hand) FROM public.tourney_hand_player_statistics stat WHERE stat.enum_face_allin='P' AND stat.cnt_players>=4 AND stat.amt_p_raise_facing <stat.amt_before AND ((stat.amt_blind+stat.amt_ante)=0)
The result of this query is 288.
or:
SELECT COUNT (DISTINCT stat.id_hand) FROM public.tourney_hand_player_statistics stat WHERE stat.enum_face_allin='P' AND stat.cnt_players>=4 AND stat.amt_p_raise_facing <stat.amt_before AND stat.amt_ante=0 AND stat.position<8
The result of this query is 288.

In none of the cases the result is 0 as i suppose it should be

More:
-When i execute the query:SELECT DISTINCT(stat.id_hand,stat.amt_before,stat.amt_p_raise_facing) FROM public.tourney_hand_player_statistics stat WHERE stat.enum_face_allin='P' AND stat.cnt_players>=4
is showing any case, no matter if stat.amt_before is lower or higher that stat.amt_p_raise_facing
-if i execute the query:SELECT DISTINCT(stat.id_hand,stat.amt_before,stat.amt_p_raise_facing) FROM public.tourney_hand_player_statistics stat WHERE stat.enum_face_allin='p' AND stat.cnt_players>=4
in this case it seems to be correct, in all the cases amt_before is higher than amt_p_raise_facing

[ibyoar]

More:
-When i execute the query:SELECT DISTINCT(stat.id_hand,stat.amt_before,stat.amt_p_raise_facing) FROM public.tourney_hand_player_statistics stat WHERE stat.enum_face_allin='P' AND stat.cnt_players>=4
is showing any case, no matter if stat.amt_before is lower or higher that stat.amt_p_raise_facing
-if i execute the query:SELECT DISTINCT(stat.id_hand,stat.amt_before,stat.amt_p_raise_facing) FROM public.tourney_hand_player_statistics stat WHERE stat.enum_face_allin='p' AND stat.cnt_players>=4
in this case it seems to be correct, in all the cases amt_before is higher than amt_p_raise_facing

A bit more confusing:
query a) shows the row "(2,300.00,210.00)"-->id.hand=2,amt_before=300,amt_p_raise_facing=210 and query b) shows the row "(2,300.00,240.00)"-->id.hand=2,amt_before=300,amt_p_raise_facing=240.Given that id.hand is unique key,how can this be possible?

[kraada]

id_hand is unique per hand but occurs once per player per hand. Only (id_hand, id_player) is truly unique.