Q

Using SQL to forecast growth, part 2

Here is the continuation of Jason's answer. See part 1.

Here's the actual SQL and its resulting rows:

select Cardinal + 1 + ( select max(TimePeriod) 
                          from History ) TimePeriod,
       ( select max(Magnitude) 
           from History 
           where TimePeriod = ( select max(TimePeriod) 
                                  from History ) ) + 
       ( ( select avg(H.Magnitude-PrevH.Magnitude)
                  from History H
                       inner join
                       History PrevH
                    on H.TimePeriod-1 = PrevH.TimePeriod )
         * ( Cardinal + 1 ) ) Forecasted
  from TwoDigitCardinals
  order by Cardinal;  

TIMEPERIOD FORECASTED
---------- ----------
5                2466
6                2832
7                3198
8                3564
9                3930
10               4296
11               4662
...
102             37968
103             38334
104             38700
For exponential growth, the SQL to get our multiplier looks like this:
select avg(H.Magnitude/PrevH.Magnitude) Multiplier
  from History H
       inner join
       History PrevH
    on H.TimePeriod-1 = PrevH.TimePeriod;

Multiplier
----------
1.284
Mathematically, my formula for exponential growth looks like f(x) = a^x + b, where x is the time period extending into the future, and f(x) is the projected magnitude.

Here is the SQL and result set for exponential growth, which uses the POWER function to raise the multiplier to a power relative to the forecasted time period. (Please note, the POWER function or a function with similar functionality should exist in your SQL implementation. I know POWER exists in Oracle, MS SQL Server, and MySQL.)

select Cardinal + 1 + ( select max(TimePeriod) 
                          from History ) TimePeriod,
       ( select max(Magnitude) 
           from History 
           where TimePeriod = ( select max(TimePeriod) 
                                  from History ) ) * 
       power( ( select avg(H.Magnitude/PrevH.Magnitude)
                  from History H
                       inner join
                       History PrevH
                    on H.TimePeriod-1 = PrevH.TimePeriod ),
              Cardinal + 1) Forecast
  from TwoDigitCardinals
  order by Cardinal;  

TIMEPERIOD FORECASTED
---------- -------------------
5                     2695.897
6                     3460.887
7                     4442.951
8                     5703.686
9                     7322.167
10                    9399.911
11                   12067.236
...
102         89879002753597.187
103        115383130030686.330
104	148124325902641.780
You can see how quickly the exponential growth model can grow. You'll want to tailor which model you use to your specific circumstances. Perhaps, even an average or a weighted average of the two will give you a better forecast. We can create any mathematical function such that f(x) is our magnitude and use the view based on the Cardinals table to create forecasts.

For More Information


This was first published in March 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close