0
$\begingroup$

Consider a task, whereby we need to generate a normally distributed data set of 100 numbers. "tableFreq" is being used to hold the "frequency" data. The sum of the frequencies needs to be approx 10000.

Refer to the first image which shows a preview of "tableFreq" (14 out of 100 rows):

  • "fkey" column represents the foreign keys that should eventually be inserted in another table, call it "tableX". There are 100 rows, and the range of "fkey" is 1-100.
  • The frequency represents the number of times these keys should appear in the said table: "tableX". This is what should be normally distributed.

Example (refer to image): if for fkey 1, we got 90 in "tableFreq", "tableX" should have foreign key 1 in it's first 90 rows, if for fkey 2, we got 59 in "tableFreq", "tableX" should have foreign key 2 in the next 59 rows, and so on.

tableTemp

As already mentioned, the sum of the frequencies of the foreign keys, needs to be approx 10000, as in "tableX" we eventually need to have approx 10000 rows. PostgreSQL is being used to generate the data (if it makes any difference).

To generate "tableFreq" we are using something along these lines:

select row_number() OVER () as fkey, (floor((normal_rand)*1.9)) as frq from normal_rand(100, 50, 28.6)

normal_rand (an internal PostgreSQL function) creates 100 (supposedly) normally distributed values, with 50 as mean, and 28.6 standard deviation. We are multiplying the value retrieved from normal_rand by 1.9 to approximate towards 10000 (i.e. if SUM(floor((normal_rand)*1.9)) is replaced into the query, its value would be ≈ 10000). The mean and standard deviation are calculated from the fact that we are generating 100 numbers (mean/std of range 1-100). The problem is that "frq" is sometimes negative (this is rare, < 10%, but still > 0% every time).

We would not be able to insert, say -18 rows (refer to image above) in "tableX". So we modified the query to make sure it always returns positive values

select row_number() OVER () as fkey, (floor((normal_rand)*1.9)) as frq from normal_rand(110, 50, 28.6) where (floor(normal_rand)) > 0 LIMIT 100

In essence, generating 10 more values by the normal_rand function, restricting to > 0 values, and limiting to 100 so we stick to the range(1,100) restriction.

The final query creates approx 10k rows in "tableX" with the frequencies in "tableTemp" and as described in the Example above.

At the end we are trying to use Python/Matplotlib "hist" to prove that the foreign keys in "tableX" follow a normal distribution. The x-axis represents the foreign keys and the y-axis represents the frequencies. This does not look like a normal distribution.

tableX histogram

Is it because of the restriction of the negative numbers (> 0)? Could generating 10 more values and then restricting to 100 be affecting in such a way? How would the negatives be handled in such a case? Or is the approach inherently wrong?

Update -

If we plot the frequencies generated by the normal_rand function as a histogram, the distribution looks "normal". However, the assignment question requires Step 2 i.e. inserting the "foreign keys" into "tableX" according to the frequencies generated in step 1. This is the misleading part.

tableFreq histogram

Update with original question -

Create another two tables (e.g. tableA and tableX in the same schema). Note that table tableA is related to table tableX with a 1(p) - N(t) relationship. tableX is to have about 10k tuples. Whilst tableA has at first 1k, then 100k, and then 10m (if it does not take too much on your install). As for the foreign key distinct values, please set at 100, 2k and 7k respectively. Generate tableA and tableX for the various sizes mentioned. As for distribution, make use of the uniform, normal (average being the mid tuple by sort order of PK) and a distribution of your invention

$\endgroup$
13
  • 1
    $\begingroup$ "whereby we need to generate a normally distributed data set for a range of numbers, e.g. 1-100" - this is impossible. The normal distribution generates data over the whole real range by definition. If you don't allow that, for example because you have a constrained value range, the data cannot be normally distributed. If you want approximate normality, you need to choose a small variance so that the probability that data leave the value range is very small. $\endgroup$ Jan 13, 2022 at 12:02
  • 1
    $\begingroup$ Other than that, your description is very hard to follow. You talk about "the frequency data" as if it were clear what that is, but it isn't. How can this be negative? This is not what you mean when you say you want to generate normally distributed data, or is it? I also don't get the "multiply by 1.9" bit. If you want to be between 1 and 100, why multiply by 1.9? $\endgroup$ Jan 13, 2022 at 12:04
  • $\begingroup$ Hi @ChristianHennig, thank you for your replying. Apologies, it is a bit complicated to explain and the sentence you quoted is indeed misleading. I will fix it. The range 1-100 is a range of FKs; refer to the first column of the first image. The frequency of FKs should be normally distributed and not the FK range; refer to the second column of the first image. You can see the frequencies are not bound within the 1-100 range but there are indeed negative values: the negative values are mainly what I am asking about. $\endgroup$ Jan 13, 2022 at 13:08
  • $\begingroup$ Case in point, the first step is to generate 100 numbers that are normally distributed. The sum of which needs to be approx 10,000 (hence the multiplication by 1.9, based on the chosen mean & standard deviation). The second step is to create rows within "tableX" with the frequencies obtained in step 1, i.e. foreign key 1 with the frequency obtained for 1 in step 1, foreign key 2 with the frequency obtained for 2 in step 2, etc. I hope this is clearer. Thanks again! $\endgroup$ Jan 13, 2022 at 13:13
  • 1
    $\begingroup$ but then in that way, you cannot expect to see a bell curve.. $\endgroup$
    – Tom
    Jan 13, 2022 at 17:55

1 Answer 1

2
$\begingroup$

You are using invalid distribution to model this data. Normal distribution is continuous and ranges over the whole real line. Your data is discrete and bounded. So this is "by design".

To simulate from a discrete, bounded distribution you should use instead Poisson, binomial, negative binomial, or one of the many other distributions.

As an additional note, you are assuming here that all the rows in your table act independently of each other, this assumption may or may not be correct. With this assumption you have no way of ensuring the constraint that the numbers would sum to some constant. If you need the constraint, you might want to use a multivariate distribution that ensures it, like multinomial, or more complicated probabilistic model for simulation.

If for technical reasons you are limited to using normal distribution, than it's a "take it or leave it" scenario. You would see negative numbers in the results. You could be hacking the results by doing things like replacing the "invalid" values with something else (e.g. negative values with zeros), but then the samples would not be resembling the normal distribution anymore and your results become arbitrary.

$\endgroup$
1
  • $\begingroup$ Hi @Tim, thank you for your answer. Regarding using an invalid distribution model; this was not a choice but a requirement based on the assignment question. Regarding negative numbers, playing around with the mean and standard deviation we could get "frequency" values which are > 0 in almost all cases (e.g. mean 60, std 15). Also, the 10,000 sum is an approximation, as also mentioned in the question. I had not included the question as I had thought it would be misleading rather than helpful. I have edited the post and included it now though, if you kindly wish to refer to it. Thanks again! $\endgroup$ Jan 13, 2022 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.