CODEWITHSUNDEEP
sqloracle-database
learner
learner

Reputation: 1023

Generate random value from a list of values with even distribution in Oracle

I am trying to generate a random value from a list of valid values [1,2,3,4,5,9]. If I run my function 10 times, there are 2 values that never appear. I need every value in the list to be present in my sample. How can I ensure this ? An equal distribution would be good, but at least few rows of every value.

select random_code
        from (
                with temp_code_table as (
                     select '1' as random_code from dual union
                     select '2' as random_code from dual union
                     select '3' as random_code from dual union
                     select '4' as random_code from dual union
                     select '5' as random_code from dual union
                     select '9' as random_code from dual)
                     select random_code from temp_code_table order by dbms_random.value)
       where rownum = 1;

I am running the above SQL inside a function, which checks that the random code generated is not the same as the original value.

Edit: Not sure the answer in the below post will help me achieve what I want.

Generating Random Value using CASE

Any advice on how to achieve this ?

Upvotes: 0

Views: 909

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18665

"A random number" and "at least one of each" cannot be combined. The definition of random is that you down't know what you'll get... Roll a dice 10 times. Are you sure you'll have thrown all sides at least once ? No. So to solve your problem, you can select one of each first - that way you have those already and then fill up the rest with randomly selected numbers from the set.

with temp_code_table (nr) as 
(
  SELECT '1' FROM dual UNION
  SELECT '2' FROM dual UNION
  SELECT '3' FROM dual UNION
  SELECT '4' FROM dual UNION
  SELECT '5' FROM dual UNION
  SELECT '9' FROM dual
)
-- now select one of each. order doesn't matter, we're doing that at the end
, one_of_each AS
(
  SELECT nr FROM temp_code_table 
)
-- ok we got at least one of each. Now fill up the rest with randoms
-- generate a list of hundred random values 1,2,3,4,5,9 (100 is chosen randomly)
, hundred_random AS
( 
  SELECT CASE round(dbms_random.value(1,6)) 
              WHEN 1 THEN '1'
              WHEN 2 THEN '2'
              WHEN 3 THEN '3' 
              WHEN 4 THEN '4'
              WHEN 5 THEN '5'
              WHEN 6 THEN '9' 
         END AS nr
  FROM DUAL connect by LEVEL < 101
)
-- select 4 out of those.
, four_more (nr) AS
( 
  SELECT nr FROM hundred_random WHERE rownum < 5
)
-- put it all together
, ten_rows AS
(
  SELECT nr FROM one_of_each
  UNION ALL
  SELECT nr FROM four_more
)
-- and shuffle...
SELECT nr FROM ten_rows order by dbms_random.value
;

The "temp_code_table" cte generates exactly 6 rows. That way you have at least one of each number in the set. The "hundred_random" cte uses the case statement on the random number which will generate a single random value of the set. Then that is run 100 times using CONNECT BY LEVEL. Out of those 100, 4 are picked. Those 4 could all be 1 - this is random, there is no guarantee that you have distinct numbers. At the end we union the 6 rows of the temp_code_table cte and the 4 rows of the four_more and order them randomly.

Upvotes: 1

Related Questions