Flopped set query by date and site (postgreSQL)

tautomer · #1 03-31-2007, 07:23 PM

I figured all the full ring set miners would get some use out of this. [img]/images/graemlins/wink.gif[/img]

It's a flopped set query (use pgAdmin) that separates everything by date and site and pocket pair. It's kind of a patchwork query as I have no background in this area and had to use ideas/lines from unrelated queries to get this to output correctly. Every set query I've seen posted was for the entire database and just spit out one small table for everything. I wanted more detail so I threw this together. Whatever, it works.

You need a postgreSQL database though, it wont work on the default Access databases in PokerTracker. Open pgAdmin, connect to the postgreSQL server, click on your database, click on the SQL button (looks like a pencil and paper), then paste the below query into the window that pops up and press the run button.

At the bottom where it says "AND g.site_id = 2", the 2 stands for Poker Stars. Full Tilt is 11, Ultimate Bet is 4, and Party Poker is 3 so you'd need to change the numbers depending on the site of interest. Those are the only sites I've played so I can't tell you the site numbers of any others. But, I believe you can just delete that line altogether and the output will be for all sites and you can separate them as you see fit. Copy the data into Excel and create a pivot table, works like a charm. I'd be interested in seeing other people's results if you're willing to share. And if anyone with the know-how could have this query spit out the total $ winnings for sets and total $ losses when they get cracked/oversetted, that would be awesome. Here it is:

