Custom tournament stat - No valid SQL

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

Moderators: WhiteRider, kraada, Flag_Hippo, morny

Custom tournament stat - No valid SQL

Postby nik00 » Sat Jul 15, 2017 5:20 pm

Hello all,

I'm a little bit rusty when it comes to SQL stuff but I started building couple stats on PT4, however I'm now trying to get a "harder" one, and failing :/
So any help would be appreciated.

I'd like to get the player stack size on the first hand of HU during a tournament (if heads up happen).

So far I'm with query :

SELECT s.amt_before
FROM (
SELECT thps.amt_before, ths.id_hand, min(ths.id_hand) over (partition by ths.id_tourney) first_hu_hand
FROM tourney_hand_summary ths INNER JOIN tourney_hand_player_statistics thps ON (ths.id_hand = thps.id_hand)
WHERE ths.cnt_players = 2
) as s
WHERE s.id_hand = s.first_hu_hand

which kind of works within the PostgreSQL query interface (except it's returning the value for both HU players instead of just Hero, but I believe this will automagically get sorted out when applied within a Hero custom report).

However query is completely rejected by PT4.

Anyone knows what I'm doing wrong ?

Thanks alot !
nik00
 
Posts: 6
Joined: Tue Jun 03, 2014 5:07 pm

Re: Custom tournament stat - No valid SQL

Postby Flag_Hippo » Tue Jul 18, 2017 4:41 pm

Sorry but that particular query is beyond my knowledge of SQL at this time.
Flag_Hippo
Moderator
 
Posts: 11148
Joined: Tue Jan 31, 2012 7:50 am

Re: Custom tournament stat - No valid SQL

Postby nik00 » Wed Jul 19, 2017 3:08 pm

Even no idea why it would be OK in the postgeresql console but not valid in PT4 ?
nik00
 
Posts: 6
Joined: Tue Jun 03, 2014 5:07 pm

Re: Custom tournament stat - No valid SQL

Postby Flag_Hippo » Thu Jul 20, 2017 6:34 am

I did try the query in PostgreSQL and PokerTracker 4 but unfortunately I couldn't identify what's causing that.
Flag_Hippo
Moderator
 
Posts: 11148
Joined: Tue Jan 31, 2012 7:50 am

Re: Custom tournament stat - No valid SQL

Postby BillGatesIII » Fri Jul 21, 2017 9:41 am

Code: Select all
max(case
  when tourney_hand_summary.id_hand = (
    select tourney_hand_summary.id_hand
    from tourney_hand_summary as ths
    where ths.id_tourney = tourney_hand_summary.id_tourney
    and ths.cnt_players = 2
    order by ths.date_played
    limit 1)
  then tourney_hand_player_statistics.amt_before
  else 0 end)

I use date_played because id_hand might be in a different order.
BillGatesIII
 
Posts: 740
Joined: Fri Dec 16, 2011 6:50 pm

Re: Custom tournament stat - No valid SQL

Postby nik00 » Sat Jul 22, 2017 5:36 pm

BillGatesIII wrote:
Code: Select all
max(case
  when tourney_hand_summary.id_hand = (
    select tourney_hand_summary.id_hand
    from tourney_hand_summary as ths
    where ths.id_tourney = tourney_hand_summary.id_tourney
    and ths.cnt_players = 2
    order by ths.date_played
    limit 1)
  then tourney_hand_player_statistics.amt_before
  else 0 end)

I use date_played because id_hand might be in a different order.


First, thanks a lot BillGates, it is a valid expression in PT4 so it's going the right way.

However, when trying it in the reports it ends up in error because it's waiting for a sum, or other kind of type values.

So I changed the max() to sum(), which seems to make to me, because then i could divide it by the number of games to get and avg value.
But then, no more error in reports but it never finishes trying to load/refresh the report.

So either there is still another issue or it is way way way too slow (i waited couple minutes).

Any idea ?
nik00
 
Posts: 6
Joined: Tue Jun 03, 2014 5:07 pm

Re: Custom tournament stat - No valid SQL

Postby Flag_Hippo » Sun Jul 23, 2017 7:07 am

Expressions like this can generate a lot of PostgreSQL processing and take a while to complete especially if you have a large database so I'd recommend creating a new database with just a small sample of relevant hands and experiment with the filter there to make sure it works.
Flag_Hippo
Moderator
 
Posts: 11148
Joined: Tue Jan 31, 2012 7:50 am

Re: Custom tournament stat - No valid SQL

Postby BillGatesIII » Sun Jul 23, 2017 9:24 am

    @nik00: I guess there is something else wrong with your report. If I use my code in a test stat and create a new report, it looks like this.

    Chips at start of heads up.png
    Chips at start of heads up.png (9.67 KiB) Viewed 1400 times

    Apply a filter like Date: Today so it will not retreive all the tournaments you have played.
    BillGatesIII
     
    Posts: 740
    Joined: Fri Dec 16, 2011 6:50 pm

    Re: Custom tournament stat - No valid SQL

    Postby nik00 » Sun Jul 23, 2017 2:51 pm

    Yes, it seems OK on a small sample (however it needs to be ridiculously small to be "quick").

    But I'm interested in the average value of this over the whole database.

    Is it possible with PT4 to add a new row to lets say tourney_summary table in the database, by computing it through some query in the postgresql console, and then access it directly within PT4 custom stats ?
    So that it doesnt try to recompute it all the time (I don't mind if I have to compute it once for a while, if I can get it instantly afterwards).
    Is that doable or PT4 won't be able to "see" this new row in the table ?

    And if not, do you see any way to tweak the query to make it much faster ? (I guess that's hardly possible)
    nik00
     
    Posts: 6
    Joined: Tue Jun 03, 2014 5:07 pm

    Re: Custom tournament stat - No valid SQL

    Postby BillGatesIII » Thu Aug 03, 2017 7:43 am

    nik00 wrote:Yes, it seems OK on a small sample (however it needs to be ridiculously small to be "quick").

    Sorry about that, didn't test it on a big sample. This code is a bit faster but still too slow.
    Code: Select all
    max(case
      when tourney_hand_summary.id_hand = ( 
        select thps.id_hand 
        from tourney_hand_player_statistics as thps
        where thps.id_tourney = tourney_hand_player_statistics.id_tourney
        and thps.id_player = tourney_hand_player_statistics.id_player
        and thps.cnt_players = 2 
        order by thps.date_played
        limit 1)
      then tourney_hand_player_statistics.amt_before
      else 0 end)


    nik00 wrote:But I'm interested in the average value of this over the whole database.

    If you set the Summary Type of the column to Average, the bottom of the report will show the average for the displayed tournaments. Computing the average over your whole database implies that you only play one kind of tourneys with the same number of players and the same starting stack? If that is the case, it looks like you have enough SQL knowledge to compute it in the query editor.

    nik00 wrote:Is it possible with PT4 to add a new row to lets say tourney_summary table in the database, by computing it through some query in the postgresql console, and then access it directly within PT4 custom stats ?
    So that it doesnt try to recompute it all the time (I don't mind if I have to compute it once for a while, if I can get it instantly afterwards).
    Is that doable or PT4 won't be able to "see" this new row in the table ?

    You can add whatever you like to the PT4 tables and use that in PT4 columns. Of course, at your own risk :geek:

    nik00 wrote:And if not, do you see any way to tweak the query to make it much faster ? (I guess that's hardly possible)

    No, I do not see a way to make the query faster which kind of irritates me because it looks like it should be possible :|
    BillGatesIII
     
    Posts: 740
    Joined: Fri Dec 16, 2011 6:50 pm


    Return to Custom Stats, Reports and HUD Profiles

    Who is online

    Users browsing this forum: No registered users and 4 guests

    cron
    highfalutin