I found this intriguing considering a side project I've been tinkering on. The solution ended up looking much simpler than a model clause, though I'm going to need to let it digest for a while before I fully understand how it works. Maybe read the relevant documentation.Use regression functions in Oracle to do basic forecasts https://t.co/gaKGHafJaQ— Connor McDonald (@connor_mc_d) June 14, 2016
Trouble is, the final result isn't the sort of thing you want to print out on your dot matrix printer to show the big cheese, right? You want to graph that sucker.
So I create a page in Oracle APEX 5.1 (apexea.oracle.com) and created a line chart.
I split the results into two series using Connor's query within an inline view, in part because that was the fastest way I could think to get the graph plotted
select dte ,sz as actual from ( with reg as ( select regr_slope(sz, to_number(to_char(dte,'j'))) slope, regr_intercept(sz, to_number(to_char(dte,'j'))) intcpt, max(dte) nxt from t ) select t.*, 'actual' tag from t union all select nxt+r dte, trunc(intcpt+slope*to_number(to_char(nxt+r,'j'))) ,'predicted' from reg, ( select rownum r from dual connect by level <= 30 ) order by 1 ) where tag ='actual'The result was compelling.
|A picture paints a thousand words|
I also like the fact the declarative series name honoured, rather than a double quoted "Column Alias".
It will be interesting to see how this plots more varied data, considering the data variance issues Connor described.
I added a
to_char(dte, 'DD Mon YYYY')to get a prettier tooltip, but interestingly, it changed the angle of the line.
Then I spotted this field here, I could use the query as supplied by Connor without having to think about pivoting the result to get it into the right format the engine requires.
|A new declarative option|
However the result didn't come out quite right. I think that's more an issue with the communication between APEX and OracleJET that anything else, ie: a bug.
|Not quite right|
It appears the declarative charting options have been engineered in 5.1 to reduce the amount of hoop jumping with the result set. Charting in general appears more declarative, easier to navigate and work out what's going on.
Nice work APEX team.