Add AvgROI%, as counted by SharkScope

[lumst]

Hi,
I have a request for useful stat "BI_ROI%". Nowadays its counted by SharkScope, but not PT4.
===
In PT4, ROI% is shown as = My_C_Net_Won / My_C_Buyin
However, playing different buyin levels, this approach are not enough to measure the skill.
Let's use kraada's example https://www.pokertracker.com/forums/viewtopic.php?f=59&t=37415&p=187202&hilit=roi#p189902

You play 10 $10 tournaments (let's assume no rake for now thanks to math) and win $50.
You play 10 $1 tournaments and win $20.
Does this mean at the end of the graph you would have shown as +25 buy-ins? (50 / 10 + 20 / 1 = 5 + 20 = 25)

In average,
- win for each $10 tourney is $50/10=$5. ROI% is $5/$10=50%
- win for each $1 tourney is $20/10=$2. ROI% is $2/$1=200%

PT4 would count Avg ROI% as (50+20)/(10*10+1*10)=63.63%.
Sharkscope would count it (let's define it "BI ROI%") as (($50/$10+$20/$1))/(10+10)=125%, because 25 buyins has been won in 20 tourneys.

125% is non-weighted average between 50% and 200%, and tells mostly about overall player's skills in this structure, no matter of buyin amount.
63.63% is weighted average between 50% with weight $10 and 200% with weight $1. It always be close to ROI of highest level, and tells mostly about cashflow and skills on highest buyin level.

So, these two values are both useful, and should be shown together in "View Stats/T/Results/Tournament/Overview", like
ROI%=63.63%
BI_ROI%=125%

[kraada]

This could certainly be built as a custom statistic. I'll put it on my list and post it here once it's ready.

[kraada]

Your stat is attached.

[lumst]

Thanks a lot!
However, I found I could see it only in any hand playback, having "Show hero Lifetime stats" option on.
Could it be viewed somehow in more convenient place, than Playback?
For ex,
- on main Stats page,
- on HUD during play (now HUD always show stat for current table only, it is -1 buyin)

[kraada]

On the HUD your stats will always be session only - and for this session you've lost one buy-in until you win something.

You should be able to add it to other reports by right clicking the report and clicking Configure Report.

[lumst]

Thanx, I have imported this and created custom stat with the value

Code: Select all

amt_won_in_buyins *100 / cnt_tourneys


and found this way is _too_ slow.
For 147 tourneys at yesterday is takes 22 sec,
For 649 tourneys at today is takes 98 sec
For 6463 tourneys at this month is takes 898 sec
For 23065 tourneys at this and previous month is wasn't enough 35 minutes. So I had to cancel the custom report and come see this info in SharkScope advanced query. So bad.

To get the custom stat report, the engine goes thru all the hands and tourneys, and counts the winning for each tourney. It is way too slow, because is contains clause

Code: Select all

 FROM  tourney_hand_player_statistics , player, tourney_summary, tourney_results, player player_real

The execution time depends on a factor of dimensions of all these tables.
I believe table "tourney_hand_player_statistics" is redundant here. No need to look into each hand. Only tourney results matters. However, I understand its generated from a generic template that is universal and slow.
Full version

Code: Select all

SELECT id_player, id_site, str_player_name, count(distinct cnt_tourneys), sum(amt_won_in_buyins) FROM ( SELECT (tourney_hand_player_statistics.id_player) as "id_player", (player_real.id_site) as "id_site", (player.player_name) as "str_player_name", ((tourney_summary.id_tourney)) as "cnt_tourneys", (( (CASE WHEN ( (tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) ) <> 0 THEN (( (tourney_results.amt_won - (tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty)) * 1.0 )/( (tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) )) ELSE 0 END) )) as "amt_won_in_buyins" FROM               tourney_hand_player_statistics , player, tourney_summary, tourney_results, player player_real WHERE  (player.id_player = tourney_hand_player_statistics.id_player)  AND (tourney_summary.id_tourney = tourney_hand_player_statistics.id_tourney)  AND (tourney_results.id_tourney = tourney_hand_player_statistics.id_tourney  AND tourney_results.id_player = tourney_hand_player_statistics.id_player)  AND (player_real.id_player = tourney_hand_player_statistics.id_player_real)  AND (player.id_player = tourney_results.id_player)  AND (tourney_results.id_tourney = tourney_summary.id_tourney)  AND (player_real.id_player = tourney_results.id_player_real)   AND (tourney_results.id_player = (SELECT id_player FROM player WHERE player_name_search='HERO'  AND id_site='100'))        AND (((tourney_summary.date_start >= (to_char(current_timestamp - interval '24 hours', 'YYYY-MM-DD 00:00:00')::timestamp + INTERVAL '8 HOURS') AND tourney_summary.date_start <= (to_char(current_timestamp - interval '24 hours', 'YYYY-MM-DD 23:59:59')::timestamp + INTERVAL '8 HOURS'))))  GROUP BY tourney_results.cnt_addon, tourney_results.cnt_rebuy, tourney_results.amt_won, tourney_summary.amt_bounty, tourney_summary.amt_addon, tourney_summary.amt_rebuy, tourney_summary.amt_fee, tourney_summary.amt_buyin, tourney_summary.id_tourney, (tourney_hand_player_statistics.id_player), (player_real.id_site), (player.player_name) ) AS tnySumGroup GROUP BY id_player, id_site, str_player_name


My tables have the following dimensions

Code: Select all

select count (*) from tourney_hand_player_statistics
14490566
select count (*) from player
51428
select count (*) from tourney_summary
264901
select count (*) from tourney_results
1541284


So, I'd like to repeat my feature request.
It seems to be very useful to create new columns in View Stats/T/Results/Tournament/By buy-in
Let's look, Now we got columns

Buy-In
My C Buy-In
My C Net Won
Tournaments
ITM%
Avg Finish
1st place
2nd place
3rd place
Avg Players

Shouldn't be so hard to add columns to each row and the summary?

Buyins won = My C Net Won / Buy-In
ROI = Buyins won / Tournaments

It will greatly help to instantly estimate the estimation and variance of similar tourney series

[kraada]

In the time since the last post in this thread we have added a default statistic to PT4 called Buy-Ins Won. You should be able to use that one in that report and you should not need a custom statistic and that should work as swiftly as any other stats that are built in.

[lumst]

I just tried it. It results in the same SQL query, that lasts the same 98 secs for 649 tourneys. The speed is approx 7 tourneys per second
For 6463 tourneys it was 898 secs, approx the same 7 tourneys/sec
So, 23065 games since Feb,1 2013 will last for 3200sec=53 minutes. No surprise I couldnt get the result in 35 minutes and had to cancel
Overall, I played 85566 tourneys, and I could get the page "results/Tournament/By buy-In" in 15 seconds
And to get ROI, it will take 85566/7=11888 secs = 3.3 hours
Dont think thats right, it would be much better, if ROI will be counted instantly on "results/Tournament/By buy-In" page within those 15 seconds

Its no matter, if it custom stat or builtin stat. Both queries are of the same redundant unoptimized structure
For the report of columns "Player", "Buy-Ins Won" the query is

SELECT id_player, id_site, str_player_name, sum(amt_won_in_buyins) FROM ( SELECT (tourney_hand_player_statistics.id_player) as "id_player", (player_real.id_site) as "id_site", (player.player_name) as "str_player_name", (( (CASE WHEN ( (tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) ) <> 0 THEN (( (tourney_results.amt_won - (tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty)) * 1.0 )/( (tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) )) ELSE 0 END) )) as "amt_won_in_buyins" FROM tourney_hand_player_statistics , player, tourney_summary, tourney_results, player player_real WHERE (player.id_player = tourney_hand_player_statistics.id_player) AND (tourney_summary.id_tourney = tourney_hand_player_statistics.id_tourney) AND (tourney_results.id_tourney = tourney_hand_player_statistics.id_tourney AND tourney_results.id_player = tourney_hand_player_statistics.id_player) AND (player_real.id_player = tourney_hand_player_statistics.id_player_real) AND (player.id_player = tourney_results.id_player) AND (tourney_results.id_tourney = tourney_summary.id_tourney) AND (player_real.id_player = tourney_results.id_player_real) AND (tourney_results.id_player = (SELECT id_player FROM player WHERE player_name_search='HERO' AND id_site='100')) AND (((tourney_summary.date_start >= (to_char(current_timestamp - interval '24 hours', 'YYYY-MM-DD 00:00:00')::timestamp + INTERVAL '8 HOURS') AND tourney_summary.date_start <= (to_char(current_timestamp - interval '24 hours', 'YYYY-MM-DD 23:59:59')::timestamp + INTERVAL '8 HOURS')))) GROUP BY tourney_summary.id_tourney, tourney_results.cnt_addon, tourney_results.cnt_rebuy, tourney_results.amt_won, tourney_summary.amt_bounty, tourney_summary.amt_addon, tourney_summary.amt_rebuy, tourney_summary.amt_fee, tourney_summary.amt_buyin, (tourney_hand_player_statistics.id_player), (player_real.id_site), (player.player_name) ) AS tnySumGroup GROUP BY id_player, id_site, str_player_name

[lumst]

Or, maybe, you could suggest some SQL speedup techniques, like creation additional indices for tables "tourney_hand_player_statistics , player, tourney_summary, tourney_results" ?

PS I'd wish we could get a possibitility to create new type of report, that deals only only with tourneys, not with every hand. So we could get rid of table "tourney_hand_player_statistics"
But donk think it would be useful for anything else

So, certainly, for the current task the best would to add two columns to "Results/By Byuin" page

[kraada]

You can add data to any report you'd like by right clicking the report and clicking Configure Report - I meant to say you can add that stat to that report and it should display just fine.