Two Plus Two Publishing LLC Two Plus Two Publishing LLC
 

Go Back   Two Plus Two Poker Forums > >

Heads Up SNG and Spin and Gos Discussion of heads up Sit & Go and Spin and Go poker games. Sponsored by HUSNG.com, the leading heads up poker video site.

Reply
 
Thread Tools Display Modes
Old 06-19-2012, 10:19 AM   #1
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Script to analyze population tendency on HEM

Hy all!! Sorry in advance for my bad bad English.
Tonight I did some work on HEM to realize a script that can create an alias for all players in our database, except for Hero. It can be very useful to analyze population tendency, but I have to work more on this because it can be very hard for our machine to analyze this huge sample of hands. So, every help his appreciated

In order to be able to do that, first of all open your HEM and create an alias clicking on Options>Player aliases>Add alias. In the name select whatever you want; then add just one player to the alias (except for a hero nicks obv) and make sure you remember his name. For semplicity, take a name with no special characters. Assume at this point you have add the nick 'Villain' to the alias 'Global'.

Now, you have to open your pgAdmin by Start>All programms>Postgresql 9.0> pgAdmin III.
NB:The directory can change based on your actual version of postgresql
Connect to your database double-clicking on PostgreSQL 9.0. Select your database of interest (for me his SNG HU), then click on Tools>Query tools (or something like that, I don't know the exact traduction cause I use an Italian version).

The first thing to do is to extract the id for our 'Global' alias. To do that, insert this code
Code:
SELECT *
FROM aliases a JOIN players p ON a.player_id = p.player_id
WHERE playername = 'Villain';
You have to replace string Villain with the name of the player added to the alias

Then, click 'F5' to start the query. At this point, you have a table in the lower part of the screen. The first column of this table should be 'aliasplayer_id'. Take this number, it will be useful soon. Assume for simplicity this number is 1234.

At this point delete all the code you have insert and digit this new code
Code:
INSERT INTO aliases
SELECT 1234, P1.player_id
FROM players AS P1
WHERE P1.player_id NOT IN
(
	SELECT A2.player_id
	FROM aliases AS A2
);
You have to replace 1234 in the second line with the exact id of the alias extracted with the first code
Again, click 'F5' to start the query.

At this point you have to restart your HEM to add the changes. To make sure all it's ok, click again on Options> Player aliases, select 'Global' and verify that there are enough nicknames associated to the alias.

Now, you're able to use this alias... if your machine is strong enough, obv
tigerjack89 is offline   Reply With Quote
Old 06-19-2012, 10:25 AM   #2
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

To reduce the size of the alias it could be possible to add to it only players that play hu at a certain buy-in level or from a certain period. But now, I still don't know how
tigerjack89 is offline   Reply With Quote
Old 06-19-2012, 10:50 AM   #3
Benjamin the Donk
old hand
 
Benjamin the Donk's Avatar
 
Join Date: Mar 2012
Location: Micros. Don't trust my advice :)
Posts: 1,254
Re: Script to analyze population tendency on HEM

Awesome work. This will come in handy for all the morons that use HEM

/end troll

Dave and I keep meaning to do something like this for PT4, but I don't know **** about SQL and Dave tells me that it doesn't use 'real' SQL or something.

There's some way of extracting all of villain's data and renaming every villain to a common name. Then you can use the data like you would you're own. In fact I would say it's may similar to what you have done, I don't know exactly what the code means. Maybe if Dave comes along he can explain it

But seriously, thanks.
Benjamin the Donk is offline   Reply With Quote
Old 06-19-2012, 11:32 AM   #4
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

Yes, I still haven't had time to test PT4, but it's in my "to do" things
But i think that PT4 also uses table like these and there aren't huge difference between this two databases.
I don't have many experience with postgresql, I usually use mySQL, but the two languages are similar. However, maybe there is someone more expert than me that can optimize the queries or the entire database to increase the speed (I have to wait also 2 minutes for a simple filter on my database from HEM).
So, more than an end point, this is a starting point to do some better works on our databases
I hope this is helpful for someone.
tigerjack89 is offline   Reply With Quote
Old 06-19-2012, 03:19 PM   #5
Quimp
adept
 
Quimp's Avatar
 
Join Date: Sep 2009
Location: Chugging maple syrup with a beaver
Posts: 1,130
Re: Script to analyze population tendency on HEM

Cool stuff man! I think you include other aliases as well as Hero when you insert the players.

First, let's create the alias manually instead of using HEM:
Code:
INSERT INTO players (site_id, playername, cashhands, tourneyhands, playertype_id)
VALUES (-1, 'Global alias', 0, 0, 0)
RETURNING player_id AS aliasplayer_id;
This will return the aliasplayer_id that was just created.


Second query:
Code:
INSERT INTO aliases (aliasplayer_id, player_id)
SELECT 1234, P1.player_id
FROM players AS P1
WHERE P1.player_id NOT IN
(
	SELECT A2.player_id
	FROM aliases AS A2
)
AND P1.site_id <> -1 AND P1.playername <> 'Screenname';
Replacing 1234 with the value from the first query and Screenname with your SN. If you have several screen names then just add another condition; it's not perfect but probably good enough, unless someone stole your SN and you played them.

We can limit the number of players in several ways:

1. Using a LIMIT 1000 for example in the INSERT INTO command.

2. Importing players who have played tournaments by adding... AND P1.tourneyhands > 0

3. By filtering by site... AND P1.site_id = 2 (if 2 is PokerStars on your computer)

4. By importing only recent players

5. Filtering by buy-in as you said

Would be cool if we could make it a function or use variables. My Google searches weren't of much help. I'm pretty much in the same boat as you, I've never worked with Postgres.


Edit: wow, I have a 14% ROI this month, Population has -23%. Rake must be brutal when you suck!

Last edited by Quimp; 06-19-2012 at 03:33 PM.
Quimp is offline   Reply With Quote
Old 06-19-2012, 03:43 PM   #6
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

Nice Quimp, the query worked for me that I have a global alias for all my nicknames. But if you don't have one yes, it includes even your nickname in the alias, so your query is way better!! Tx a lot.

For the first query, I didn't know how to create a global alias by postgresql. I mean, when I create one, it didn't show the name in my HEM. Now the trick is revealed

Anyway, it shouldn't be too difficult to make a function; tomorrow I work on this. My main question are:
1) With mySQL, when you invoke the INSERT, you can also choice an option on ON DUPLICATED KEY; in postgresql, it's impossible. So, how it respond when we try to insert the same player in the global alias?
2) It's possible to select only players that played hu? A bias selection it's way too efficient, but I think there's no code for HU format on postgresql table.
3) There's a way to optimize the alias? I mean, after the two queries, I made a VACUUM and a REINDEX to the database, but it's still too slow.
I think that the problem is that every time it has to select the data of all players; it could be more more efficient to create a single player with all the datas rather than an alias, but I don't know how and I think it's too difficult.

