Re: Force randomize within a set of numbers
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 4 Dec 2009 06:47:09 -0800 (PST)
Message-ID: <a469fdef-428c-4dfa-b836-dca406787fd9_at_g26g2000yqe.googlegroups.com>
On Dec 4, 2:43 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Dec 4, 1:01 am, CrazyKarma <ska..._at_gmail.com> wrote:
>
> > Here is the last one for this week..I guess :)
>
> > Is there way to force the randomize within a set of number in Oracle?
> > Say I have a set of integers ( 2,8,6,5)
> > and I want to force randomize function to randomly pick within that
> > set only.
>
> > I know about the DBMS_RANDOM package, sample and seed clause but none
> > help what I need to do here.
>
> > any thoughts?
>
> > CK
>
> Straightforward way: just generate random numbers between 0 and 1,
> divide the 0..1 range into 4 subranges and then pick a number from
> your list depending on which subrange your generated random value
> belongs, like this:
>
> with rnd as (select dbms_random.value val from dual)
> select
> case
> when val < 0.25 then 2
> when val >= 0.25 and val < 0.5 then 8
> when val >= 0.5 and val < 0.75 then 6
> when val >= 0.75 then 5
> end x
> from rnd
>
> (note that this query does not work correctly in 9.2.0.8 for some
> reason - returns wrong results. In 10.2.0.4 it returns expected
> results.) Obviously, you can partition 0..1 range into as many
> subranges as there are numbers in your list and apply the same
> technique. A function that will do this automatically given an array
> of possible return values is not too hard to write.
>
> Regards,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com
Date: Fri, 4 Dec 2009 06:47:09 -0800 (PST)
Message-ID: <a469fdef-428c-4dfa-b836-dca406787fd9_at_g26g2000yqe.googlegroups.com>
On Dec 4, 2:43 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Dec 4, 1:01 am, CrazyKarma <ska..._at_gmail.com> wrote:
>
> > Here is the last one for this week..I guess :)
>
> > Is there way to force the randomize within a set of number in Oracle?
> > Say I have a set of integers ( 2,8,6,5)
> > and I want to force randomize function to randomly pick within that
> > set only.
>
> > I know about the DBMS_RANDOM package, sample and seed clause but none
> > help what I need to do here.
>
> > any thoughts?
>
> > CK
>
> Straightforward way: just generate random numbers between 0 and 1,
> divide the 0..1 range into 4 subranges and then pick a number from
> your list depending on which subrange your generated random value
> belongs, like this:
>
> with rnd as (select dbms_random.value val from dual)
> select
> case
> when val < 0.25 then 2
> when val >= 0.25 and val < 0.5 then 8
> when val >= 0.5 and val < 0.75 then 6
> when val >= 0.75 then 5
> end x
> from rnd
>
> (note that this query does not work correctly in 9.2.0.8 for some
> reason - returns wrong results. In 10.2.0.4 it returns expected
> results.) Obviously, you can partition 0..1 range into as many
> subranges as there are numbers in your list and apply the same
> technique. A function that will do this automatically given an array
> of possible return values is not too hard to write.
>
> Regards,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com
CK, instead of using a fixed range as Vladimir had in his example I was thinking I might use a MOD division of the random number returned from dbms_randon to generate the index key into my table of values. This would make the code flexible for variable length lists. If you have a fixed number of entries then Vlad's solution is simple and easy to understand. If your number of variables varies then I think this would fit the bill.
HTH -- Mark D Powell -- Received on Fri Dec 04 2009 - 08:47:09 CST