stats refined to when I'm the villain

[jludman]

Wow, that's complicated. Makes some sense, though.

I did a simple filter for chance to steal, and I also added that the position is exactly 0 off the button. Just running that without the complex filter shows the expected result, the exact same as my button steal% as shown in the positions tab. This is 6365 hands for my DB.

I added the two complex filters, and the report shows there were 0 hands, thus the steal % is NA. Pretty sure I've seen plenty of these situations, but to test, I reduced the 75 and 85 both to 0, so that I think the report should show the same 6365 hands, and my button steal%. Now, it's reporting the number of hands I have in total in my DB, 66839, and the "answer" is my overall attempt to steal % for the 3 possible steal positions. It seems, if I put anything more than 0 in either of those filters, I get 0 hands, att to steal % of NA.

Do I need to do something so that the simple filters and these SQL direct ones play nice together, maybe?

[kraada]

Hrm, it appears there's a bug in the query generation code; I'll make sure to report that so that it gets fixed.

In the meantime, remove the simple filters.

Also, the filters I gave you earlier aren't going to work right, and that's my fault.

[Edited out again - this was tricky! :) They've been fixed and the newer version is below]


I'll edit my earlier post so people don't end up using those instead.

If you use these filters, display your player name, filter on active player, and show your Att to Steal, it should give you your Att to Steal in spots where these players were in the blinds - as the only hands counted with these filters are are hands with those players in the blinds.

(Huh, I only have 78% ATS in those spots - that's a leak I'll have to plug! :) )

[jludman]

I have been playing with the numbers for BB fold %, and leaving out the SB fold %. It is accurate when I put it to >= 0%, showing my overall steal %. However, on any number from 1 to 99, the report comes out exactly the same, att to steal 3762/5892 = 63.85%.

[kraada]

Well, clearly that's not right!

I'll do some more analysis to try and figure out what's going wrong and I'll get back to you.

[kraada]

Okay, I'm pretty sure we have it now.

For the BB side of the filter:

holdem_hand_player_statistics.id_hand in (SELECT id_hand FROM holdem_hand_player_statistics, player WHERE player.id_player = holdem_hand_player_statistics.id_player and holdem_hand_player_statistics.flg_blind_b and player.id_player in (SELECT player.id_player from player, holdem_hand_player_statistics where player.id_player = holdem_hand_player_statistics.id_player GROUP BY player.id_player HAVING (((( (CASE WHEN ( (sum((case when(holdem_hand_player_statistics.flg_blind_def_opp AND holdem_hand_player_statistics.flg_blind_b) then 1 else 0 end)))) <> 0 THEN (((sum((case when(holdem_hand_player_statistics.flg_bb_steal_fold) then 1 else 0 end))) * 1.0 )/( (sum((case when(holdem_hand_player_statistics.flg_blind_def_opp AND holdem_hand_player_statistics.flg_blind_b) then 1 else 0 end))))) ELSE 0 END) ) * 100) >=75))))

For the SB side of the filter:

holdem_hand_player_statistics.id_hand in (SELECT id_hand FROM holdem_hand_player_statistics, player WHERE player.id_player = holdem_hand_player_statistics.id_player and holdem_hand_player_statistics.flg_blind_s and player.id_player in (SELECT player.id_player from player, holdem_hand_player_statistics where player.id_player = holdem_hand_player_statistics.id_player GROUP BY player.id_player HAVING (((( (CASE WHEN ( (sum((case when(holdem_hand_player_statistics.flg_blind_def_opp AND holdem_hand_player_statistics.flg_blind_s) then 1 else 0 end)))) <> 0 THEN (((sum((case when(holdem_hand_player_statistics.flg_sb_steal_fold) then 1 else 0 end))) * 1.0 )/( (sum((case when(holdem_hand_player_statistics.flg_blind_def_opp AND holdem_hand_player_statistics.flg_blind_s) then 1 else 0 end))))) ELSE 0 END) ) * 100) >=65))))

Here's what's going on (looking at the SB side): we're looking to filter to times where the hand matches certain characteristics. Which characteristics are these? Well, they're when the hand has a player who is in the small blind, and that player has a fold to steal in the SB of >= 65. Thus we have the id_hand is a list resulting from a query. That query selects the hand from the hhps table. We need to join to the player table to specify the player who has the characteristics we want, and have to join on id_player. We then look only at cases where the player in question is in the small blind (flg_blind_s) and the player's id is in a list. What list? The list of all players who have fold to steal in the SB >= 65.

I was trying previously to combine too many steps, which gave us erroneous results. Note that this report will take a little while to populate (on my 400k played hands database it took about 30 seconds for the query to finish and the data to be populated).

[jludman]

Sweet, that seems to be working, thanks!

Is there a tracking number for the bug you mentioned that doesn't let us add simple filters to refine it to on the button only?

[kraada]

There is an internal one, but it is not something the general public has access to.

However, in cases where you want to do something like this, if you look at a stat that only has a limited set of opportunities, you will get the right answer; what the filter is doing is pulling all hands out of the sample where the BB doesn't have the requisite stat (in this case fold to steal when in the BB). So your own attempt to steal will only be in cases like this.

If you want to see the individual hands in question you'd have to create a different report in the Holdem Cash Hand section - the same filter actually will work, but if you want to see hands where you had a chance to steal you'd want to manually add another filter for holdem_hand_player_statistics.flg_steal_opp - but that should work. Basically you have to recreate the simple filters - thankfully, for many of them, they are fairly simple. If there are any you want the definitions for, just let me know and I'll be happy to provide them.