PDA

View Full Version : looking for SQL help



jtag64
11-12-2010, 11:58 AM
I am working on a project to construct realistic hand ranges, and I am hoping to write an SQL query based on HEM data that can help with this construction.

The information I'm looking for is all available in some fashion in the HUD, but probably needs to be summed up somehow. What I'm looking for is a query to return the following rows. For each hand where we know a player's holecards, I would like to see.

playername
holecards
position (EP, MP, CO, BTN, SB, BB)
Player's total VPIP from this position
Player's total PFR from this position
How player entered the hand (call, raise, reraise).

the "from this position" is important - I happen to play with stats of 17 VPIP/13 PFR, but of course my raising range on the button is much wider than it is from EP. So if I raise up 82s on the button to steal blinds, I would want to see my BTN VPIP/PFR, not my overall 17/13 number.

My hope is to come up with information like "JTs is hand that's played in EP only by people with a VPIP of 30 or more". Stuff like that.

thank you

redlotus
11-16-2010, 01:36 PM
Sorry about not seeing this earlier. Give this a try:

WITH playervpip AS (SELECT player_id, AVG((case didvpip when true then 100::real else 0 end)*(case preflopaction_id>-1 when true then 1::real else null end)) AS vpip, positiontype_id FROM playerhandscashkeycolumns GROUP BY player_id, positiontype_id),
playerpfr AS(SELECT player_id, AVG((case didpfr when true then 100::real else 0 end)*(case preflopaction_id>-1 when true then 1::real else null end)) AS pfr, positiontype_id FROM playerhandscashkeycolumns GROUP BY player_id, positiontype_id)
SELECT playername, (SELECT holecardstring FROM holecards WHERE holecard_id=holecardvalue_id) AS "Hole Cards",
case positiontype_id when 2 then 'EP' when 3 then 'MP' when 4 then 'CO' when 5 then 'BTN' when 0 then 'SB' when 1 then 'BB' else ' ' end AS pos,
(SELECT to_char(vpip, '999D00') FROM playervpip WHERE playervpip.player_id=playerhandscashkeycolumns.pla yer_id AND playervpip.positiontype_id=playerhandscashkeycolum ns.positiontype_id) AS "VPIP",
(SELECT to_char(pfr, '999D00') FROM playerpfr WHERE playerpfr.player_id=playerhandscashkeycolumns.play er_id AND playerpfr.positiontype_id=playerhandscashkeycolumn s.positiontype_id) AS "PFR",
case firstpreflopactiontype_id when 1 then 'Check' when 2 then 'Call' when 3 then (case preflopaction_id<3 when true then 'Raise' else '3-Bet' end) else '?' end AS "How Entered"
FROM playerhandscashkeycolumns
JOIN players USING (player_id)
WHERE holecardvalue_id>0
GROUP BY playername, player_id, positiontype_id, "Hole Cards", pos, "How Entered"
ORDER BY playername, "Hole Cards", pos, "How Entered"

You'll have to change every instance of "playerhandscashkeycolumns" to "playerhandstourneykeycolumns" if you want stats from tournament players instead of cash players.

-red