oracle regr,Forecasting Time series data in Oracle/SQL

You can create a simple forecast using the REGR linear regression functions.

--Ordinary least squares forecast for each customer for the next year.

select

cust_id,

max(year) +1 forecast_year,

-- y = mx+b

regr_slope(revenue, year)

* (max(year) + 1)

+ regr_intercept(revenue, year) forecasted_revenue

from customer_data

group by cust_id;

CUST_ID FORECAST_YEAR FORECASTED_REVENUE

------- ------------- ------------------

1 2018 730868

2 2018 50148

4 2018 7483

3 2018 -9920

Below is the sample schema. Or you can use this SQLFiddle.

create table customer_data

(

cust_id number,

year number,

revenue number

);

insert into customer_data

select 1, 2016, 679862 from dual union all

select 1, 2017, 705365 from dual union all

select 2, 2016, 51074 from dual union all

select 2, 2017, 50611 from dual union all

select 3, 2016, 190706 from dual union all

select 3, 2017, 90393 from dual union all

select 4, 2016, 31649 from dual union all

select 4, 2017, 19566 from dual;