WANTED: "Simple" Query example

[js2002]

Hi,

I´m coding a tool that converts hands and does SQL queries to get following stats:

VPIP/PFR/AF/AFq/WTSD/W$@SD/3bet Hands

I tried learning by the logfile:

Code: Select all

SELECT (player_real.player_name_search) as "str_player_name_search_real", (sum(if[holdem_hand_player_statistics.id_hand > 0, 1, 0])) as "cnt_hands", (player.player_name) as "str_player_name", (sum(if[holdem_hand_player_statistics.flg_vpip, 1, 0])) as "cnt_vpip", (sum(if[holdem_hand_player_statistics.cnt_p_raise > 0, 1, 0])) as "cnt_pfr", (sum(if[holdem_hand_player_statistics.flg_f_bet, 1, 0])) as "cnt_f_bet", (sum(holdem_hand_player_statistics.cnt_f_raise)) as "cnt_f_raise", (sum(if[holdem_hand_player_statistics.flg_t_bet, 1, 0])) as "cnt_t_bet", (sum(holdem_hand_player_statistics.cnt_t_raise)) as "cnt_t_raise", (sum(if[holdem_hand_player_statistics.flg_r_bet, 1, 0])) as "cnt_r_bet", (sum(holdem_hand_player_statistics.cnt_r_raise)) as "cnt_r_raise", (sum(holdem_hand_player_statistics.cnt_f_call)) as "cnt_f_call", (sum(holdem_hand_player_statistics.cnt_t_call)) as "cnt_t_call", (sum(holdem_hand_player_statistics.cnt_r_call)) as "cnt_r_call", (sum(if[holdem_hand_player_statistics.flg_f_fold, 1, 0])) as "cnt_f_fold", (sum(if[holdem_hand_player_statistics.flg_t_fold, 1, 0])) as "cnt_t_fold", (sum(if[holdem_hand_player_statistics.flg_r_fold, 1, 0])) as "cnt_r_fold", (sum(if[holdem_hand_player_statistics.flg_showdown, 1, 0])) as "cnt_wtsd", (sum(if[holdem_hand_player_statistics.flg_f_saw, 1, 0])) as "cnt_f_saw", (sum(if[holdem_hand_player_statistics.flg_p_3bet, 1, 0])) as "cnt_p_3bet", (sum(if[holdem_hand_player_statistics.flg_p_3bet_opp, 1, 0])) as "cnt_p_3bet_opp" FROM  holdem_hand_summary, holdem_hand_player_statistics , player, player player_real WHERE  (player.id_player = holdem_hand_player_statistics.id_player)   AND (player_real.id_player = holdem_hand_player_statistics.id_player_real)   AND (holdem_hand_summary.id_hand = holdem_hand_player_statistics.id_hand)   AND ((holdem_hand_summary.cnt_players >= 4 AND holdem_hand_summary.cnt_players <= 10)AND (holdem_hand_player_statistics.date_played < '2009/11/05 14:32:19')AND ((holdem_hand_player_statistics.id_player in (SELECT if[p.id_player_alias <> 0, p.id_player_alias, p.id_player] FROM player p WHERE ((p.id_site=300 AND p.player_name_search='mary wing') OR (p.id_site=300 AND p.player_name_search='_soya76_') OR (p.id_site=300 AND p.player_name_search='iron_man_404') OR (p.id_site=300 AND p.player_name_search='obv r1gg3d') OR (p.id_site=300 AND p.player_name_search='777amanda777'))))))  GROUP BY (player_real.player_name_search), (player.player_name) HAVING (((sum(if[holdem_hand_player_statistics.id_hand > 0, 1, 0])) >= 1))
2009/11/06 10:49:49.047: [00002B58][Query] Playback for Full Tilt Hand #15810320231 - $0.10 NL (6 max) - Texas Holdem [TA0]: Query execution time: 7.92433859 ms :

When I use parts of it in pgadmin:

Code: Select all

