Difference with previous data

Question

I have this table:

CREATE TABLE datos ( 
    id_estacion smallint(6) DEFAULT NULL, 
    id_sensor smallint(6) DEFAULT NULL, 
    tipo_sensor smallint(6) DEFAULT NULL, 
    valor float DEFAULT NULL, 
    fecha date DEFAULT NULL, 
    hora time DEFAULT NULL, 
    id int(11) NOT NULL AUTO_INCREMENT, 
    dato float DEFAULT NULL, 
    PRIMARY KEY (id) 
) ENGINE=InnoDB AUTO_INCREMENT=8556 DEFAULT CHARSET=latin1;

And this data:

id_estacion  fecha       hora      valor
1            2019-03-15  00:00:00  1164.63
1            2019-03-15  00:15:00  1164.63
1            2019-03-15  00:30:00  1164.64
1            2019-03-15  00:45:00  1164.62
1            2019-03-15  01:00:00  1164.67
1            2019-03-15  01:15:00  1164.63
1            2019-03-15  01:30:00  1164.64

I need to calculate with mysql the difference between a data and the previous data. For example the value at '00:30' is 1164.64, the previus value, at '00:15', is 1164.63 the difference is 0.01.

id_estacion  fecha      hora     valor    diferencia
1            3/15/2019  0:00:00  1164.63   0
1            3/15/2019  0:15:00  1164.63   0
1            3/15/2019  0:30:00  1164.64   0.01
1            3/15/2019  0:45:00  1164.62  -0.02
1            3/15/2019  1:00:00  1164.67   0.05
1            3/15/2019  1:15:00  1164.63  -0.04
1            3/15/2019  1:30:00  1164.64   0.01

Is that possible? Hope you understand me.

Best regards

score 0 · Answer 1 · answered Mar 16 '19 at 00:42

Here is a solution that should work on all versions of MySQL. The principle is to self-JOIN the table, using a NOT EXISTS condition to bring in the previous record of the same id_estacion.

SELECT
    t.id_estacion,
    t.fetcha,
    t.hora,
    t.valor,
    COALESCE(t.valor - tprev.valor, 0) diferencia
FROM mytable t
LEFT JOIN mytable tprev 
    ON  tprev.id_estacion = t.id_estacion
    AND CONCAT(tprev.fecha, ' ', tprev.hora) < CONCAT(t.fecha, ' ', t.hora)
    NOT EXISTS (
        SELECT 1 FROM mytable t1 
        WHERE 
            t1.id_estacion = t.id_estacion 
            AND CONCAT(t1.fecha, ' ', t1.hora) < CONCAT(t.fecha, ' ', t.hora)
            AND CONCAT(t1.fecha, ' ', t1.hora) > CONCAT(tprev.fecha, ' ', tprev.hora)
    )

NB: I would recommend not storing the date and time parts in separated columns, as it just makes things more complex; instead, you can use a unique column of datatype DATETIME, and use MySQL date and time functions when you need to extract parts of it.

Difference with previous data

1 Answers1