MySQL Forums
Forum List  »  Performance

Linear interpolation of MySQL data
Posted by: Aviv Giladi
Date: May 05, 2012 06:10PM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Linear interpolation of MySQL data
4195
May 05, 2012 06:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.