Very simple daily forecast example for Fredrik

View All Public Scripts Login to Run Script

Script Name Very simple daily forecast example for Fredrik
Visibility Unlisted - anyone with the share link can access
Description Very simple daily forecast example for Fredrik
Contributor Kim Berg Hansen
Created Wednesday August 14, 2019

Statement 1

create table sales ( 
   item     varchar2(10) 
 , day      date 
 , qty      number 
)

Table created.

Statement 2

insert into sales 
select 
   'Item' as item 
 , date '2019-01-01' - level as day 
 , 200 - trunc(level/7) + trunc(17*dbms_random.value) - mod(level, 5) as qty 
from dual 
connect by level <= 500

500 row(s) inserted.

Statement 3
```
commit
```
```
Statement processed.
```

Statement 4

select * 
from ( 
   select 
      item 
    , day 
    , qty 
    , regr_slope(qty, day - date '2000-01-01') over ( 
         partition by item 
         order by day 
         range between interval '12' month preceding and current row 
      ) as slope 
   from sales 
) 
where day between date '2018-12-01' and date '2018-12-31' 
order by day


ITEM DAY QTY SLOPE
Item 01-DEC-18 207 .1455830224365779797595351020130934599398
Item 02-DEC-18 206 .14578213505758907291474810172978324627
Item 03-DEC-18 208 .1462816911233341757171939928444284262332
Item 04-DEC-18 199 .14578262457847662323596558912920965017
Item 05-DEC-18 204 .1460770713923381414482842598841915960278
Item 06-DEC-18 210 .1460888198936393491575039574704252896281
Item 07-DEC-18 212 .1464881465576585236906693035525142710636
Item 08-DEC-18 196 .145933274631620234590647336302685450399
Item 09-DEC-18 194 .1450522594142515439794743891850150313508
Item 10-DEC-18 203 .1447399450879944390427174283509693431425
Item 11-DEC-18 206 .1449516628718599529692807286028890298981
Item 12-DEC-18 201 .1448823956662715825170062615840528780463
Item 13-DEC-18 200 .1443974028469311017707806206023432141085
Item 14-DEC-18 201 .1439885305256046959738742702314393566227
Item 15-DEC-18 196 .1437021608063877580616441415669930751151
Item 16-DEC-18 202 .1436735238344660642704211287005484469644
Item 17-DEC-18 206 .1434974186951698362124300367568996439347
Item 18-DEC-18 197 .143353989075117592095706228724963301231
Item 19-DEC-18 205 .1431089838708986563263537853120481492745
Item 20-DEC-18 200 .1428510063631596370447379258143332939677
Item 21-DEC-18 208 .1431182847677621124294860459011498233747
Item 22-DEC-18 201 .1429703270795000278415004794245192445958
Item 23-DEC-18 203 .1428103761294929603836864716619417702666
Item 24-DEC-18 206 .1430414299884167119983405241912044110727
Item 25-DEC-18 210 .1428555344313694775159996842590275300428
Item 26-DEC-18 210 .1431477784012370192828396617165906583505
Item 27-DEC-18 211 .143201992839533217357676391203064890277
Item 28-DEC-18 209 .1436010747431086167302329935854406697625
Item 29-DEC-18 200 .1429573547759799443292370633397195412455
Item 30-DEC-18 209 .1431515721881155342722751890621452885756
Item 31-DEC-18 202 .1431099629126737569687887601109009570745

31 rows selected.

ITEM	DAY	QTY	SLOPE
Item	01-DEC-18	207	.1455830224365779797595351020130934599398
Item	02-DEC-18	206	.14578213505758907291474810172978324627
Item	03-DEC-18	208	.1462816911233341757171939928444284262332
Item	04-DEC-18	199	.14578262457847662323596558912920965017
Item	05-DEC-18	204	.1460770713923381414482842598841915960278
Item	06-DEC-18	210	.1460888198936393491575039574704252896281
Item	07-DEC-18	212	.1464881465576585236906693035525142710636
Item	08-DEC-18	196	.145933274631620234590647336302685450399
Item	09-DEC-18	194	.1450522594142515439794743891850150313508
Item	10-DEC-18	203	.1447399450879944390427174283509693431425
Item	11-DEC-18	206	.1449516628718599529692807286028890298981
Item	12-DEC-18	201	.1448823956662715825170062615840528780463
Item	13-DEC-18	200	.1443974028469311017707806206023432141085
Item	14-DEC-18	201	.1439885305256046959738742702314393566227
Item	15-DEC-18	196	.1437021608063877580616441415669930751151
Item	16-DEC-18	202	.1436735238344660642704211287005484469644
Item	17-DEC-18	206	.1434974186951698362124300367568996439347
Item	18-DEC-18	197	.143353989075117592095706228724963301231
Item	19-DEC-18	205	.1431089838708986563263537853120481492745
Item	20-DEC-18	200	.1428510063631596370447379258143332939677
Item	21-DEC-18	208	.1431182847677621124294860459011498233747
Item	22-DEC-18	201	.1429703270795000278415004794245192445958
Item	23-DEC-18	203	.1428103761294929603836864716619417702666
Item	24-DEC-18	206	.1430414299884167119983405241912044110727
Item	25-DEC-18	210	.1428555344313694775159996842590275300428
Item	26-DEC-18	210	.1431477784012370192828396617165906583505
Item	27-DEC-18	211	.143201992839533217357676391203064890277
Item	28-DEC-18	209	.1436010747431086167302329935854406697625
Item	29-DEC-18	200	.1429573547759799443292370633397195412455
Item	30-DEC-18	209	.1431515721881155342722751890621452885756
Item	31-DEC-18	202	.1431099629126737569687887601109009570745

