The problem I was working on is solved, but felt it would be worthwhile to ask for some opinions as to whether the approach can actually be improved.
I am using the following example and data taken from
SQL datetime calculations and MySQL 5.1:
Code:
CREATE TABLE `data` (
`id` INT NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`temperature` float NOT NULL,
`change` float NOT NULL,
PRIMARY KEY (id)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `data` (`datetime`, `temperature`) VALUES ('2009-01-01 06:07:08','70.4')
,('2009-01-01 06:08:08','62.1')
,('2009-01-01 06:09:08','78.8')
,('2009-01-01 07:08:09','72.6')
,('2009-01-01 07:08:10','63.2')
,('2009-01-01 07:08:11','79.9')
,('2009-01-01 08:09:10','69.3')
,('2009-01-01 11:12:13','64.3')
;
Now, to calculate the changes in temperature, the following query can be used:
Code:
UPDATE data AS D1, data AS D2 SET D1.change = D1.temperature - D2.temperature WHERE D1.id = D2.id + 1;
This works as designed, but requires the need for the data to be sorted first by datetime and - for performance reasons - the addition of an auto-increment called id. Perhaps there are other approaches, which are used commonly in situations where changes in data need to be calculated?
More UNIX and Linux Forum Topics You Might Find Helpful
Powered by 
09-14-2009
Hybrid Mode
