I am trying to build a system that will track vehicle fuelings, and have run into a problem with one report; determining fuel efficiency in distance/fuel. Sample data is:

Where odometer is the total distance the vehicle has traveled, fuel is the number of gallons or liters put in, and partial_fillup is a boolean meaning the fuel tank was not completely filled if non-zero.

If the user fills the tank each time the query I can use is:

set @a = null;
select 
   odometer,
   odometer-previousOdometer distance,
   fuel,
   (odometer-previousOdometer)/fuel mpg,
   partial_fillup
from
   (
      select 
         @a as previousOdometer,
         @a:=odometer,
         odometer,
         fuel/1000 fuel,
         partial_fillup
      from fuel
      where 
         vehicle_id =1
         and odometer >= 61290
      order by odometer
   ) as readings
where readings.previousOdometer is not null;

However, when the user only partially fills the tank, the correct procedure would be to subtract the last full fueling from current odometer reading, then divide by the sum of all fuel since the previous odometer reading, so at odometer 63499, the calculate would be (63499-62876)/(10.7660 6.1370)

CodePudding user response：

This will get the average used on the last ride:

select
  odometer,
  odometer-lag(odometer) over (order by odometer) as distance,
  fuel,
  (odometer-lag(odometer) over (order by odometer))/fuel as mpg
from fuel

output:

Or you can calculate the total drive distance, and the total amount of fuel used:

select
   distance,
   sum_fuel,
   distance/sum_fuel as mpg
from (
select
   f.odometer,
   f.odometer-(select min(odometer) from fuel) as distance,
   fuel,
   sum_fuel
from fuel f
inner join (
   select
      odometer,
      sum(fuel) over (order by R) as sum_fuel
   from (
      select 
         odometer, 
         fuel,
         row_number() over (order by odometer) R
      from fuel) x
   ) x on x.odometer = f.odometer
) x2

which will get next output, which will get closer to an average after a longer time of measurement:

DBFIDDLE

CodePudding user response：

I was able to figure it out after studying Luuk's answer. I'm sure there is a more efficient way to do this; I am not used to using variables in SQL. But, the answers are correct in the test data.

set @oldOdometer = null;
set @totalFuel = 0;

select
   s.odometer,
   format(fuel, 3) fuel,
   s.distance,
   format( distance / fuel, 2) as mpg
from (
   select
      partial_fillup as partial,
      odometer,
      (fuel @totalFuel) as fuel,
      @totalFuel as totalFuel,
      @oldOdometer oldOdometer,
      if ( partial_fillup, null,odometer - @oldOdometer ) as distance,
      @totalFuel := if ( partial_fillup, @totalFuel   fuel, 0) as pastFuel,
      @oldOdometer := if (partial_fillup,@oldOdometer,odometer ) as runningOdometer
   from
      fuel
   order by
      odometer ) s
where s.distance is not null
order by s.odometer
limit 1,999;

limit 1,999 simply there to skip the first row returned, since there is not enough data to calculate distance or mpg. On my copy of MySQL, doing this means you do not need to initialize the two variables (you don't have to include the set commands at the beginning), so it works with my reporting tool very well. If you do initialize them, you do not need the limit statement. Works assuming you don't have more than 999 rows returned.