How to Create A Table With Specific Frequency Distribution

We often need to create tables for testing or testcases, the usual method is to use CTAS (Create Table As Select) on a select from the dual table and the “CONNECT BY ROWNUM < <number_of_rows_we_need>” clause. Looking at the example below we see that we are creating a table with a single column and one hundred tuples, it has the same uniform distribution as the values we will insert correspond to the ROWNUM. We generated one hundred tuples with the values 1,2,3, … 100, and if we took one random row do they all have the same probability to be elected.

 

 -- CREATE TABLE WITH LINEAL-DISTRIBUTION ---

CREATE TABLE T
AS SELECT ROWNUM            
FROM DUAL
CONNECT BY ROWNUM <= 100;

 
Creating tables with specific distributions is not very common, but they are a very powerful tool if we wish implement testing, especially to play with the histograms of the columns. An example of creating a table with a Gaussian distribution or Gaussian bell curve called.

 

 -- CREATE TABLE WITH GAUSS-DISTRIBUCION  ----

CREATE TABLE T
AS SELECT CASE WHEN (ROWNUM BETWEEN 1  AND 10) THEN 1
               WHEN (ROWNUM BETWEEN 11 AND 30) THEN 2
               WHEN (ROWNUM BETWEEN 31 AND 70) THEN 3
               WHEN (ROWNUM BETWEEN 71 AND 90) THEN 4
               WHEN (ROWNUM BETWEEN 91 AND 100) THEN 5
          END X
FROM DUAL
CONNECT BY ROWNUM <= 100;

 

In this case we have created a table with one hundred tuples, and has the values 1,2,3,4 and 5 however, if we took a random row we would have the following probabilities.

  • The probability of being a value of 1 is 10%
  • The probability of being a value of 2 is 20%
  • The probability of being a value of 3 is 40%
  • The probability of being a value of 4 is 20%
  • The probability of being a value of 5 is 10%

 
HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s