Advanced Player Report SQL filter

[denutza]

I have a huge database and want to start to get more advanced with it, creating player reports that will show my bb/100 against specific villain types, with specific positions indicated and specific hole card ranges:

Lets start with an Omaha high lo 3-6 handed example where Hero in on button, with A2 AND 4 cards lower than 6 AND no pair, facing a loose aggressive big blind, that 3-bets a steal between 20 and 40 percent of the time.

So we need to filter for hero position: (Button), 3-6 players dealt in, when hero had specific hole card ranges
Filter for opponents that were
a) in the big blind, in a 3-6 handed game
b) match the 3 bet vs steal freq from their big blind at a 20-40% frequency

Return the aggregate of bb/100 and total hands played in a Players Report for HERO

Here is the code for the holecards:

Code: Select all

exists (
  select 1
  from cash_hand_player_statistics as hero
  join lookup_hole_cards as lhc on lhc.id_holecard = hero.id_holecard
  where hero.id_hand = cash_hand_player_statistics.id_hand
  and hero.flg_hero and lhc.hole_cards ~ '([A234]|[A235]|[A236]|[A245]|[A246]|[A256])'
 )


Here is some potential code for BB 3 bet frequency:

Code: Select all

cash_hand_player_statistics.id_hand in (select chps.id_hand from cash_hand_player_statistics chps where
chps.id_player in (select cc.id_player from cash_custom_cache cc join cash_limit cl on cl.id_limit=cc.id_limit
where cc.cnt_players between 3 and 6 and cc.id_gametype=3  and cl.flg_lo=TRUE
group by cc.id_player having sum(cc.cnt_hands_cached)>10
and (sum(cc.cnt_steal_bb_def_action_3bet) / (sum(cc.cnt_steal_bb_def_opp))between .20 and.40)))


Every attempt ive made at isolating opponents that fit this frequency ends up returning ANY opponent that fits the 20-40% 3 bet frequency that was dealt in, regardless if they're actually in the big blind or not.
So specifying exact opponent and hero positions is giving me difficulties (Adding simple expressions is not a solution as it will run forever).

[BillGatesIII]

Becasue I don't have the custom stats you are referring to in your example, here is a query filtering opponents with a '3Bet vs BTN Open' between 20% and 40%, and only counting hands with hero on the button and villain in the big blind.

Code: Select all

exists (
  select 1
  from cash_hand_player_statistics as hero
  where hero.id_hand = cash_hand_player_statistics.id_hand
  and hero.flg_hero
  and hero.position = 0)
and cash_hand_player_statistics.position = 8
and cash_hand_player_statistics.id_player in (
  select id_player
  from (
    select
      cc.id_player,
      sum(cc.cnt_hands) as sum_hands,
      sum(cc.cnt_p_3bet_vs_btn_open) as sum_3bvsbt,
      sum(cc.cnt_p_3bet_opp_vs_btn_open) as sum_3bvsbt_opp
    from cash_cache cc
    where cc.id_gametype = 2
    and cc.cnt_players between 3 and 6
    group by cc.id_player) as opp
  where opp.sum_hands > 10
  and opp.sum_3bvsbt_opp > 0
  and opp.sum_3bvsbt::numeric / opp.sum_3bvsbt_opp between .2 and .4)


It is kind of hard to check if it really does what I think it should do so let me know if it's any good or wrong

[denutza]

It is kind of hard to check if it really does what I think it should do so let me know if it's any good or wrong

If i dont strip out the part where you try to define opps position as 8, it returns nothing.

To test, you can run this:

Code: Select all

exists (
  select 1
  from cash_hand_player_statistics as hero
  where hero.id_hand = cash_hand_player_statistics.id_hand
  and hero.flg_hero
  and hero.position = 0)
and cash_hand_player_statistics.position <>0

and it thinks cash_hand_player_statistics.position <>0 applies to hero, and will return empty.

[denutza]

My bad, I had Filter for Active Player checked.

I guess this would be looking at bb/100 from opponents viewpoint then, and ill just have to reverse the logic.
Is there any way to make it work with Filter for Active Player on.

I still have yet to test more in depth (hero hole cards, etc), but so far it appears to be working.