SELECT
site_id AS site,
to_char(date_played, 'MM/DD/YYYY') AS Date,
gp.hole_cards AS Pair,
count(*) AS Times,
SUM(
CASE WHEN rtrim(g.flop_1,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_2,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_3,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE 0 END END END ) as Flopped_Set , SUM((
CASE WHEN rtrim(g.flop_1,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_2,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_3,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE 0 END END END )*Won_Hand) as Won_With_Set, SUM((1-
CASE WHEN rtrim(g.flop_1,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_2,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_3,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE 0 END END END )*Won_Hand) as Won_Without_Set

FROM game_players AS gp INNER JOIN game AS g ON gp.game_id = g.game_id

WHERE
gp.player_id = (select pref_value from prefs where pref_key = 'RP')
AND gp.pair_hand = 1
AND gp.saw_flop_n = 1
AND g.site_id = 2
GROUP BY g.date_played, g.site_id, gp.hole_cards, gp.card_order1

ORDER BY g.date_played DESC

kidpokeher · #2 04-01-2007, 12:09 AM

This is cool, thanks! Is there a way of adding something similar to a REM statement to the query to help me remember to change the different site ID's? Wildzer0 seems to be the resident expert on SQL. Maybe he'd be able to help get totals on this query and do the other stuff you mentioned.

tautomer · #3 04-01-2007, 01:32 AM

I'm not sure how to do that. I have a separate Excel file for each site and usually update it when I'm done with a session before I forget. And it gives me an excuse to review my hands, which is always a good thing.

Anyway, you should flop a set or better 11.76% of the time you see a flop and get dealt a pocket pair 5.88% of the time.

My results:
Full Tilt (24000 hands) pocket pair: 5.89% set: 11.84% won with set: 92%
Ultimate Bet (42000 hands) pocket pair: 5.68% set: 11.31% won with set: 89.8%
Poker Stars (96000 hands) pocket pair: 5.84% set: 11.19% won with set: 91%

The numbers are close to expected so I guess the sites aren't rigged after all!

skylos · #4 04-01-2007, 10:15 AM

Cool stuff thanks. I only have 63k hands but my numbers are pocket pair: 5.95% set: 10.54% and won with set: 90.44%.

So I'm missing about 18 or so flopped sets but the other numbers look pretty close. To bad I can't ever win a pot with a >75% edge when the money goes in either. Would there be an easy way to do a search for something like win % based on equity when the pot is like bigger than a certain amount or something?

myheadhurts · #5 04-01-2007, 10:33 AM

[ QUOTE ]
I figured all the full ring set miners would get some use out of this. [img]/images/graemlins/wink.gif[/img]

It's a flopped set query (use pgAdmin) that separates everything by date and site and pocket pair. It's kind of a patchwork query as I have no background in this area and had to use ideas/lines from unrelated queries to get this to output correctly. Every set query I've seen posted was for the entire database and just spit out one small table for everything. I wanted more detail so I threw this together. Whatever, it works.

You need a postgreSQL database though, it wont work on the default Access databases in PokerTracker. Open pgAdmin, connect to the postgreSQL server, click on your database, click on the SQL button (looks like a pencil and paper), then paste the below query into the window that pops up and press the run button.

At the bottom where it says "AND g.site_id = 2", the 2 stands for Poker Stars. Full Tilt is 11, Ultimate Bet is 4, and Party Poker is 3 so you'd need to change the numbers depending on the site of interest. Those are the only sites I've played so I can't tell you the site numbers of any others. But, I believe you can just delete that line altogether and the output will be for all sites and you can separate them as you see fit. Copy the data into Excel and create a pivot table, works like a charm. I'd be interested in seeing other people's results if you're willing to share. And if anyone with the know-how could have this query spit out the total $ winnings for sets and total $ losses when they get cracked/oversetted, that would be awesome. Here it is:

SELECT
site_id AS site,
to_char(date_played, 'MM/DD/YYYY') AS Date,
gp.hole_cards AS Pair,
count(*) AS Times,
SUM(
CASE WHEN rtrim(g.flop_1,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_2,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_3,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE 0 END END END ) as Flopped_Set , SUM((
CASE WHEN rtrim(g.flop_1,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_2,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_3,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE 0 END END END )*Won_Hand) as Won_With_Set, SUM((1-
CASE WHEN rtrim(g.flop_1,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_2,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_3,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE 0 END END END )*Won_Hand) as Won_Without_Set

FROM game_players AS gp INNER JOIN game AS g ON gp.game_id = g.game_id

WHERE
gp.player_id = (select pref_value from prefs where pref_key = 'RP')
AND gp.pair_hand = 1
AND gp.saw_flop_n = 1
AND g.site_id = 2
GROUP BY g.date_played, g.site_id, gp.hole_cards, gp.card_order1

ORDER BY g.date_played DESC

[/ QUOTE ]

Absolutely bloody brilliant, thanks.

I put it through my 2007 stats, and noted that I had never had a flopped set of Jacks cracked (every other set had been cracked 5+ times). I thought to myself "now I know that JJJ will never hold up".

THe very next day:

PokerStars No-Limit Hold'em, $1.00 BB (8 handed) Hand History Converter Tool from FlopTurnRiver.com (Format: 2+2 Forums)

Hero ($99.30)
UTG ($98.05)
UTG+1 ($39.65)
MP1 ($80.75)
MP2 ($39)
CO ($107.50)
Button ($92.20)
SB ($146.70)

Preflop: Hero is BB with J[img]/images/graemlins/heart.gif[/img], J[img]/images/graemlins/club.gif[/img].
UTG calls $1, 3 folds, CO raises to $3, 2 folds, Hero calls $2, UTG calls $2.

Flop: ($9.50) Q[img]/images/graemlins/heart.gif[/img], J[img]/images/graemlins/spade.gif[/img], 9[img]/images/graemlins/heart.gif[/img] (3 players)
Hero bets $7.5, UTG raises to $15, CO calls $15, Hero calls $7.50.

Turn: ($54.50) 5[img]/images/graemlins/diamond.gif[/img] (3 players)
Hero checks, UTG bets $21, CO calls $21, Hero raises to $81.3, UTG calls $59.05 (All-In), CO calls $60.30.

River: ($297.15) K[img]/images/graemlins/club.gif[/img] (3 players, 1 all-in)

Final Pot: $297.15

Results in white below:
Hero has Jh Jc (three of a kind, jacks).
UTG has Ks Td (straight, king high).
CO has Qd Qc (three of a kind, queens).
Outcome: UTG wins $294.65. CO wins $2.49.

I mean, if you're going to lose you may as well be totally crushed. Twice. Right?

skylos · #6 04-01-2007, 11:27 AM

Yeah that sucks. I can't win with JJJ either. Those 9/10 where I win are all limped pots and I pick up $1.50 or the board is 4 straight or 4 flush and mabye I'll get lucky and win $2.00 but the 1/10 where I lose are al a buyin or more. Yaah poker.

PokerStars No-Limit Hold'em, $0.25 BB (9 handed) Hand History Converter Tool from FlopTurnRiver.com (Format: 2+2 Forums)

MP1 ($19.35)
MP2 ($4.80)
MP3 ($23.85)
Hero ($27.20)
Button ($20.30)
SB ($6.70)
BB ($39.95)
UTG ($21.05)
UTG+1 ($6.65)

Preflop: Hero is CO with J[img]/images/graemlins/club.gif[/img], J[img]/images/graemlins/diamond.gif[/img].
2 folds, MP1 calls $0.25, 2 folds, Hero calls $0.25, Button calls $0.25, SB completes, BB raises to $1.5, MP1 calls $1.25, Hero calls $1.25, Button folds, SB folds.

Flop: ($5) 5[img]/images/graemlins/diamond.gif[/img], 6[img]/images/graemlins/club.gif[/img], J[img]/images/graemlins/spade.gif[/img] (3 players)
BB bets $3.5, MP1 folds, Hero raises to $8.5, BB raises to $38.45, Hero calls $17.20 (All-In).

Turn: ($69.15) T[img]/images/graemlins/diamond.gif[/img] (2 players, 1 all-in)

River: ($69.15) Q[img]/images/graemlins/spade.gif[/img] (2 players, 1 all-in)

Final Pot: $69.15

Results in white below:
BB has Qh Qc (three of a kind, queens).
Hero has Jc Jd (three of a kind, jacks).
Outcome: BB wins $69.15.

futuredoc85 · #7 04-01-2007, 11:41 AM

any advice on how to flop one of these "sets"?

Albert Moulton · #8 04-01-2007, 12:19 PM

Next you can check AA over KK preflop by site.

That's the one that the conspiracy thorists claim occurs too often at online sites.

kidpokeher · #9 04-02-2007, 11:00 AM

[ QUOTE ]
any advice on how to flop one of these "sets"?

[/ QUOTE ]

Raise big with your small pocket pairs to ensure villain will flop a bigger set.

wildzer0 · #10 04-02-2007, 11:33 AM

Hey,
Kidepokher pointed me towards this thread. I think this is the query you were looking for? Also, for people who like SQL stuff, I'm going to post a really cool baluga theorum application sql thing in a little while in SSNL, so look for that.

SELECT
gp.hole_cards AS Pair,
SUM(
CASE WHEN rtrim(g.flop_1,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_2,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_3,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE 0 END END END ) as Flopped_Set , SUM((
CASE WHEN rtrim(g.flop_1,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_2,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_3,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE 0 END END END )*Won_Hand) as Won_With_Set, SUM((1-
CASE WHEN rtrim(g.flop_1,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_2,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE
CASE WHEN rtrim(g.flop_3,'hdcs')=rtrim(gp.hole_card_1,'hdcs' ) THEN 1 ELSE 0 END END END )*Won_Hand) as Won_Without_Set

FROM game_players AS gp INNER JOIN game AS g ON gp.game_id = g.game_id

WHERE
gp.player_id = (select pref_value from prefs where pref_key = 'RP')
AND gp.pair_hand = 1
AND gp.saw_flop_n = 1
AND g.site_id = 2
and g.date_played >= '2007-01-01'
GROUP BY gp.hole_cards