Statement 5

select 
   item 
 , day + 31 as forecast_day 
 , qty + 31 * slope as forecast_qty 
from ( 
   select 
      item 
    , day 
    , qty 
    , regr_slope(qty, day - date '2000-01-01') over ( 
         partition by item 
         order by day 
         range between interval '12' month preceding and current row 
      ) as slope 
   from sales 
) 
where day between date '2018-12-01' and date '2018-12-31' 
order by forecast_day


ITEM FORECAST_DAY FORECAST_QTY
Item 01-JAN-19 211.513073695533917372545588162405897258
Item 02-JAN-19 210.519246186785261260357191153623280634
Item 03-JAN-19 212.534732424823359447233013778177281213
Item 04-JAN-19 203.519261361932775320314933263005499155
Item 05-JAN-19 208.528389213162482384896812056409939477
Item 06-JAN-19 214.528753416702819823882622681583183978
Item 07-JAN-19 216.541132543287414234410748410127942403
Item 08-JAN-19 200.523931513580227272310067425383248962
Item 09-JAN-19 198.496620041841797863363706064735465972
Item 10-JAN-19 207.486938297727827610324240278880049637
Item 11-JAN-19 210.493501549027658542047702586689559927
Item 12-JAN-19 205.491354265654419058027194109105639219
Item 13-JAN-19 204.476319488254864154894199238672639637
Item 14-JAN-19 205.463644446293745575190102377174620055
Item 15-JAN-19 200.454766984998020499910968388576785329
Item 16-JAN-19 206.453879238868447992383054989717001856
Item 17-JAN-19 210.448419979550264922585331139463888962
Item 18-JAN-19 201.443973661328645354966893090473862338
Item 19-JAN-19 209.436378499997858346116967344673492628
Item 20-JAN-19 204.428381197257948748386875700244332113
Item 21-JAN-19 212.436666827800625485314067422935644525
Item 22-JAN-19 205.432080139464500863086514862160096582
Item 23-JAN-19 207.427121660014281771894280621520194878
Item 24-JAN-19 210.434284329640918071948556249927336743
Item 25-JAN-19 214.428521567372453802995990212029853431
Item 26-JAN-19 214.437581130438347597768029513214310409
Item 27-JAN-19 215.439261778025529738087968127295011599
Item 28-JAN-19 213.451633317036367118637222801148660763
Item 29-JAN-19 204.431677998055378274206348963531305779
Item 30-JAN-19 213.437698737831581562440530860926503946
Item 31-JAN-19 206.436408850292886466032451563437929669

31 rows selected.

Statement 6
```
drop table sales purge
```
```
Table dropped.
```

ITEM	FORECAST_DAY	FORECAST_QTY
Item	01-JAN-19	211.513073695533917372545588162405897258
Item	02-JAN-19	210.519246186785261260357191153623280634
Item	03-JAN-19	212.534732424823359447233013778177281213
Item	04-JAN-19	203.519261361932775320314933263005499155
Item	05-JAN-19	208.528389213162482384896812056409939477
Item	06-JAN-19	214.528753416702819823882622681583183978
Item	07-JAN-19	216.541132543287414234410748410127942403
Item	08-JAN-19	200.523931513580227272310067425383248962
Item	09-JAN-19	198.496620041841797863363706064735465972
Item	10-JAN-19	207.486938297727827610324240278880049637
Item	11-JAN-19	210.493501549027658542047702586689559927
Item	12-JAN-19	205.491354265654419058027194109105639219
Item	13-JAN-19	204.476319488254864154894199238672639637
Item	14-JAN-19	205.463644446293745575190102377174620055
Item	15-JAN-19	200.454766984998020499910968388576785329
Item	16-JAN-19	206.453879238868447992383054989717001856
Item	17-JAN-19	210.448419979550264922585331139463888962
Item	18-JAN-19	201.443973661328645354966893090473862338
Item	19-JAN-19	209.436378499997858346116967344673492628
Item	20-JAN-19	204.428381197257948748386875700244332113
Item	21-JAN-19	212.436666827800625485314067422935644525
Item	22-JAN-19	205.432080139464500863086514862160096582
Item	23-JAN-19	207.427121660014281771894280621520194878
Item	24-JAN-19	210.434284329640918071948556249927336743
Item	25-JAN-19	214.428521567372453802995990212029853431
Item	26-JAN-19	214.437581130438347597768029513214310409
Item	27-JAN-19	215.439261778025529738087968127295011599
Item	28-JAN-19	213.451633317036367118637222801148660763
Item	29-JAN-19	204.431677998055378274206348963531305779
Item	30-JAN-19	213.437698737831581562440530860926503946
Item	31-JAN-19	206.436408850292886466032451563437929669

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database