SELECT (sum(if[holdem_hand_player_statistics.flg_p_3bet_opp, 1, 0])) as "cnt_p_3bet_opp" FROM holdem_hand_player_statistics WHERE id_player = 3054
fails at pgadmin:
ERROR: syntax error at or near ","
SQL Status:42601
Zeichen:60

So I guess thats PT3 code, so I cant do it this way.
Can you show me one little Query that returns a stat?
f.e. "Give me the PFR of player "Homofürst"" (so <a player id search> included, I only succeed by knowing the player_id)

[kraada]

You're looking in the PT3 log file; you want to look at the pg_log files which are the actual PostgreSQL logs. You may need to increase the logging level in order to see all queries, though you can do that via Start --> Programs --> PostgreSQL --> Configuration Files --> Edit Postgresql.conf (you will need to restart your PostgreSQL server after making changes to that file).

A query to get the columns for PFR from a specific player looks like this:

SELECT (player.player_name) as "str_player_name", (sum((case when(holdem_hand_player_statistics.cnt_p_raise > 0) then 1 else 0 end))) as "cnt_pfr", (sum((case when(holdem_hand_player_statistics.id_hand > 0) then 1 else 0 end))) as "cnt_hands" FROM holdem_hand_player_statistics , player WHERE (player.id_player = (SELECT id_player from player where player_name = 'Homofürst')) GROUP BY player.player_name;

Then you can calculate PFR as cnt_pfr / cnt_hands * 100. If you want it all done 100% by PostgreSQL you would run this:

SELECT (player.player_name) as "str_player_name", 100 * (sum((case when(holdem_hand_player_statistics.cnt_p_raise > 0) then 1 else 0 end))) / (sum((case when(holdem_hand_player_statistics.id_hand > 0) then 1 else 0 end))) as "PFR" FROM holdem_hand_player_statistics , player WHERE (player.id_player = (SELECT id_player from player where player_name = 'Homofürst')) GROUP BY player.player_name;

And that should do it for you.

[js2002]

* * * * * 5 Stars 4 that !!!

thx 4 turbo and concret answer....

thats al I need. I understand the first SQL Query 100% the second: I have to take a further look, but u explain very well what it does.

[js2002]

Ah 1 little additional question:

SELECT (player.player_name) as "str_player_name", CAST(100 * (sum((case when(holdem_hand_player_statistics.cnt_p_raise > 0) then 1 else 0 end))) / (sum((case when(holdem_hand_player_statistics.id_hand > 0) then 1 else 0 end))) as numeric(38,4)) as "PFR" FROM holdem_hand_player_statistics , player WHERE (player.id_player = (SELECT id_player from player where player_name = 'Homofürst')) GROUP BY player.player_name;

resulted in 14,0000 hmmmm, that cant be true.
Wrong place for that cast?

[kraada]

The second query should just compound things together; I think the reason PT3 does it the way that it does is that things are re-used and don't need to be recalculated.

There's a simpler way to do this:
SELECT (player.player_name) as "str_player_name", 100.0 * (sum((case when(holdem_hand_player_statistics.cnt_p_raise > 0) then 1 else 0 end)))::float / (sum((case when(holdem_hand_player_statistics.id_hand > 0) then 1 else 0 end)))::float as "PFR" FROM holdem_hand_player_statistics , player WHERE (player.id_player = (SELECT id_player from player where player_name = 'Homofürst')) GROUP BY player.player_name;

That works. I'm casting as float here but you could cast as numeric just as well; I figure float is good for what you need, yes?

[js2002]

sure, though numeric and float were the same.

also didnt know the :: notation, thx

[js2002]

sry but I cant put a TRUNC(.::float....::float..,2) on that because the notation is: TRUNC(...,2)::float

So:

TRUNC(100.00 * (sum((case when(holdem_hand_player_statistics.cnt_p_raise .....

where do I put the "2" ?

ah damn: I dont need trunc I need sth like "round".

[kraada]

See Example 10-4.