I am doing a Seles forecast in SQL Oracle. I need to figure out what revenue I can expect for next year. I should calculate for each month(In my example January, February 2018 for Each customer by State/City) . I have data for 3 years.

The result should contain an estimated sales forecast for each month based on the city state combination. I was trying to use use req_slope, but it doesn't work. I have code here: SQL Fiddle

select c.*,
       max(year)  1 forecast_year,
       regr_slope(revenue, year)
         * (max(year)   1)
           regr_intercept(revenue, year) forecasted_revenue
from   New_customer_data c 
group by Cust_ID ,
       State ,
       City ,   
       year ,
       id_month ,
       revenue ;

CodePudding user response：

I need to figure out what revenue I can expect for next year.

Remove revenue and year from the GROUP BY clause as those are the columns you want to perform the regression on:

select cust_id,
       city,
       state,
       id_month,
       max(year)  1 forecast_year,
       regr_slope(revenue, year)
         * (max(year)   1)
           regr_intercept(revenue, year) forecasted_revenue
from   New_customer_data c 
group by
       Cust_ID,
       city,
       state,
       id_month;

Which, for your sample data:

insert into New_customer_data
select 1, 'MN' , 'Minneapolis', 2016, 1, 679862 from dual union all
select 1, 'IL', 'Chicago' , 2016,  2, 11862 from dual union all
select 1,  'MN' , 'Minneapolis', 2017,  1,547365 from dual union all
select 1, 'IL', 'Chicago' , 2017,  2, 705365 from dual union all
select 2, 'CA', 'San Diego',  2016, 1,  51074  from dual union all
select 2, 'CA', 'LA', 2016, 2, 598862 from dual union all
select 2, 'CA', 'San Diego',  2017,  1, 705365 from dual union all
select 2,'CA', 'LA',  2017,  2, 50611  from dual union all
select 3, 'CA', 'Santa Monica', 2016,  1, 190706 from dual union all
select 3, 'IL', 'Evanston', 2016, 2, 679862 from dual union all
select 3, 'CA', 'Santa Monica', 2017,  1, 705365 from dual union all
select 3, 'IL', 'Evanston', 2017, 2,  90393  from dual union all
select 4, 'MN', 'Shakopee',  2016, 1,  31649  from dual union all
select 4, 'FL', 'Miami', 2016, 2,888862 from dual union all
select 4, 'MN', 'Shakopee', 2017,  1, 125365 from dual union all
select 4, 'FL', 'Miami',  2017, 2,  30566  from dual;

Outputs:

CUST_ID	CITY	STATE	ID_MONTH	FORECAST_YEAR	FORECASTED_REVENUE
1	Minneapolis	MN	1	2018	414868
1	Chicago	IL	2	2018	1398868
2	San Diego	CA	1	2018	1359656
2	LA	CA	2	2018	-497640
3	Santa Monica	CA	1	2018	1220024
3	Evanston	IL	2	2018	-499076
4	Shakopee	MN	1	2018	219081
4	Miami	FL	2	2018	-827730

db<>fiddle here