[R] Sampling from a database

[Andrew Perrin]

The standard way to do this under PostgreSQL is:

SELECT * FROM tablename ORDER BY random() LIMIT n;

where n is the number of records you want returned.

----------------------------------------------------------------------
Andrew J Perrin - andrew_perrin at unc.edu - http://www.unc.edu/~aperrin
 Assistant Professor of Sociology, U of North Carolina, Chapel Hill
      269 Hamilton Hall, CB#3210, Chapel Hill, NC 27599-3210 USA


On Fri, 1 Feb 2002, F. Tusell wrote:

> I use RODBC  and RpgSQL quite a lot to access  files stored in another
> machine under PostgreSQL. Since I am  now using files which do not fit
> into R's  memory, I would  like to take  random samples. What  I would
> like is to issue a query such as
> 
>    SELECT * FROM file WHERE runif > 0.9
> 
> with "runif" being a uniformly distributed random number, generated on
> the fly; but I  cannot find any way to do that  in PostgreSQL. So far,
> my only idea is to create  a new table with two fields, "IDnumber" and
> "Random",  fill "IDnumber"  with the  similarly named  field  from the
> table to sample, populate "Random"  with random numbers generated in R
> and then issue a query for  records where "IDnumber" of the two tables
> match AND Random > 0.9.
> 
> Seems  to me  clumsy, and  surely there  has to  be a  better  way and
> someone knowing SQL can suggest it.
> 
> 
> ft.
> -- 
> Fernando TUSELL                                e-mail:
> Departamento de Econometría y Estadística           etptupaf at bs.ehu.es 
> Facultad de CC.EE. y Empresariales             Tel:   (+34)94.601.3733
> Avenida Lendakari Aguirre, 83                  Fax:   (+34)94.601.3754
> E-48015 BILBAO  (Spain)                        Secr:  (+34)94.601.3740
> PGP: finger etptupaf at bsdx01.bs.ehu.es          http://etdx01.bs.ehu.es
> ----------------------------------------------------------------------
> 
> 
> -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
> r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
> Send "info", "help", or "[un]subscribe"
> (in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
> _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
> 

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._