Creating a report to filter for play vs specific players

[Darvini]

Was that for me? If it was I dont understand how it relates to my filter request...

[kraada]

That was for a different thread, sorry about that . . . I appear to have mixed up my tabs. You want a Simple Filter for the stuff that hero does (has a 3bet opp, or actually 3bets), and to filter for only hands where a player made an open raise with a fold to 3bet after raise (I presume that's the one you want) between X and Y use this filter:

cash_hand_player_statistics.id_hand in (SELECT chps.id_hand from cash_hand_player_statistics chps where chps.flg_p_first_raise and chps.id_player in (SELECT p.id_player from player p, cash_cache cc where p.id_player = cc.id_player group by p.id_player HAVING ((( (CASE WHEN ( sum(cc.cnt_p_3bet_def_opp_when_open_raised)) <> 0 THEN ((sum(cc.cnt_p_3bet_def_action_fold_when_open_raised) * 1.0 )/( sum(cc.cnt_p_3bet_def_opp_when_open_raised))) ELSE 0 END) ) * 100) BETWEEN X and Y)))

[Darvini]

Yes! That seems to work. It does take quite some time to run the report like you said. Only tried it from a single session hands. Thank you very much!

Now, If i want to change the filter to filter on another stat can I just change the columns (cnt_p_3bet_def_action_fold_when_open_raised cnt_p_3bet_def_opp_when_open_raised) in the filter to corresponding columns of the another stat? And since the report will take a long time is there something I can do optimize the speed?

With this tool I dont even mind so much that I cant use all my PT3 customs stats in my PT4 HUD, cause Im going to build a new HUD with this filter.

[kraada]

Yes, you can change those columns to the times and opportunities columns elsewhere - note that since it uses the cache only built in columns will work here. But since you can see how long this is and uncached stats would take even longer . . . you can see why that would be unappealing.

There is no way to speed this up I'm afraid.

[Darvini]

Do you have an idea how many times longer would it take to run a similar filter for custom stats? The stat on report can still be custom stat right. Just the filter stat has to be non custom?

Surely something would affect the speed. Is it more about cpu or memory? Will report run faster if there arent another programs on.

[Darvini]

Can a minimum opportunities be added to the filter? Could more stats then one be added to the filter? I dont understand the code so I dont know what part does what. If you could help me understand it I could edit the filter further for my needs.

[kraada]

Going for custom stats is going to make everything take much longer because we need to calculate in addition every player's value for that stat.

Here's what's going on - any expression in the Filters Expression area is added as a restriction in the WHERE clause of your SQL query. So your normal SQL might be something like:
select X from T where W;

where X is the data you want to get, T is the table(s) involved and W are the restrictions. In this case, we're putting extra restrictions on the query used to get our final data.

The expression we've got here is:

cash_hand_player_statistics.id_hand in (SELECT chps.id_hand from cash_hand_player_statistics chps where chps.flg_p_first_raise and chps.id_player in (SELECT p.id_player from player p, cash_cache cc where p.id_player = cc.id_player group by p.id_player HAVING ((( (CASE WHEN ( sum(cc.cnt_p_3bet_def_opp_when_open_raised)) <> 0 THEN ((sum(cc.cnt_p_3bet_def_action_fold_when_open_raised) * 1.0 )/( sum(cc.cnt_p_3bet_def_opp_when_open_raised))) ELSE 0 END) ) * 100) BETWEEN X and Y)))

So in this case we're restricting to times where a hand (cash_hand_player_statistics.id_hand) matches a subquery (SELECT ....). So what does our subquery do?

We select hands (chps.id_hand) from a clean copy of cash_hand_player_statistics (which we reference as chps) where the player made the first raise (chps.flg_p_first_raise) and the player (chps.id_player) matches a subquery (SELECT ....) which picks out the players who have the stat value we want (in this case folding to 3bet after raise between X and Y).

This final subquery will be fairly quick because the stats only need to retrieve and divide two numbers (assuming the denominator is nonzero). If you use a custom stat it would need to retrieve the player's action for every hand, check to see if it should be added, if so, add it, then after all times and opportunities have been added up do the division. This would need to be done for all players. So I hope it's clear why cached values are much preferable here.

But for completion's sake I'll break down the last subquery:

(SELECT p.id_player from player p, cash_cache cc where p.id_player = cc.id_player group by p.id_player HAVING ((( (CASE WHEN ( sum(cc.cnt_p_3bet_def_opp_when_open_raised)) <> 0 THEN ((sum(cc.cnt_p_3bet_def_action_fold_when_open_raised) * 1.0 )/( sum(cc.cnt_p_3bet_def_opp_when_open_raised))) ELSE 0 END) ) * 100) BETWEEN X and Y))

This selects player id values from a clean player table and cache table. We join so that id_player refers to the same player in both tables. We group by players since we want to be returning player data and we want players that have a certain property (HAVING is different from WHERE because WHERE checks to see that some data matches before it's added to the result set, HAVING checks data in the result set and only displays rows which match the check). The CASE statement makes sure that the denominator of our fraction is not 0 (we'd get divide by zero errors then), and if it doesn't, then we add up the times and opportunities of the columns for our stat and divide and make sure that value is between x and y.

To change it to use a custom stat you'd basically want to change data in the HAVING clause to the SQL to tabulate and divide by your custom stat - but since that will require joining to (at least) the cash_hand_player_statistics table again which is the largest table in the database containing the most data - reading every row for every player is a whole lot of extra number crunching.

[Darvini]

I think I got some of that explanation. Not enough to understand why CASE WHEN ( sum(cc.cnt_p_3bet_def_opp_when_open_raised)) <> 0 has no relevance when the range is 0 to 100. But when the range is narrower (ie useful) it does make a difference and if I understood correctly I can use it as a minimum opportunity requirement.

I did find two way to make the report faster. First was perhaps too obvious for you to mention, but if I set simple filters to exact scenario, its faster. Second was more of a surprise. The smaller the database I have the faster the report even if I use global filters. So if my DB is only for last two months it will run faster then with a year even though the filter would be only for the last two months.

[kraada]

The CASE WHEN statement is needed because otherwise you will get some players who have a denominator of zero and the division will still error out when testing to see if the result is between your range. It can't tell if the division is in that range until it actually does divide.

A smaller database will always return results faster - there's less data in it to read.

[Darvini]

Im still coming back to this. I could manually go through all of certain situations in the hand replayer and then write down the numbers but it seems crazy. It should be done automatically. The replayer shows the HUD stats as they were at the time of the play. It takes about a second for the HUD to appear. So going through a thousand hands shouldnt take no more then 15 minutes. Even if the stats are not cached.