计算过去30天的平均燃料

Question

我有一个计算过去30天平均燃料消耗的脚本，但是一些用户报告计算错误。

这是我得到的:

//Calculate the total km for the last 30 days
    $result = mysqli_query($con,"SELECT MIN(km) AS minikm, MAX(km) AS maxkm FROM diesel WHERE diesel.dato >= '$dx' AND userid = ".$_COOKIE['userid']." ORDER BY diesel.dato DESC");
    while($row = mysqli_fetch_array($result)){ $mileage = $row['maxkm'] - $row['minikm'];}
//Sum up all the Liters of diesel
    $result = mysqli_query($con,"SELECT SUM(liter) AS totalfuel FROM diesel WHERE diesel.dato >= '$dx' AND userid = ".$_COOKIE['userid']." ORDER BY diesel.dato DESC");
    while($row = mysqli_fetch_array($result)){ $totalfuel = $row['totalfuel'];} 
$averagefuel = $totalfuel / $mileage * 10;

在排除故障时，我注意到我不应该在计算中包括第一个记录中的升。只有总公里。怎么做呢?

Answer 1

如果我必须推测用户在想什么，问题是一个月的燃料输入与该月的燃料消耗在某种程度上是无关的。换句话说，油箱开始时可能是空满的，或者介于两者之间。并且，它可能以空、满或介于两者之间的某个位置结束。

这意味着你的计算可能会被两边装满一油箱的燃料所偏离。

现在，这可以通过特殊情况来减轻。例如，也许这些车辆每天都在行驶，一天结束时油箱都加满了油。所以，在午夜，你有一个合理的假设，油箱是满的。如果是这样的话，我会很惊讶，因为汽车里的燃料库存是要花钱的。他们可以聘请我作为顾问来改善他们的资产负债表;)(只是为了记录，这不是我的专长)。

作为一个小提示，您可以在SQL中进行完整的计算。您不需要使用两个查询或任何客户端计算:

SELECT 10 * SUM(liter) / (MAX(km) - MIN(km)) as AvgFuel
FROM diesel
WHERE diesel.dato >= '$dx' AND userid = ".$_COOKIE['userid'].";

Answer 2

我认为你需要一直加$mileage。试试这个:

//Calculate the total km for the last 30 days
$mileage = 0;
    $result = mysqli_query($con,"SELECT MIN(km) AS minikm, MAX(km) AS maxkm FROM diesel WHERE diesel.dato >= '$dx' AND userid = ".$_COOKIE['userid']." ORDER BY diesel.dato DESC");
    while($row = mysqli_fetch_array($result)){ $mileage += $row['maxkm'] - $row['minikm'];}
//Sum up all the Liters of diesel
    $result = mysqli_query($con,"SELECT SUM(liter) AS totalfuel FROM diesel WHERE diesel.dato >= '$dx' AND userid = ".$_COOKIE['userid']." ORDER BY diesel.dato DESC");
    while($row = mysqli_fetch_array($result)){ $totalfuel = $row['totalfuel'];} 
$averagefuel = $totalfuel / $mileage * 10;

Gordon Linoff提供给你很好的解决方案。试着这样写:

//Calculate the total km for the last 30 days
$mileage = 0;
$result = mysqli_query($con,
"SELECT 10 * SUM(liter) / (MAX(km) - MIN(km)) as AvgFuel
FROM diesel
WHERE diesel.dato >= '$dx' AND userid = ".$_COOKIE['userid']);
$averagefuel = $steps = 0; 
while($row = mysqli_fetch_array($result)){ 
    $averagefuel += $row['AvgFuel']; 
    $steps++; 
} 
$averagefuel = $averagefuel/$steps;