EDIT: yes if someone has your nick it could be a problem. So the only way is to include an alias as 'HERO' and insert into that all hero nicks, then proceed with my query.
tigerjack89 is offline   Reply With Quote
Old 06-19-2012, 03:44 PM   #7
Quimp
adept
 
Quimp's Avatar
 
Join Date: Sep 2009
Location: Chugging maple syrup with a beaver
Posts: 1,130
Re: Script to analyze population tendency on HEM

This is for HU <= 15$, mostly reg speeds.



There's a cool feature in HEM that lets you export the stats to a CSV file, meaning we could merge stats and weight them based on sample size.
Quimp is offline   Reply With Quote
Old 06-19-2012, 03:55 PM   #8
Quimp
adept
 
Quimp's Avatar
 
Join Date: Sep 2009
Location: Chugging maple syrup with a beaver
Posts: 1,130
Re: Script to analyze population tendency on HEM

1. You don't need to worry about it because of the WHERE clause...

Quote:
WHERE P1.player_id NOT IN
(
SELECT A2.player_id
FROM aliases AS A2
)
So if you run the script twice, the second time 0 rows will be affected. However, I just realized there's something missing here. We should add a WHERE A2.aliasplayer_id = 1234 in the embedded SELECT to make sure we don't ignore players who may be in another alias (ex. a fish or reg list).

