mysql and php - subtract the last two records from the same column and divide by the last record from another column

Question

I have a table carro_log that stores the update history of another table.

id  kilometragem  combustivel  dt_atualização
 1      200           45         2017-05-03
 2      400           35         2017-05-03
 1      150           38         2017-05-02
 3      220           30         2017-05-01
 .       .             .             .  
 .       .             .             .

The calculation of the car yield is (current kilometer - previous kilometer) / current fuel of each id (car). How can I do this? Select and subtract the current and previous value from the same column?

Answer 1

The query below will return in a new column the yield (according to its formula):

select *,
       cast((select (car.kilometragem - cAnt.kilometragem) * 1.0
             from carro_log cAnt
             where cAnt.id = car.id 
                   and cAnt.dt_atualizacao < car.dt_atualizacao
             order by dt_atualizacao desc
             limit 1)
            / car.combustivel as decimal(7,2))  as 'Rendimento'
from carro_log car
order by dt_atualizacao

For the same table I made two selects:
- car = CURRENT
- cant = THE PREVIOUS RECORD

In the subconsultation is subtracted by the immediately previous record (using the limit 1 to ensure only one (1)) and also made a multiplication by 1.0 - to convert to float (could be with cast normally).
Finally, the division is made by the current fuel and the conversion to decimal(7,2) - for two decimal places only.