Hey Chaps,
I am stumped! Hope you can help ...
SQL
WITH tblData AS
(
SELECT 1 AS known_ys, TO_DATE('01/01/2015', 'DD/MM/YYYY') AS known_xs FROM DUAL UNION ALL
SELECT 2, TO_DATE('02/01/2015', 'DD/MM/YYYY') FROM DUAL UNION ALL
SELECT 3, TO_DATE('03/01/2015', 'DD/MM/YYYY') FROM DUAL UNION ALL
SELECT 4, TO_DATE('04/01/2015', 'DD/MM/YYYY') FROM DUAL
)
SELECT *
FROM tblData
;
What I am trying to do is predict using my known_ys what will happen on the 05/01/2015 (known_xs). The answer is ofcourse 5
In Excel, I would do FORECAST("05/01/2015", known_ys, knownxs) and this would spit out 5. Unfortunately my data is so big in Excel (and other stats packages) I need to push the calculation into the Oracle server.
I have spent 30 mins reading the documentaiton and getting more confused - does anyone know (and ideally provide an example) how to do this?
Created this just now which works but is a bit 'rubbish'.
WITH tblData AS
(
SELECT 1 AS known_ys, TO_DATE('01/01/2015', 'DD/MM/YYYY') AS known_xs FROM DUAL UNION ALL
SELECT 2, TO_DATE('02/01/2015', 'DD/MM/YYYY') FROM DUAL UNION ALL
SELECT 4, TO_DATE('03/01/2015', 'DD/MM/YYYY') FROM DUAL UNION ALL
SELECT 8, TO_DATE('04/01/2015', 'DD/MM/YYYY') FROM DUAL
)
SELECT last_value,
REGR_SLOPE(known_ys, (known_xs - SYSDATE)) AS slope,
last_value + REGR_SLOPE(known_ys, (known_xs - SYSDATE)) AS nxt_value
FROM tblData,
(SELECT FIRST_VALUE(known_ys) OVER (PARTITION BY 1 ORDER BY known_xs DESC) AS last_value FROM tblData)
GROUP BY last_value
;