2. It's not possible to select only players that played HUSNGs because the alias works on a player basis. In HEM, you can select the number of players before running a report (you can also select a buy in range and game speed).

3. It would probably be faster yes. Probably complex and annoying to work through and maintain though. I think the best way to make it faster is to limit the number of players included. Do you have an idea how to randomize sampling?

One way I have in mind is to create a temporary table with an extra column with a random value ranging from 1 - 10, then select only 1's (10%) or 1-2's (20%), etc.

Quote:
For the first query, I didn't know how to create a global alias by postgresql. I mean, when I create one, it didn't show the name in my HEM.
HEM loads the aliases when the program launches. Simply close it and reopen it to see the new alias.

Last edited by Quimp; 06-19-2012 at 04:04 PM.
Quimp is offline   Reply With Quote
Old 06-19-2012, 06:55 PM   #9
_dave_
_Pooh_Bah_
 
Join Date: Feb 2005
Location: UK (or what remains of it)
Posts: 13,121
Re: Script to analyze population tendency on HEM

I approve of this thread nice work everyone!
_dave_ is offline   Reply With Quote
Old 06-19-2012, 07:31 PM   #10
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

[QUOTE=Quimp;33360848]1. My solution is this and i think it's a good compromise.
a. create a new alias, naming it 'Hero' and adding into it all hero nicknames.
b. create a new alias, naming it 'All'
c. adding all the players to 'All' except those in the alias 'Hero' and those yet added
It isn't difficult to do to something like that, except for the italic part, on which i have to think about.

2. Yes, I know, but at the same time if you make the selection before the insert the result is less tuple to insert and more efficient database.

3. I don't know if a randomization can be helpul. I mean, I have 11k players in my db and in certain spots I have a sample of only 400-500 hands.

Quote:
HEM loads the aliases when the program launches. Simply close it and reopen it to see the new alias.
Yes, i wanted to say that before your post I didn't know how to add a new alias by postgresql and for this reason i suggested to create this by HEM
tigerjack89 is offline   Reply With Quote
Old 06-19-2012, 07:49 PM   #11
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

Assuming that:
a. we create an alias for hero nicknames naming it 'Hero'. It's simple to do that by HEM.
b. we create a new alias player with Quimp's query and that its id is 11084
Code:
INSERT INTO players (site_id, playername, cashhands, tourneyhands, playertype_id)
VALUES (-1, 'Global alias', 0, 0, 0)
RETURNING player_id AS aliasplayer_id;
c. the query to insert all the players except hero and those yet inserted is this
Code:
INSERT INTO aliases 
SELECT 11084, p1.player_id
FROM players p1
WHERE p1.player_id NOT IN
(
	SELECT a1.player_id
	FROM aliases a1
	WHERE a1.aliasplayer_id = 11084
	-- to exclude players yet inserted
)
AND p1.player_id NOT IN
(	
	SELECT a2.player_id
	FROM players p2 JOIN aliases a2 ON p2.player_id = a2.aliasplayer_id
	WHERE p2.site_id = -1 and p2.playername = 'Hero'
	-- to exclude hero nicknames from the insert	
)
AND p1.site_id <> -1
-- to exclude other aliases
;

Last edited by tigerjack89; 06-19-2012 at 08:04 PM.
tigerjack89 is offline   Reply With Quote
Old 06-19-2012, 09:59 PM   #12
genher
veteran
 
genher's Avatar
 
Join Date: May 2007
Location: Melbourne Australia
Posts: 2,931
Re: Script to analyze population tendency on HEM

brilliant!
genher is offline   Reply With Quote
Old 06-20-2012, 07:18 AM   #13
Benjamin the Donk
old hand
 
Benjamin the Donk's Avatar
 
Join Date: Mar 2012
Location: Micros. Don't trust my advice :)
Posts: 1,254
Re: Script to analyze population tendency on HEM

NERDS!!!

Quote:
Originally Posted by tigerjack89 View Post
(I have to wait also 2 minutes for a simple filter on my database from HEM).
I just bought a shiny new SSD so maybe that things like this will go quickly for me.

