Обсуждение: RANDOM function?
can someone point me to the documentation of RANDOM() function.... and can i insert RANDOM POSTIVE WHOLE NUMBERS...? create table table_name (key int4); insert into table_name(key) values(random()); <-- this gives me a value of 1 and 0 only.. ---------------------------------------------------------------------- The information contained in this message (including any attachments) is confidential and intended solely for the attention and use of the named addressee(s). It must not be copied, distributed nor disclosed to any person. If you are not the intended recipient, please delete it from your system and notify sender immediately. Any disclosure, copying or distribution thereof or any action taken or omitted to be taken in reliance thereon is prohibited and may be unlawful. ----------------------------------------------------------------------
On Tue, 31 Jul 2001, macky wrote: > can someone point me to the documentation of RANDOM() function.... > > and can i insert RANDOM POSTIVE WHOLE NUMBERS...? > > create table table_name (key int4); > > insert into table_name(key) values(random()); <-- this gives me a value of > 1 and 0 only.. > random produces a float between 0 and 1. Just multiple it with the interval you want and you will get the random number you want. eg. to get a random number between 0 and 100 -> 100 * random(). between 32 and 40 -> (8 * random()) + 32. - Einar Karttunen
if ill be using big range of numbers sa 10000 woundd the be any possibility to have multiple items... meaning lets say 1234 will show up more than once? ---------------------------------------------------------------------- The information contained in this message (including any attachments) is confidential and intended solely for the attention and use of the named addressee(s). It must not be copied, distributed nor disclosed to any person. If you are not the intended recipient, please delete it from your system and notify sender immediately. Any disclosure, copying or distribution thereof or any action taken or omitted to be taken in reliance thereon is prohibited and may be unlawful. ---------------------------------------------------------------------- ----- Original Message ----- From: "Einar Karttunen" <ekarttun@cs.Helsinki.FI> To: "macky" <macky@edsamail.com> Cc: <pgsql-novice@postgresql.org> Sent: Tuesday, July 31, 2001 1:50 PM Subject: Re: RANDOM function? > On Tue, 31 Jul 2001, macky wrote: > > > can someone point me to the documentation of RANDOM() function.... > > > > and can i insert RANDOM POSTIVE WHOLE NUMBERS...? > > > > create table table_name (key int4); > > > > insert into table_name(key) values(random()); <-- this gives me a value of > > 1 and 0 only.. > > > random produces a float between 0 and 1. Just multiple it with the > interval you want and you will get the random number you want. > > eg. to get a random number between 0 and 100 -> 100 * random(). > between 32 and 40 -> (8 * random()) + 32. > > - Einar Karttunen > > >
On Tue, 31 Jul 2001, macky wrote: > if ill be using big range of numbers sa 10000 woundd the be any possibility > to have multiple items... meaning lets say 1234 will show up more than once? Yes it is possible. The probablity is 1/10000. Just multiply it with a large number. I suggest 2147483647 if you want to get all the non-negative numbers possible for an int4 value. - Einar Karttunen
On Tue, 31 Jul 2001, macky wrote: > if ill be using big range of numbers sa 10000 woundd the be any possibility > to have multiple items... meaning lets say 1234 will show up more than once? It sounds like you are trying to draw a number from a finite supply, WITHOUT REPLACEMENT. I don't know how to attach an external C (perl, ...?) function to PostgreSQL, but I think you'll end up doing this. Some of what I am describing can be done "virtually". You will need to set up an array (stack, list) containing all the possible integers you want to draw from. Say 1, 2, 3, .... 100000. Note that there are 100000 elements in the stack. Now, draw a random (0 - 1) floating point number. If the 0-1 deviate lies between (i-1)/N and i/N (where N is how many integers are in our stack, and i is an index), we will withdraw the i'th value from the stack as our random number (leaving us with N-1 values in the stack). We continuous to withdraw numbers in this manner until we have either withdrawn enough numbers, or we have exhausted the stack. Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca 780/993-1274 (cell)
On Tue, 31 Jul 2001 ghaverla@freenet.edmonton.ab.ca wrote: > > On Tue, 31 Jul 2001, macky wrote: > > > if ill be using big range of numbers sa 10000 woundd the be any possibility > > to have multiple items... meaning lets say 1234 will show up more than once? > > It sounds like you are trying to draw a number from a finite > supply, WITHOUT REPLACEMENT. I don't know how to attach > an external C (perl, ...?) function to PostgreSQL, but > I think you'll end up doing this. > > Some of what I am describing can be done "virtually". > You will need to set up an array (stack, list) containing > all the possible integers you want to draw from. Say > 1, 2, 3, .... 100000. Note that there are 100000 > elements in the stack. Now, draw a random (0 - 1) > floating point number. If the 0-1 deviate lies between > (i-1)/N and i/N (where N is how many integers are in > our stack, and i is an index), we will withdraw the > i'th value from the stack as our random number (leaving > us with N-1 values in the stack). We continuous to > withdraw numbers in this manner until we have either > withdrawn enough numbers, or we have exhausted the stack. > That sounds very hard to implement effeciently. Could you send me some high level pseudocode? I think that a with 31 bit random integers the probablity to get the same value is very very small. If it is too big I can always change to bigints. - Einar Karttunen
On Tue, 31 Jul 2001, Einar Karttunen wrote: > On Tue, 31 Jul 2001 ghaverla@freenet.edmonton.ab.ca wrote: > > On Tue, 31 Jul 2001, macky wrote: > > > > > if ill be using big range of numbers sa 10000 woundd the be any possibility > > > to have multiple items... meaning lets say 1234 will show up more than once? > > > > It sounds like you are trying to draw a number from a finite > > supply, WITHOUT REPLACEMENT. > That sounds very hard to implement effeciently. Could you send me some > high level pseudocode? I think that a with 31 bit random integers the > probablity to get the same value is very very small. If it is too big > I can always change to bigints. I'm sure you can do a search for a RNG without replacement as easily as I can. Yes, a person can always keep track of what numbers have already been picked and reject duplicate picks. If the number of picks you are making is much smaller than the space you are picking from, this would work fine. Especially using a smart (not linear) search for the possible duplicate. If the number of picks is getting close to the size of the stack, rejection is going to be a big loser. Any program that draws cards from a deck is going to use this type of without replacement method, as some card games deal the entire deck. Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca 780/993-1274 (cell)
I think that what you want to do is create a trigger (or rule?) that pulls up your random number and then checks to see if it exists already. I haven't used triggers yet, but it should not be too hard. Also, what happens when you declare a column unique, and a function like random() tries to give it a non-unique number? At 03:58 PM 7/31/01 +0300, you wrote: >On Tue, 31 Jul 2001 ghaverla@freenet.edmonton.ab.ca wrote: > > > > > On Tue, 31 Jul 2001, macky wrote: > > > > > if ill be using big range of numbers sa 10000 woundd the be any > possibility > > > to have multiple items... meaning lets say 1234 will show up more > than once? > > > > It sounds like you are trying to draw a number from a finite > > supply, WITHOUT REPLACEMENT. I don't know how to attach > > an external C (perl, ...?) function to PostgreSQL, but > > I think you'll end up doing this. > > > > Some of what I am describing can be done "virtually". > > You will need to set up an array (stack, list) containing > > all the possible integers you want to draw from. Say > > 1, 2, 3, .... 100000. Note that there are 100000 > > elements in the stack. Now, draw a random (0 - 1) > > floating point number. If the 0-1 deviate lies between > > (i-1)/N and i/N (where N is how many integers are in > > our stack, and i is an index), we will withdraw the > > i'th value from the stack as our random number (leaving > > us with N-1 values in the stack). We continuous to > > withdraw numbers in this manner until we have either > > withdrawn enough numbers, or we have exhausted the stack. > > >That sounds very hard to implement effeciently. Could you send me some >high level pseudocode? I think that a with 31 bit random integers the >probablity to get the same value is very very small. If it is too big >I can always change to bigints. > >- Einar Karttunen > > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Tue, 31 Jul 2001, Neal Lindsay wrote:
> I think that what you want to do is create a trigger (or rule?) that pulls
> up your random number and then checks to see if it exists already. I
> haven't used triggers yet, but it should not be too hard. Also, what
> happens when you declare a column unique, and a function like random()
> tries to give it a non-unique number?
>
This can be accomplished very easily.
CREATE TABLE tbl (
col integer default (random()*2147483647) unique,
col2 text
);
INSERT INTO tbl (col2) VALUES ('the value of col2');
the insert will fail if the number is not unique then just perform it
anew.
- Einar Karttunen
On Tue, 31 Jul 2001, Einar Karttunen wrote:
> On Tue, 31 Jul 2001, Neal Lindsay wrote:
>
> > I think that what you want to do is create a trigger (or rule?) that pulls
> > up your random number and then checks to see if it exists already. I
> > haven't used triggers yet, but it should not be too hard. Also, what
> > happens when you declare a column unique, and a function like random()
> > tries to give it a non-unique number?
> >
> This can be accomplished very easily.
>
> CREATE TABLE tbl (
> col integer default (random()*2147483647) unique,
> col2 text
> );
>
> INSERT INTO tbl (col2) VALUES ('the value of col2');
>
> the insert will fail if the number is not unique then just perform it
> anew.
The only thing I would add, is your multiplier (2147...) must
always be big, with respect to how many times you want to draw
this random number without replacement. If you were wanting
to draw 2000000000 random numbers, this method would bog
down in rejections (insert failures) towards the end.
Gord
Matter Realisations http://www.materialisations.com/
Gordon Haverland, B.Sc. M.Eng. President
101 9504 182 St. NW Edmonton, AB, CA T5T 3A7
780/481-8019 ghaverla @ freenet.edmonton.ab.ca
780/993-1274 (cell)
On Tue, 31 Jul 2001, Einar Karttunen wrote: > That sounds very hard to implement effeciently. Could you send me some > high level pseudocode? I think that a with 31 bit random integers the > probablity to get the same value is very very small. If it is too big > I can always change to bigints. I got 516 hits at Google.com on random number generator "without replacement" some hits in C, some in C++, .... One employs the Gnu Scientific Library, Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca 780/993-1274 (cell)
On Tue, 31 Jul 2001 ghaverla@freenet.edmonton.ab.ca wrote: > > I'm sure you can do a search for a RNG without replacement as > easily as I can. Yes, a person can always keep track of what > numbers have already been picked and reject duplicate picks. > If the number of picks you are making is much smaller than the > space you are picking from, this would work fine. Especially > using a smart (not linear) search for the possible duplicate. > If the number of picks is getting close to the size of the > stack, rejection is going to be a big loser. Any program > that draws cards from a deck is going to use this type of > without replacement method, as some card games deal the entire > deck. I don't think it is wise to do it externally if you can let the db do it using unique columns. The method you suggested just sounded new to me, I would have preferred a hash table or a tree structure. - Einar Karttunen
On Tue, 31 Jul 2001 ghaverla@freenet.edmonton.ab.ca wrote: > > The only thing I would add, is your multiplier (2147...) must > always be big, with respect to how many times you want to draw > this random number without replacement. If you were wanting > to draw 2000000000 random numbers, this method would bog > down in rejections (insert failures) towards the end. > You cannot go higher if you use an int column because it is a signed 32 bit integer. If you need more use bigints. They will suffice. If not you are screwed anyways because oids don't suffice. - Einar Karttunen