This thread is about creating a custom 3Bet Preflop vs Hero stat, not about the usefulness of such stats.

/

It took a long time but I finally managed to create a vs Hero stat that performs almost as good (or as bad) as any other custom stat.

The trick is to define a vs Hero stat as a subset of the 'normal' stat. I'll use the Preflop 3Bet stat as an example.

The column cnt_p_3bet, which counts the number of times a player 3bets preflop, is defined like this.

- Code: Select all
`sum(if[cash_hand_player_statistics.flg_p_3bet, 1, 0])`

Now we have to find the subset of this stat. The only way an opponent can make a 3bet against Hero, is if Hero made a 2bet. This is stored in de database field cash_hand_player_statistics.flg_p_first_raise (or tourney_hand_player_statistics). Now, one of the neat things of PT4 is that every hand is saved multiple times in cash_hand_player_statistics. For instance, if six players are dealt in a hand, there will be six different records for that hand in cash_hand_player_statistics. And only one of these hands will have flg_hero set to true.

The original column combined with these two restrictions results in this simple and elegant cnt_p_3bet_vs_hero column.

- Code: Select all
`sum(if[cash_hand_player_statistics.flg_p_3bet and exists`

(select 1

from cash_hand_player_statistics hero

where hero.id_hand = cash_hand_player_statistics.id_hand

and hero.flg_hero

and hero.flg_p_first_raise), 1, 0])

And the opportunity column cnt_p_3bet_opp_vs_hero.

- Code: Select all
`sum(if[cash_hand_player_statistics.flg_p_3bet_opp and exists`

(select 1

from cash_hand_player_statistics hero

where hero.id_hand = cash_hand_player_statistics.id_hand

and hero.flg_hero

and hero.flg_p_first_raise), 1, 0])

Because id_hand is an indexed field, retrieving the records of the hand is fast and the select only has to check for a maximum of the number of players dealt in the hand.

In my small database (350k hands), the stat is fast enough to use in a table group. I'm curious how it will perform in larger databases. And of course, I would like to hear if I'm right and this stat will give the correct numbers