Filter for multiple players in Hand Report

Discuss how to create custom stats, reports and HUD profiles and share your creations.

Moderators: WhiteRider, kraada, Flag_Hippo, morny, Moderators

Filter for multiple players in Hand Report

Postby JackOscar95 » Sat May 06, 2023 7:23 am

What I've tried so far is first filtering for players in player report and then adding a filter #Player# IN (....) for all those players, but it seems to break when the string becomes too long so that doesn't work. Any better way to do this?
JackOscar95
 
Posts: 14
Joined: Fri Aug 28, 2020 1:10 pm

Re: Filter for multiple players in Hand Report

Postby JackOscar95 » Sat May 06, 2023 8:54 am

The thing I'm trying to filter for is:

Hands > 350
VPIP > 0.32
PFR/VPIP < 0.65
JackOscar95
 
Posts: 14
Joined: Fri Aug 28, 2020 1:10 pm

Re: Filter for multiple players in Hand Report

Postby Flag_Hippo » Sun May 07, 2023 1:21 pm

JackOscar95 wrote:What I've tried so far is first filtering for players in player report and then adding a filter #Player# IN (....) for all those players, but it seems to break when the string becomes too long so that doesn't work. Any better way to do this?

If you want to filter by player names you can use these formats:

Code: Select all
player.player_name = 'name1' OR player.player_name = 'name2'

or

Code: Select all
player.player_name SIMILAR TO '(name1|name2)'

JackOscar95 wrote:The thing I'm trying to filter for is:

Hands > 350
VPIP > 0.32
PFR/VPIP < 0.65

Filtering a hand report based on player statistics will require a subquery - see this thread for an example.
Flag_Hippo
Moderator
 
Posts: 14441
Joined: Tue Jan 31, 2012 7:50 am

Re: Filter for multiple players in Hand Report

Postby JackOscar95 » Tue May 09, 2023 1:24 am

Flag_Hippo wrote:
JackOscar95 wrote:What I've tried so far is first filtering for players in player report and then adding a filter #Player# IN (....) for all those players, but it seems to break when the string becomes too long so that doesn't work. Any better way to do this?

If you want to filter by player names you can use these formats:

Code: Select all
player.player_name = 'name1' OR player.player_name = 'name2'

or

Code: Select all
player.player_name SIMILAR TO '(name1|name2)'

JackOscar95 wrote:The thing I'm trying to filter for is:

Hands > 350
VPIP > 0.32
PFR/VPIP < 0.65

Filtering a hand report based on player statistics will require a subquery - see this thread for an example.


