by kraada » Fri Nov 06, 2009 12:02 pm
Some more recent analysis for related reports makes this filter a lot more easily doable. This filter could apply to either the Holdem Cash Hand or Holdem Cash Player Statistics section. Given that you want overall stats as you say, I'm going to assume we're looking at the HCPS section.
You want to start with a filter like this:
holdem_hand_player_statistics.id_hand in (SELECT holdem_hand_player_statistics.id_hand from player, holdem_hand_player_statistics WHERE player.id_player = holdem_hand_player_statistics.id_player and player.val_icon = X)
If you use that alone in a report that just had Player Name and VP$IP, it would show you all the names of players and their VP$IP in hands where someone with val_icon = X dealt in.*
Let me break it down for you. The report is set to now only look at specific hands. Which hands? The hands where id_hand is among the results from a certain query. That query is:
SELECT holdem_hand_player_statistics.id_hand from player, holdem_hand_player_statistics WHERE player.id_player = holdem_hand_player_statistics.id_player and player.val_icon = X
In the query we are joining to the player table on id_player and then making sure that the player's icon is X. Thus if there was a hand with no player who had val_icon of X, it would not come back from this query. Since all stats in this custom report are generated only from hands in the list, we'll see whatever stats only from hands involving players with val_icon of X.
You can then narrow things down further using this rubric as a starting point.
For example, you wanted to analyze stats when there were TAGs in the blinds. To modify the above to require the other TAGs to be in the blinds, change the filter to this:
holdem_hand_player_statistics.id_hand in (SELECT holdem_hand_player_statistics.id_hand from player, holdem_hand_player_statistics WHERE player.id_player = holdem_hand_player_statistics.id_player and player.val_icon = X and holdem_hand_player_statistics.flg_blind_b)
For clarity's sake, the changes from the rubric were bolded. What did we add? We added another condition that in the hand in question the player with val_icon X must also be in the big blind (flg_blind_b).
Now if we run the report with, say, our player name and attempt to steal, we'll see our attempt to steal percentage only for those times a player with val_icon X is in the big blind. To make it for times when there are TAGs in both blinds, add another filter, identical to the above except using flg_blind_s - then you'll filter first for hands with TAGs in the big blind, then for hands with TAGs in the small blind, and the result will be hands with TAGs in both blinds.
You also wanted "3bet pots versus other TAGs". This is going to be a bit more work as you're going to need to clarify exactly what you mean by this. Who is doing the 3betting? Does the hand need to reach a flop? But once you clarify exactly what you mean here, the above rubric should help. You may need to add another filter for hands in which you have done something, depending on exactly what you want.
To refer to yourself replace:
player.val_icon = X
with
player.id_player::int = (SELECT setting_value::int from settings where setting_name = 'my_player_id')
Then you can also limit to only hands in which you've done certain things. Please note that using filters like these currently are incompatible with the simple filters. This is something we are working to fix, although I don't know exactly at what point the fix will become available.
*You can actually change the icon numbers around based on the value in your Settings tab, so you may need to experiment in order to figure out what is the val_icon for TAGs in your setup. It's going to be a number between 1 and 14 in any case, so if you know the name of one TAG and run the query with that number and sort by Player Name, you should be able to go through the options reasonably quickly and figure out which number you want. I'm going to use X through out in order to keep things suitably generic.