generate 6 numbers between 1 and 2 in a 2:1 ratio in SQL or PL/SQL

how can i generate 6 numbers between 1 and 2 where 4 of the 开发者_JAVA百科numbers will be 1 and the other 2 will be 2 in a random order i.e.

results

1

2

1

1

1

2

and also in a different ratio i.e. 3:2:1 for numbers between 1 and 3 for 12 numbers

i.e.

results

1

1

2

3

1

2

1

3

1

1

3

3

results don't have to be in this order but in the ratios as above in oracle SQL or PL/SQL

To get the ratios perfect you could do something like this - generate all the numbers, then sort in random order:

SELECT r
FROM   (SELECT CASE
               WHEN ROWNUM <=4 THEN 1
               ELSE 2
               END AS r
        FROM DUAL
        CONNECT BY LEVEL <= 6)
ORDER BY DBMS_RANDOM.value;

R                      
---------------------- 
2                      
1                      
1                      
2                      
1                      
1                      

I think this will work in straight SQL; it's horrifically inefficient, and a PL/SQL one might be less so. It's also completely static; differing ratios call for a different number of values selected.

select value
  from (
       select mod(value, 2) + 1 as value, 
              row_number() over (partition by 
                                   case mod(value, 2) = 1 
                                     then 1 
                                     else 0 
                                   end) as twos_row,
              row_number() over (partition by 
                                   case mod(value, 2) = 0
                                     then 1 
                                     else 0 
                                   end) as ones_row
         from (select dbms_crypto.randominteger as value
                 from dba_objects
                order by object_id
              )
       )
 where twos_rows <= 2
    or ones_rows <= 4

The inner-most select grabs a big stack of random numbers. The next query out determines whether that random value would be a 2 or a 1 by mod'ing the earlier random value. The last level of nesting just filters out all the rows after the correct number of that type of row has been returned.

This is untested and fragile. If you need a solution that's reliable and performance, I'd recommend PL/SQL, where you

• loop
• pick off random numbers
• determine what partition in your set of values they'd fit into
• keep them if that partition hasn't been satisfied
• exit when all partitions have been satisfied.