CODEWITHSUNDEEP
sqloracle-database
UserU95
UserU95

Reputation: 1

Generating random numbers in PL SQL

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?

Upvotes: 0

Views: 142

Answers (3)

MT0
MT0

Reputation: 167972

If you want a more even distribution of lengths then you can use a logarithmic scale and generate a random number between 0 and log10300000000 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:

RANDOM_VALUE
8
16974213
310
7
24360
57
175411274
107512402
3832107
28521
3
43978239
153212
907350
273253102
1
13877946
27677
1
1267448

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

Upvotes: 0

Schwern
Schwern

Reputation: 164809

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

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

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));

Upvotes: 0

Related Questions