filter question?

[ommaha]

Does that mean that there is no way to get it working properly because of the known issue?

[kraada]

If you're running Windows 8.1 or newer you should be able to see disk utilization in Task Manager - take a look there if you can to see if data is being processed. How long have you actually let the query run at the most?

[ommaha]

I run Win7.

I am not sure about the longest but at least 4 hours with big DB and today ~3 hours with the small DB (~400k hands)

[kraada]

Try opening PGAdmin III and connecting to your database, clicking the SQL icon in the toolbar and running this query:

select chps.id_hand from cash_hand_player_statistics chps where chps.id_player in (select cc.id_player from cash_cache cc where cc.cnt_players = 6 group by cc.id_player having sum(cc.cnt_p_open_opp_btn) > 0 and ((sum(cc.cnt_p_rfi_btn) * 1.0) / (sum(cc.cnt_p_open_opp_btn) * 1.0) >= .7 )) LIMIT 5;

The LIMIT 5 portion returns a maximum of 5 hands - on my testing database, this took about 12 seconds. So it should return data quickly if it's going to return it at all.

[BillGatesIII]

The SQL is ok and will run fast in pgAdmin.

However, in a Hand report it will return an error.

Code: Select all

Error: Unable to execute query: Fatal Error; Reason: Error:  (ERROR:  syntax error at or near "ORDER" LINE 1: ..._hand_player_statistics.id_gametype IN (2))) AND  ORDER BY

You can see in the log file something is wrong, the parser puts an AND before an ORDER BY.

Code: Select all

AND id_site='100')) AND (1=1) AND  ORDER BY (timezone('UTC',  cash_hand_playe

In a Player report it will run forever if you have any of the normal filters selected because the PT4 parser is way too creative. For instance, if I select This Month and Omaha Hi it looks like this.

Code: Select all

AND ((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_cache cc 
    where cc.cnt_players = 6   
    AND (((cash_hand_player_statistics.date_played >= (to_char(current_timestamp, 'YYYY-MM-01 00:00:00')::timestamp + INTERVAL '-2 HOURS') AND cash_hand_player_statistics.date_played <= (to_char(current_timestamp + INTERVAL '1 MONTH', 'YYYY-MM-01 00:00:00')::timestamp - INTERVAL '1 SECOND' + INTERVAL '-2 HOURS'))AND (cash_hand_player_statistics.id_gametype IN (2)))) group by cc.id_player  having sum(cc.cnt_p_open_opp_btn) > 0  and ( (CASE WHEN ( (sum(cc.cnt_p_open_opp_btn) * 1.0) ) <> 0 THEN (((sum(cc.cnt_p_rfi_btn) * 1.0) * 1.0 )/( (sum(cc.cnt_p_open_opp_btn) * 1.0) )) ELSE 0 END) >= .7 )))))  GROUP BY (cash_hand_player_statistics.id_player), (player_real.id_site), (player.player_name)


So you'll have to leave all the normal filters unselected; it will run for a couple of minutes in a decent sized database but you will get results.

As far as I can remember, there is a way to get stuff like this working in a hand report, can't find the SQL I used right now, will look for it Thursday.

[ommaha]

Try opening PGAdmin III and connecting to your database, clicking the SQL icon in the toolbar and running this query:

select chps.id_hand from cash_hand_player_statistics chps where chps.id_player in (select cc.id_player from cash_cache cc where cc.cnt_players = 6 group by cc.id_player having sum(cc.cnt_p_open_opp_btn) > 0 and ((sum(cc.cnt_p_rfi_btn) * 1.0) / (sum(cc.cnt_p_open_opp_btn) * 1.0) >= .7 )) LIMIT 5;

The LIMIT 5 portion returns a maximum of 5 hands - on my testing database, this took about 12 seconds. So it should return data quickly if it's going to return it at all.

Thank you all.

I tried that and I am shown (first colum no name, second column id_hand_integer)
1 1
2 1
3 2
4 2
5 3

I do not have an idea how to interpret that

[4StarGen]

As far as I can remember, there is a way to get stuff like this working in a hand report, can't find the SQL I used right now, will look for it Thursday.

Subscribed for this, I'm really interested in the workaround

[BillGatesIII]

Sorry guys for being late, had to do some serious partying

If you put this

Code: Select all

cash_hand_player_statistics.id_hand in (
  select chps.id_hand
  from cash_hand_player_statistics as chps
  where chps.flg_p_first_raise
  and chps.flg_p_open_opp
  and chps.position = 0
  and chps.id_player in (
    select
      distinct on (pt.id_player) pt.id_player
    from (
      select
        cc.id_player,
        sum(cc.cnt_hands) over pl as cnt_hands,
        sum(cc.cnt_p_rfi_btn) over pl as rfi_btn,
        sum(cc.cnt_p_open_opp_btn) over pl as rfi_btn_opp
      from cash_cache as cc
      where cc.id_gametype = 2
      and cc.id_limit = 8
      and cc.cnt_players between 4 and 6
      and to_char(now(), 'YYYYWW')::int - cc.date_played_year_week <= 26
      window pl as (partition by cc.id_player)) as pt
    where pt.cnt_hands >= 100
    and pt.rfi_btn_opp > 0
    and 100.0 * pt.rfi_btn / pt.rfi_btn_opp >= 70))


in an expression filter, it will show hands where a '6-max PLO regular' with a button opening range of >=70% (calculated for the last half year) opened the button. Use the normal filters to get yourself out of position on the flop.

Don't have time to explain it right now, maybe you can work it out yourself. If I feel like it, I might create a new thread to explain how this works and can be used for all kind of analyses.

[ommaha]

Thank you very very much!

I tried and it seems to work the right direction but I found some hands where villain has button open of just 56 instead of 70+ in my HUD. As I only used hands played in the last 2 months the 6 months should not be an issue here. I tried lifetime stats abled/disabled but it is still far from 70. In the code it looks like it is already filtered for 4-6 handed. My HUD is filtered the same. I also made sure that just this database is used. Do you have an idea where the difference comes from?

Thanks again very much

[BillGatesIII]

Maybe you have the option checked 'Stats From Current Cash Stake Only' in the Hud Options?


The number in this line

Code: Select all

and to_char(now(), 'YYYYWW')::int - cc.date_played_year_week <= 26

should be the same as in your Hud Options 'Stats from the last xx weeks'.


Hud Stat Values at Time of Hand should be unchecked in the replayer.


That's all I can think of right now.