Linear interpolation of MySQL data
Hey guys,
I am working on a database that aggregates financials data observations (consider the currency pair USDCAD as an example).
The financial data comes in as observations. I don't set the date and time for the observations as it is determined by the data provider.
My table looks like this: CREATE TABLE observations (symbol varchar(32) not null, datetime datetime not null, value decimal(20, 10) not null, primary key(series_id, datetime));
I need to do linear interpolation of the data - for example, lets say I have randomly dispersed observations at 1:04, 1:12, 1:17, 1:21 and 1:27 - I need to display the data as having observations of 1:00, 1:05, 1:10, 1:15, 1:20, 1:25, 1:30, and the respective data interpolated.
I was offered to use the below query, but it takes 10 minutes to run!
SELECT time, p0.y + IFNULL((unixtime-p0.x) * (p1.y-p0.y) / (p1.x-p0.x), 0) AS value
FROM
(
SELECT
time,
UNIX_TIMESTAMP(`time`) AS `unixtime`,
MAX(UNIX_TIMESTAMP(`before`.`datetime`)) AS `prevTime`,
MIN(UNIX_TIMESTAMP( `after`.`datetime`)) AS `nextTime`
FROM
(
SELECT '2012-05-02 19:20:00' AS `time`
UNION ALL SELECT '2012-05-02 19:20:05'
UNION ALL SELECT '2012-05-02 19:20:10'
UNION ALL SELECT '2012-05-02 19:20:15'
-- etc.
) AS `times`
JOIN (SELECT `datetime` FROM `observations` WHERE `series_id` = MYSERIESID)
AS `before` ON (`before`.`datetime` <= `time`)
JOIN (SELECT `datetime` FROM `observations` WHERE `series_id` = MYSERIESID)
AS `after` ON ( `after`.`datetime` >= `time`)
GROUP BY `time`
) AS `matches`
JOIN (
SELECT
UNIX_TIMESTAMP(`datetime`) AS x,
`value` AS y
FROM `observations` WHERE `series_id` = MYSERIESID
) AS p0 ON (p0.x = `matches`.`prevTime`)
JOIN (
SELECT
UNIX_TIMESTAMP(`datetime`) AS x,
`value` AS y
FROM `observations` WHERE `series_id` = MYSERIESID
) AS p1 ON (p1.x = `matches`.`nextTime`);
Thank you!