我有一个表格,显示每次汽车加满油箱的时间,返回日期、汽车ID、此时行驶里程和加注的升数:
| Date | Vehicle_ID | Mileage | Liters |
| 2016-10-20 | 234 | 123456 | 100 |
| 2016-10-20 | 345 | 458456 | 215 |
| 2016-10-20 | 323 | 756456 | 265 |
| 2016-10-25 | 234 | 123800 | 32 |
| 2016-10-26 | 345 | 459000 | 15 |
| 2016-10-26 | 323 | 756796 | 46 |
这个想法是计算每个月的平均耗油量(我无法按天计算,因为不是每辆汽车都每天加满油)。
为了得到这个结果,我尝试按照月份分组并计算 max(mileage)-min(mileage)/sum(liters)。但是这只适用于特定的一辆车和一个月。
如果我想要针对一辆车和多个月进行计算,最大值和最小值将无法正确返回。如果将所有车辆加起来,则更糟,因为它将假设最大值和最小值与每辆车相同。
select convert(char(7), Date, 127) as year_month,
sum("Liters tanked")/(max("Mileage")-min("Mileage"))*100 as Litres_per_100KM
from Tanking
where convert(varchar(10),"Date",23) >= DATEADD(mm, -5, GETDATE())
group by convert(char(7), Date, 127)
这种方式是行不通的,因为它会假设所有汽车的最大里程和最小里程相同。
"工作流程"应该是这样的: - 对于每个月份,获取每辆汽车的最大和最小里程数。计算最大值减去最小值以得到该月行驶的里程数。将每辆汽车的里程数相加以得到所有汽车的总行驶里程数。将加注的升数相加。将总加注的升数除以总里程数。
我该怎么得到结果:
| YearMonth | Average |
| 2016-06 | 30 |
| 2016-07 | 32 |
| 2016-08 | 46 |
| 2016-09 | 34 |
2016-09
、2016-08
、2016-07
和2016-06
的行。但是示例数据中没有这些月份的记录。您能否更新一下,以便我们有可操作的数据? - David Rushton