Generating Data: Increase a number by a random percentage

Suppose you want to create some test data in a table where a column value should increase by a random percentage each row.
For example, you wish to generate “sales” data for a month. For each day of the month you want to record how many sales orders are in the system. Since sales orders are always added, the number of sales orders will continually increase. On some days it will increase more than others. To reflect this variable growth, I came up with this solution.

The sales table then has two columns: sales_date and number_of_orders.

CREATE TABLE sales (sales_date DATE,number_of_orders NUMBER);

The following SQL can generate ever increasing values for the number_of_orders column for a period of n days. The SQL takes three arguments:

1. start_value – the number of sales orders you want to start out with
2. Num_days – the number of days for which you want to generate data
3. Start_date – the date from which you want to start generating data
4. max_growth_pct – the maximum percentage you want to allow the number of sales order to grow.

For each row in the result set, the number of sales orders generated will have increased by a random percentage (within the given range). The pct_increase column shows by what percentage each day the number of sales orders have grown.

[sourcecode language=”sql”]
WITH a AS
(SELECT LEVEL lvl
,&start_value +
(SUM(round(dbms_random.value(1, &max_growth_pct) * 10)) over(ORDER BY LEVEL)) new_val
FROM dual
CONNECT BY LEVEL <= &num_days)
SELECT to_date(‘&start_date’,’dd-mon-yy’) + lvl dt
,new_val
,ROUND(new_val / GREATEST(LAG(new_val, 1, 0) over(ORDER BY lvl),&start_value)-1,4)*100 pct_increase
FROM a;
[/sourcecode]

I used the following variable values to generate the rows below:

• start_value = 1000
• max_growth_pct = 10
• num_days = 10
• start_date = ’01-JAN-10′

DT	Sales Orders	PCT_INCREASE
02-Jan-10	1028	2.8
03-Jan-10	1066	3.7
04-Jan-10	1165	9.29
05-Jan-10	1251	7.38
06-Jan-10	1294	3.44
07-Jan-10	1361	5.18
08-Jan-10	1375	1.03
09-Jan-10	1457	5.96
10-Jan-10	1544	5.97
11-Jan-10	1618	4.79

Now all you have to do is wrap the SQL inside an insert statement and you’re ready to go.