Sampling from a Postgres database

One way could be to first select only the unique ID's, sample this and then
select only the relevant records:

strQuery = "SELECT ID from tblFoo;"
IDs <- sqlQuery(channel, strQuery)
sample.IDs <- sample(IDs,10)
strQuery = paste("SELECT ID from tblFoo WHRE ID IN(", sample.IDs, ");")
IDs <- sqlQuery(channel, strQuery)

Bart

Hi Everybody

Is there a way in which one can use the RPostgreSQL package to take a
sample
from a table in Postgres database without having to read the whole table
into R

regards
Christiaan

	[[alternative HTML version deleted]]

______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

View this message in context: http://n4.nabble.com/Sampling-from-a-Postgres-database-tp1014506p1014638.html
Sent from the R help mailing list archive at Nabble.com.

One way could be to first select only the unique ID's, sample this and then
select only the relevant records:

strQuery = "SELECT ID from tblFoo;"
IDs <- sqlQuery(channel, strQuery)
sample.IDs <- sample(IDs,10)
strQuery = paste("SELECT ID from tblFoo WHRE ID IN(", sample.IDs, ");")
IDs <- sqlQuery(channel, strQuery)

Better is to use the built-in random() function in Postgres:

#select count(*) from visits;
  count
---------
 4846604
(1 row)

# select count(*) from visits where random() < 0.005;
 count
-------
 24391
(1 row)

HTH,

Joe

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 899 bytes
Desc: OpenPGP digital signature
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20100115/10f492cc/attachment.bin>