Gaussian random distribution in Postgresql

Question

I have a table let say 250 URL:

create table url (
  id serial,
  url varchar(64)
)

These URLs correspond to each website. Each of the sites has a different popularity. Let's say that the most popular is id=125(a center centered on the Gaussian), the least popular are those that have id=1or id=250.

I want to populate the “log” table as follows with the URL value among those listed in the “url” table, but given that different URLs may appear more often (for example, a URL whose id 125 will be the most popular).

create table log (
  id serial,
  url_id integer
)

I want to avoid use random()as it is homogeneous and not very "real".

How can this be achieved with Postgresql?

Answer 1

12 [0, 1) , [0, 12]. , , / .

select
    random() + 
    random() + 
    random() +
    random() + 
    random() + 
    random() +
    random() + 
    random() + 
    random() +
    random() + 
    random() + 
    random();

http://books.google.com/books?id=EKA-yeX2GVgC&pg=PA185&lpg=PA185&dq=%22sum+of+12+uniform+random+variables%22&source=bl&ots=YfwwE0fBB3&sig=HX9J9Oe6x316kVL8uamDU_GOsn4&hl=en&sa=X&ei=bJLZUur1GozaqwGHm4DQDQ&ved=0CEUQ6AEwAw#v=onepage&q=%22sum%20of%2012%20uniform%20random%20variables%22&f=false

Answer 2

. , :

PostgreSQL 8.4 , tablefunc (http://www.postgresql.org/docs/current/static/tablefunc.html).

normal_rand(n, mean, stddev) n ( , FROM). , n 1, , , .

nb10, 10 , 10 ( = 0, stddev = 1)

SELECT normal_rand(1, 0, 1) FROM nb10;

SELECT * from normal_rand(10, 0, 1);

, ... :-)

, - :

SELECT floor(random_rand(1, 0, 1) * 250 + 125);

, [0, 249]. , :

• , , , [0, 249],
• , , [0, 249],

• , [0, 250[, , , . , ( /+/ , -x y x PostgreSQL, : p):

  SELECT ((floor(normal_rand(1,0,1)*250 + 125)::int % 250) + 250) % 250 as v;
  

Answer 3

, , rand() , .

, , , . - http://www.perlmonks.org/?node_id=26889 ( Perl, , , pl/pgsql SQL).

CREATE OR REPLACE FUNCTION gaussian_rand() RETURNS numeric LANGUAGE PLPERL VOLATILE AS
$$
    my ($u1, $u2);  # uniformly distributed random numbers
    my $w;          # variance, then a weight
    my ($g1, $g2);  # gaussian-distributed numbers

    do {
        $u1 = 2 * rand() - 1;
        $u2 = 2 * rand() - 1;
        $w = $u1*$u1 + $u2*$u2;
    } while ( $w >= 1 );

    $w = sqrt( (-2 * log($w))  / $w );
    $g2 = $u1 * $w;
    $g1 = $u2 * $w;
    # return both if wanted, else just one
    return $g1;

$$;

Answer 4

tablefunc . , :

SELECT normal_rand(1, 0, 1); -- generates 1 single value with mean 0 and a standard deviation of 1

, :

CREATE EXTENSION "tablefunc";

.