Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Using SQL to forecast growth

I have a simple table, it has a date column and a column with values that reflect the total disk space occupied...

by a database. Given that I have values for all preceding dates, how can I predict future values using SQL or PL/SQL , i.e. trend prediction.

Assume a simple table:

```Col1     Col2
JAN      1000
FEB      1300
MAR      1800
APR      2100
```
...and so on. How would I predict the value for any month in the future?

This is probably one of those problems more easily solved with a spreadsheet application such as Microsoft Excel. However, the SQL challenge has been put forth, and it is my obligation to accept. I came up with two different solutions: one for linear growth, and one for exponential growth. However, their implementations are very similar. Which solution you use depends on the type of thing you are measuring. For example, linear growth would be evident in your car's odometer, where population growth tends to be exponential.

The solutions involves calculating an addend for linear growth and a multiplier for exponential growth. They also use a helper table to assist in extending out the forecast. Let's start by creating our helper table. Its utility will become evident shortly.

```create table Cardinals
( digit numeric(1) primary key
check (digit >= 0)
);

insert into Cardinals values (0);
insert into Cardinals values (1);
insert into Cardinals values (2);
insert into Cardinals values (3);
insert into Cardinals values (4);
insert into Cardinals values (5);
insert into Cardinals values (6);
insert into Cardinals values (7);
insert into Cardinals values (8);
insert into Cardinals values (9);
```
This table simply holds the numbers 0 through 9. By cross joining the Cardinals table to itself and using each instance of the table for a different order of magnitude, we can produce a view that returns numbers 0 through 99. We will use this view as the basis for our forecast consisting of one hundred time periods.
```create view TwoDigitCardinals
as
select ( Tens.Digit * 10 ) + Ones.Digit Cardinal
from Cardinals Ones
cross join
Cardinals Tens;
```
(Please note, if your database doesn't support the SQL-92 CROSS JOIN syntax, you should be able to accomplish the same by simply separating the two instances of the table by a comma with no WHERE clause.) Incidentally, by cross joining even more instances of the Cardinals table, we can create queries that return even larger series of numbers. Now, let's create a table to store our history. It's pretty generic, with time periods and magnitude represented by simple integers. Without too much difficulty, we could modify this example to use dates to solve the original request. For now, I'll try to make my example as simple as possible for the sakes of both understanding and brevity. Here's the History table:
```create table History
( TimePeriod smallint primary key,
Magnitude integer
);

insert into History values (1, 1000);
insert into History values (2, 1300);
insert into History values (3, 1800);
insert into History values (4, 2100);
```
For linear growth, I decided to take the average of the increases from one period to the next as the basis for future growth. The query could be easily modified to consider only the last five or ten time periods. Here it is:
```select avg(H.Magnitude - PrevH.Magnitude) Addend
from History H
inner join
History PrevH
on H.TimePeriod-1 = PrevH.TimePeriod;

------
366
```
Mathematically, my formula for linear 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.

In order to make the final SQL a bit easier to understand, I'll illustrate it first, with pseudo-SQL using some simplified tokens encased in greater-than and less-than symbols:

```select Cardinal + 1 + <Last Recorded TimePeriod>,
<Magnitude of the Last Recorded TimePeriod>
+
( <Addend> * ( Cardinal + 1 ) )
from TwoDigitCardinals
order by Cardinal;```

This was last published in March 2002

## Content

Find more PRO+ content and other member only offers, here.

#### Have a question for an expert?

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

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

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Kubernetes gains momentum in big data implementation process

Big data vendors and users are looking to Kubernetes-managed containers to help accelerate system and application deployments and...

• ### IT, others focus efforts as GDPR implementation date looms

The EU's General Data Protection Regulation is meant to bring better data privacy to bear in the age of big data. How GDPR ...

• ### Streaming tool from StreamSets eyes data in motion for GDPR

StreamSets software for inspecting big data brings governance to data in motion. Such capabilities may find more use as the ...

• ### Rethinking analytics processes spurs enterprise innovation

By taking a fresh look at the makeup of their analytics organizations, enterprises can innovate their business models and take ...

• ### Diversified data sets for analytics deliver top results

Analytics teams should focus on data diversity to ensure that their projects deliver the most meaningful insights -- but they ...

• ### How to boost the value of BI in today's analytics landscape

Traditional BI reporting still gives businesses valuable information. But its value can be increased by incorporating it into a ...

## SearchSAP

• ### New leadership and redesign may spark SAP Community revival

SAP Community Network was once the best forum for SAP developers to share ideas, but user activity declined significantly. Can ...

• ### SAP Ariba Live focuses on procurement for purpose

SAP Ariba Live 2018 focused on the idea that businesses can use procurement technology to do good in the world; for example, by ...

• ### SAP debuts consumption-based pricing model for SAP Cloud

SAP Cloud Platform is now available as a consumption-based model, an alternative to the subscription model. SAP also updated the ...

## SearchSQLServer

• ### ObjectRocket launches Azure MongoDB service

Count ObjectRocket is among those pursuing Azure MongoDB deployments. This open source NoSQL database continues to find traction ...

SQL Operations Studio simplifies routine administration of SQL Server and Azure SQL databases, making database development and ...

• ### Meltdown and Spectre fixes eyed for SQL Server performance issues

Microsoft has responded to the Spectre and Meltdown chip vulnerabilities with patches and other fixes. But IT teams need to sort ...

## TheServerSide.com

• ### Rust and Python top list of most popular development languages

A new Stack Overflow survey of over 100,000 developers indicated Java and JavaScript are not as popular as they had been before. ...

• ### Stay ahead of Java security issues like SQL and LDAP injections

Java security best practices include validating input to prevent injection attacks and managing malicious JAR vulnerabilities.

• ### Why the Waterfall or Agile debate will be around forever

Which is the right methodology to use for your project: Waterfall or Agile? The industry may be at peak Agile, as the ...

## SearchDataCenter

• ### IBM cloud services to secure mainframes out to the edge

Big Blue will introduce IBM cloud services that use blockchain, containers and its z14 mainframes to deliver improved security ...

• ### Four disadvantages of hyper-converged infrastructure systems

Problems with scalability and unexpected licensing costs can create problems for organizations that deploy hyper-converged ...

• ### IBM Power9 servers seek market inroads to AI, cloud

IBM follows up its first Power9 server with a raft of systems designed to appeal to a wider array of markets -- most notably, AI ...

## SearchContentManagement

• ### Scrivito unveils serverless CMS product

By building the CMS with ReactJS, Scrivito gained attraction with development community, according to an analyst.

• ### Content personalization tools sharpen focus on customers

Content personalization isn't new; Amazon weaponized it, and Jeff Bezos is the world's richest man. New tools are putting it ...

• ### Leading brands see the need for personalized content

Content personalization continues to expand within companies as maturing technologies make it a viable marketing option for ...

## SearchHRSoftware

• ### Oracle HCM Cloud gets 'big' upgrade

Oracle is bringing consumerlike approaches to its HCM cloud applications. That includes using virtual assistants and intelligent ...

• ### Why the talent management revolution pivots on AI for HR

In this Q&A, HR analyst Jeanne Meister explains how AI will transform the way companies deal with talent and how HR departments ...

• ### Blockchain in HR remains in beta, outlook uncertain

Blockchain in HR verification is getting interest from universities, vendors and startups. There are powerful forces driving its ...

Close