Expression filters

[kraada]

Ah I think I see what happened.

This looks to give me some data on my test database:

cash_hand_player_statistics.id_hand in (SELECT chps.id_hand from cash_hand_player_statistics chps, cash_cache cc where chps.id_player = cc.id_player and chps.position = 0 and chps.flg_steal_att GROUP BY chps.id_player, chps.id_hand HAVING (( (CASE WHEN sum(cc.cnt_p_open_opp_btn) <> 0 THEN ((((sum(cc.cnt_p_rfi_btn) * 1.0)* 1.0 )/((sum(cc.cnt_p_open_opp_btn) * 1.0)) * 100.0 )) ELSE 0 END) ) >= 60))

And then the same fix for the second one would give you:

cash_hand_player_statistics.id_hand in (SELECT chps.id_hand from cash_hand_player_statistics chps, cash_cache cc where chps.id_player = cc.id_player and chps.position = 8 and chps.flg_blind_def_opp and cc.position_type = 4 GROUP BY chps.id_player, chps.id_hand HAVING (( (CASE WHEN sum(cc.cnt_p_3bet_opp_vs_btn_open) <> 0 THEN ((((sum(cc.cnt_p_3bet_opp_vs_btn_open) * 1.0) - (sum(cc.cnt_p_call_vs_btn_open)* 1.0) - (sum(cc.cnt_p_3bet_vs_btn_open) ))/((sum(cc. cnt_p_3bet_opp_vs_btn_open) * 1.0)) * 100.0 )) ELSE 0 END) ) <= 40))

Both of these now return some data for me.

Edit to add: You can also fiddle with sample size by changing <> 0 to > 10 or some such to require more than 10 data points for a given player. If you were so interested

[summy]

I'll probably give u nightmares but:


The first filter also doesn't return data for me. I've checked it on two machines :/.

[kraada]

It looks like we have a bug here affecting the second filter - looking at the direct query though the subquery returns proper data in PGAdmin what PT4 converts it into is not valid. I'll keep looking at the first one - I too see no data in the final query that PT4 puts through but if you run the subquery manually via PGAdmin you will see hands that match.

[summy]

Oh ok :/.
Shall i make a support ticket regarding the bug?
I guess there is no way around it either? Would have loved that filter ^^.

[kraada]

Please create a ticket, that will make it easiest to figure out why things are not working properly and get it fixed permanently.

[summy]

No reply from the support for 12 days now, is that normal?

Would be nice if they could give some insight whether they want to fix the bug or not :\.

[kraada]

I'll make sure you get a response today.

[coon74]

Try these

1 (as the BTN opener vs <40% BB folders). Apply basic filters: players dealt into hand - between 3 and 10 (in HU hands the BTN position is 9, not 0, and I'm too lazy to incorporate it ), position of active player - exactly 0 (BTN), attempted steal. Then apply a custom filter

Code: Select all

true IN (
SELECT bool_or ( chps.id_hand =
  cash_hand_player_statistics.id_hand )
FROM cash_hand_player_statistics chps
WHERE chps.position = 8 and chps.flg_p_3bet_opp
GROUP BY chps.id_player
HAVING avg ( CASE WHEN
  chps.cnt_p_call + chps.cnt_p_raise > 0
  THEN 1 ELSE 0 END ) > 0.6
)

An explanation to SQL wizards (or those who want to include cases when the SB 3bet or treat BB's (3+)bet/folds as blind defence fails, defying the spirit of the 'Fold vs BTN Open' stat):

Spoiler: show

I select such hand+player records (chps) that the player (opponent) was in the BB ('chps.position = 8') and had an opportunity to 3bet ('and chps.flg_p_3bet_opp'; if you don't mind including hands with your raise and SB's 3bet, erase that). Then it groups these records by id_player and excludes 'tight' players that fail to call or raise with a frequency of at least 60% (I want to include cases when BB 3bet/folds as non-fold cases so I write 'chps.cnt_p_call + chps.cnt_p_raise > 0' - at least one PF raise or call - instead of just 'flg_p_fold'). Finally, the aggregator 'bool_or (chps.id_hand = cash_hand_player_statistics.id_hand)' forms a column of boolean values. Its entry is 'true' if the corresponding 'loose' player was the BB in the hand to which the filter is applied, and 'false' otherwise. If 'true' is present in this column, the hand is filtered in; if all the entries of the column are 'false' (all the 'loose' players weren't the BB in this hand, i.e. the BB was 'tight'), the hand is filtered out.

Note that we can't use a SELECT returning a column of hand ids and check whether our hand id is there because, whenever a GROUP BY clause is used, SQL forbids referring to columns outside of aggregators in the SELECT list without including these columns in the GROUP BY list (because they can have different values inside a group). That's probably why Kraada opted to include chps.id_hand into the GROUP BY list in his attempts, which is senseless because groups then include a single hand each (because both id_hand and id_player have to coincide within any group in his queries) and so stats are aggregated over a single hand. I circumvented the restriction by hiding id_hand into the bool_or aggregator in the SELECT list

2 (as the BB vs >60% BTN openers). Apply basic filters: players dealt into hand between 3 and 10, position of active player is exactly 8 (BB). Then apply a custom filter

Code: Select all

true IN (
SELECT bool_or ( chps.id_hand =
  cash_hand_player_statistics.id_hand )
FROM cash_hand_player_statistics chps
WHERE chps.position = 0 and chps.flg_p_open_opp
GROUP BY chps.id_player
HAVING avg ( CASE WHEN chps.flg_p_first_raise
  THEN 1 ELSE 0 END ) > 0.6
)