I am using for loop to create 10 rows. Inside a for loop, i am using dbms_random.value to generate integers between 0 to 10. I want to code dbms_random methid in such a way where each row as 30% chance to get 0.
Asked
Active
Viewed 114 times
-1
-
Do you actually have a question? – PM 77-1 Mar 11 '15 at 02:25
-
Yes ..do I not sound clear in phrasing of that question? – Ish Bhatt Mar 11 '15 at 02:28
-
`CASE WHEN DBMS_RANDOM.VALUE(0,10) < 3 THEN 0 ELSE ROUND(DBMS_RANDOM.VALUE(1,10)) END` – David Faber Mar 11 '15 at 03:01
1 Answers
2
As-is use of random number generator gives equal probability to all of the numbers. So in case you want to give a particular number higher or lower probability while keeping fair probability for rest of the numbers, you need to set the range representing numbers in same fashion.
So we need to give 0 probability of 30% and rest of 10 numbers have same probability. If N is the entire range of number then if x represents number 1-10 then 3*x represents number 0. So Number of numbers to be thrown is N = 10*x + 3*x = 13*x.
So the range being generated should be multiple of 13.
So make dbms_random generate numbers from 0-12. And set condition if "generate value"<=2 then take it as 0 else take it as "generated value"-2
The psuedocode would be like
select case when T1.x>2
then T1.x-2
else 0
end case
from (select dbms_random(0,13) as x from Dummy) T1