Will this sort of code work on the PT4 DB in PostgreSQL?
Benjamin the Donk is offline   Reply With Quote
Old 06-20-2012, 07:25 AM   #14
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

Quote:
Originally Posted by Benjamin the Donk View Post
I just bought a shiny new SSD so maybe that things like this will go quickly for me.

Will this sort of code work on the PT4 DB in PostgreSQL?
I don't know if the tables used by PT4 are the same, and surely they have different names.
Also, I don't know if there's the possibility to make an alias in PT4.
In these days I'll download it and try to make something similar.
Ah and yes, an SSD is surely better and faster than my HDD
tigerjack89 is offline   Reply With Quote
Old 06-20-2012, 01:51 PM   #15
genher
veteran
 
genher's Avatar
 
Join Date: May 2007
Location: Melbourne Australia
Posts: 2,931
Re: Script to analyze population tendency on HEM

Quote:
Originally Posted by tigerjack89 View Post
The first thing to do is to extract the id for our 'Global' alias. To do that, insert this code
Where do you insert this code? in the SQL editor? and if yes what do you do with the stuffs already in there? delete it? sorry noob w postgreSQL
genher is offline   Reply With Quote
Old 06-20-2012, 02:12 PM   #16
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

Quote:
Originally Posted by genher View Post
Where do you insert this code? in the SQL editor? and if yes what do you do with the stuffs already in there? delete it? sorry noob w postgreSQL
Hey genher, what's up?
Quote:
Now, you have to open your pgAdmin by Start>All programms>Postgresql 9.0> pgAdmin III.
NB:The directory can change based on your actual version of postgresql
Connect to your database double-clicking on PostgreSQL 9.0

