I need to generate 50 random numbers in the range from 1 to 300.000.000 using PL/SQL (Oracle sql dev)

I tried using floor(dbms_random.value(1, 300000000));

This works, but when I output the numbers, they don't have a good distribution (ex. i need them to be numbers of different digits like: 45, 2, 829, 2000000 etc). Instead, more than 95% of them, have the same number of digits (8 in this case and rarely 7) which is not what I want. Is there any way I can fix this?

CodePudding user response：

Another approach I can think of is to use the same function with different ranges like -

floor(dbms_random.value(1, 10));
floor(dbms_random.value(11, 100));
floor(dbms_random.value(101, 1000));
floor(dbms_random.value(1000, 100000));
floor(dbms_random.value(100001, 300000000));

CodePudding user response：

Instead, more than 95% of them, have the same number of digits (8 in this case and rarely 7)

Let's think about that for a moment. Let's look at the distribution of digits in the 1 to 300.000.000 range.

• 9: 200.000.000 (100.000.000 to 300.000.000)
• 8: ~90.000.000 (10.000.000 to 99.999.999)
• 7: ~9.000.000 (1.000.000 to 9.999.999)
• 6: ~900.000 (100.000 to 999.999)
• 5: ~90.000 (10.000 to 99.999)

And so on. Or another way...

• 9: 66% (2 out of 3)
• 8: 30% (9 out of 30)
• 7: 3% (9 out of 300)
• 6: .3% (9 out of 3000)
• 5: .03 % (9 out of 30000)

A good distribution of random numbers will have 96% with 9 or 8 digits. If you got an even distribution of digits, that would be a bad distribution.

Demonstration

CodePudding user response：

If you want a more even distribution of lengths then you can use a logarithmic scale and generate a random number between 0 and log₁₀300000000 and then raise 10 to that power:

SELECT FLOOR(POWER(10, DBMS_RANDOM.VALUE(0, LOG(10, 300000000)))) AS random_value
FROM   DUAL
CONNECT BY LEVEL <= 20

May output:

Note: With a logarithmic scale, there will be the same chance to get the numbers 1-10 as it is to get the value 11-100 or 1001-10000 or 10001-100000, etc. So 7 will be 10 times more likely to occur than 70 and 100 times more likely to occur than 700.

fiddle