Thank you, that gave me enough to go on to create my own subquery to filter with. What I came up with is this:

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 group by chps.id_hand
having not
(
( (sum(cc.cnt_vpip) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.3 and
( (sum(cc.cnt_pfr) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.25 and
( (sum(cc.cnt_vpip) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) - ( (sum(cc.cnt_pfr) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.08 and
( (sum(cc.cnt_p_limp) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.02 and
(sum(cc.cnt_hands) > 100) )
or (sum(cc.cnt_hands) > 20000)
)

Unfortunately, my report just gets stuck on "loading..." even after several hours. Do you think this query is too slow to execute or something else? It's a pretty large DB ~25gb.
JackOscar95
 
Posts: 14
Joined: Fri Aug 28, 2020 1:10 pm

Re: Filter for multiple players in Hand Report

Postby Flag_Hippo » Tue May 09, 2023 5:33 am

JackOscar95 wrote:Unfortunately, my report just gets stuck on "loading..." even after several hours. Do you think this query is too slow to execute or something else? It's a pretty large DB ~25gb.

Your filter works for me on a smaller database but on larger databases these types of queries are going to be much slower.
Flag_Hippo
Moderator
 
Posts: 14441
Joined: Tue Jan 31, 2012 7:50 am

Re: Filter for multiple players in Hand Report

Postby JackOscar95 » Tue May 09, 2023 11:52 am

Flag_Hippo wrote:
JackOscar95 wrote:Unfortunately, my report just gets stuck on "loading..." even after several hours. Do you think this query is too slow to execute or something else? It's a pretty large DB ~25gb.

Your filter works for me on a smaller database but on larger databases these types of queries are going to be much slower.



Thanks for trying that out, is there any chance it would finish loading if I waited for like 24+ hours or does it time out well before that?

If so, then maybe I need to find a solution with a "player.player_name SIMILAR TO '(name1|name2)'" approach, the problem there however is that the list of players becomes too large for PT4 to read the query. Is it possible to get around this by creating a table and insert the player names and then write a query with that? e.g.

player.player_name IN (SELECT playername from CustomPlayerTable)
JackOscar95
 
Posts: 14
Joined: Fri Aug 28, 2020 1:10 pm

Re: Filter for multiple players in Hand Report

Postby Flag_Hippo » Tue May 09, 2023 1:02 pm

JackOscar95 wrote:Thanks for trying that out, is there any chance it would finish loading if I waited for like 24+ hours or does it time out well before that?

I wouldn't know how long it would take or if it would finish as there are too many variables. If you are going to leave a query running for awhile then I'd suggest testing it on a smaller database first so you can verify it works and you are getting the kind of results you want to see.
JackOscar95 wrote:If so, then maybe I need to find a solution with a "player.player_name SIMILAR TO '(name1|name2)'" approach, the problem there however is that the list of players becomes too large for PT4 to read the query. Is it possible to get around this by creating a table and insert the player names and then write a query with that?

Unfortunately that's not something I know anything about but bear in mind these filters will give you different kinds of results. A filter using names with give you all hands played by those players in a hand report (with the filter on active player option turned off) from their perspective. The subquery is different as it checks every player in every hand and if any player has matching stats to your query the hand gets returned which is suitable if you wanted to see the hands from your perspective when any of these players are dealt in.
Flag_Hippo
Moderator
 
Posts: 14441
Joined: Tue Jan 31, 2012 7:50 am

Re: Filter for multiple players in Hand Report

Postby JackOscar95 » Tue May 09, 2023 1:27 pm

Flag_Hippo wrote:
JackOscar95 wrote:Thanks for trying that out, is there any chance it would finish loading if I waited for like 24+ hours or does it time out well before that?

I wouldn't know how long it would take or if it would finish as there are too many variables. If you are going to leave a query running for awhile then I'd suggest testing it on a smaller database first so you can verify it works and you are getting the kind of results you want to see.
JackOscar95 wrote:If so, then maybe I need to find a solution with a "player.player_name SIMILAR TO '(name1|name2)'" approach, the problem there however is that the list of players becomes too large for PT4 to read the query. Is it possible to get around this by creating a table and insert the player names and then write a query with that?

Unfortunately that's not something I know anything about but bear in mind these filters will give you different kinds of results. A filter using names with give you all hands played by those players in a hand report (with the filter on active player option turned off) from their perspective. The subquery is different as it checks every player in every hand and if any player has matching stats to your query the hand gets returned which is suitable if you wanted to see the hands from your perspective when any of these players are dealt in.



Oh, I see. What I'm actually interested in seeing the hands from *their* perspective.

From what I can see the player table has the column id_player so I suppose just this should work (also also compiles):

player.player_name in
(select pl.player_name
from player pl, cash_hand_player_statistics chps, cash_cache cc
where pl.id_player = chps.id_player and chps.id_player = cc.id_player
group by pl.player_name
having not
(
( (sum(cc.cnt_vpip) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.3 and
( (sum(cc.cnt_pfr) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.25 and
( (sum(cc.cnt_vpip) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) - ( (sum(cc.cnt_pfr) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.08 and
( (sum(cc.cnt_p_limp) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.02 and
(sum(cc.cnt_hands) > 100) )
or (sum(cc.cnt_hands) > 20000)
)

I guess this should take equally long or even longer to query, however.
JackOscar95
 
Posts: 14
Joined: Fri Aug 28, 2020 1:10 pm

Re: Filter for multiple players in Hand Report

Postby JackOscar95 » Wed May 10, 2023 6:38 am

Okay, new issue...

I've decided to make do with the "player in" filter, but the problem now is that I can only retrieve 100,001 hands in the report at a time. How do I bypass this? If possible I would only need to be able to export the results to CSV, not view them in the program.
JackOscar95
 
Posts: 14
Joined: Fri Aug 28, 2020 1:10 pm

Re: Filter for multiple players in Hand Report

Postby Flag_Hippo » Wed May 10, 2023 1:28 pm

JackOscar95 wrote:I've decided to make do with the "player in" filter, but the problem now is that I can only retrieve 100,001 hands in the report at a time. How do I bypass this? If possible I would only need to be able to export the results to CSV, not view them in the program.

See this post.
Flag_Hippo
Moderator
 
Posts: 14441
Joined: Tue Jan 31, 2012 7:50 am

Next

Return to Custom Stats, Reports and HUD Profiles

Who is online

Users browsing this forum: No registered users and 12 guests

cron
highfalutin