Select your database of interest (for me his SNG HU), then click on Tools>Query tools (or something like that, I don't know the exact traduction cause I use an Italian version).
On the new editor, insert the code and just press F5 to run it
Anyway, look at post #11, it's way more accurate than my first post
Let me know if you have some problem.
tigerjack89 is offline   Reply With Quote
Old 06-20-2012, 02:24 PM   #17
genher
veteran
 
genher's Avatar
 
Join Date: May 2007
Location: Melbourne Australia
Posts: 2,931
Re: Script to analyze population tendency on HEM

Quote:
INSERT INTO players (site_id, playername, cashhands, tourneyhands, playertype_id)
VALUES (-1, 'Global alias', 0, 0, 0)
RETURNING player_id AS aliasplayer_id;
am I right in assuming that the bolded part of the code are the columns of the player table?

if so, in HEM2 the columns are slightly different : player_id, playername, pokersire_id, cashhands, tourneyhands, optimizationstatus, icon

so I thought of adapting the code to
Quote:
INSERT INTO players (player_id, playername, pokersire_id, cashhands, tourneyhands, optimizationstatus, icon)
VALUES (0, 'Global alias', 0, 0, 0,0,0)
RETURNING player_id AS aliasplayer_id;
but obviously the player_id returns 0
or in this case can I just insert any player id numer ?

Help anyone?
genher is offline   Reply With Quote
Old 06-20-2012, 02:33 PM   #18
Quimp
adept
 
Quimp's Avatar
 
Join Date: Sep 2009
Location: Chugging maple syrup with a beaver
Posts: 1,130
Re: Script to analyze population tendency on HEM

Don't specify player_id, it will be automatically increamented and returned. Also, pokersiRe_id should be (?) pokersite_id? If so, make its value -1 to create an alias.

You'll have to look at the table's structure to see if optimizationstatus and icon are integers.
Quimp is offline   Reply With Quote
Old 06-20-2012, 02:36 PM   #19
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

Yes, these are the columns of the table but no, you don't have to insert all the right values. I mean, player_id is derived automatically, so you don't have to insert it.
Another thing to do is to check if HEM 2 has the same way to memoryze aliases. In HEM1 it differenciates normal players from aliases by the site_id column: the value -1 indicates that the player is an alias.

So, first of all to check this create an alias with HEM2 and naming it 'proof'. Then open pgadmin3 and run the query
Code:
SELECT *
FROM players
WHERE playername = 'proof'
and checks that in the pokersite_id there is the -1 value. If it's true, run the query
Code:
INSERT INTO players (playername, pokersite_id, cashhands, tourneyhands)
VALUES ('Global alias', -1, 0, 0)
RETURNING player_id AS aliasplayer_id;
Then run the point c in post #11, replacing 1234 with the number you have obtained in the previous query.
tigerjack89 is offline   Reply With Quote
Old 06-20-2012, 02:38 PM   #20
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

Quote:
Originally Posted by Quimp View Post
You'll have to look at the table's structure to see if optimizationstatus and icon are integers.
... and if they have to be not null, but i don't think.

I just downloaded PT4. There's a way to create an alias with it?
tigerjack89 is offline   Reply With Quote
Old 06-20-2012, 02:38 PM   #21
_dave_
_Pooh_Bah_
 
Join Date: Feb 2005
Location: UK (or what remains of it)
Posts: 13,121
Re: Script to analyze population tendency on HEM

hem and PT databases are really quite different!

but yes, those are the columns of the named table "players", it's INSERT INTO tablename (columnname_A, columnname_B) VALUES (what_to_put_in_col_A, what_to_put_in_col_B);

The easiest way to figure out what are the relevant tables/columns is to enable full logging in postgresql (it's possibly on already), do whatever it is you're interested in (e.g. create a simple alias), open up the postgresql log file and see what lines of SQL got executed.

Or in PGAdmin just expand the tree on the left schema / public / tables iirc and examine them see if you can make sense of how it's storing the information.
_dave_ is offline   Reply With Quote
Old 06-20-2012, 03:07 PM   #22
genher
veteran
 
genher's Avatar
 
Join Date: May 2007
Location: Melbourne Australia
Posts: 2,931
Re: Script to analyze population tendency on HEM

ok

Quote:
SELECT *
FROM players
WHERE playername = 'proof'
This works fine

so I adapted your code to
Quote:
INSERT INTO players (playername, pokersite_id, cashhands, tourneyhands, optimizationstatus, icon)
VALUES ('Global alias', -1, 0, 0, 0, 0)
RETURNING player_id AS aliasplayer_id;
and it does work and return a player_id number. So so far so good

BUT when I run the next query (c in post 11) I get ERROR: column p1.site_id does not exist!? what's p1.site_id ??
genher is offline   Reply With Quote
Old 06-20-2012, 03:10 PM   #23
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

Quote:
Originally Posted by _dave_ View Post
The easiest way to figure out what are the relevant tables/columns is to enable full logging in postgresql (it's possibly on already), do whatever it is you're interested in (e.g. create a simple alias), open up the postgresql log file and see what lines of SQL got executed.
How it's possible to enable full logging? Where is the log file?

Quote:
Or in PGAdmin just expand the tree on the left schema / public / tables iirc and examine them see if you can make sense of how it's storing the information.
Yes, the main columns are there. But I don't know yet if there's a way to create an alias on PT4 (it's still importing hands from my previous database :S)
tigerjack89 is offline   Reply With Quote
Old 06-20-2012, 03:13 PM   #24
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

Quote:
Originally Posted by genher View Post
ok

BUT when I run the next query (c in post 11) I get ERROR: column p1.site_id does not exist!? what's p1.site_id ??
p1 is a "variable" equal to the table players. So, it's p1.pokersite_id

Can you post also the aliases columns?
SELECT * FROM aliases
tigerjack89 is offline   Reply With Quote
Old 06-20-2012, 03:41 PM   #25
tigerjack89
grinder
 
tigerjack89's Avatar
 
Join Date: Jun 2010
Posts: 460
Re: Script to analyze population tendency on HEM

Well, I think in PT4 there's a way to add only players who have played heads up. But still i don't know if there's a way to create an alias cause I can't be able to use the program until he imports all hands.
This is the code to show only hu players
Code:
SELECT DISTINCT id_player
FROM tourney_cache T
WHERE T.cnt_players = 2;
tigerjack89 is offline   Reply With Quote

Reply
      

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Forum Jump


All times are GMT -4. The time now is 07:33 AM.


Powered by vBulletin®
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2008-2020, Two Plus Two Interactive