最大和最小群由2列组成
我有下一张表，上面显示的是每次一辆车装满油箱的时候。它返回日期、汽车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 |这样做的目的是按月计算平均消耗(我不能按日计算，因为不是每辆车每天都装满油箱)。
为了做到这一点，我试着按月获得最大(里程)-min(里程)/sum(升)组。但这将只适用于一个具体的汽车和一个具体的月。
如果我尝试一个具体的车和几个月，最大和最小将无法正常返回。如果我把所有的车都加起来，更糟的是，它会假设最大和最小，就好像每辆车都是一样的。
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 |回答 3
Stack Overflow用户
发布于 2016-11-28 15:07:59
这是一个比看上去更复杂的问题。问题是你不想在几个月之间损失几英里。做这样的事情是很诱人的：
select year(date), month(date),
sum(liters) / (max(mileage) - min(mileage))
from Tanking
where Date >= dateadd(month, -5, getdate())
group by year(date), month(date);然而，这错过了英里和升跨越一个月的界限。此外，这个月的第一次记录上的升数是因为以前的英里差。糟了！那是不对的。
解决这个问题的一种方法是查找下一个值。该查询如下所示：
select year(date), month(date),
sum(next_liters) / (max(next_mileage) - min(mileage))
from (select t.*,
lead(date) over (partition by vehicle_id order by date) as next_date,
lead(mileage) over (partition by vehicle_id order by date) as next_mileage,
lead(liters) over (partition by vehicle_id order by date) as next_liters
from Tanking t
) t
where Date >= dateadd(month, -5, getdate())
group by year(date), month(date);这些查询使用简化的列名，因此转义字符不会干扰逻辑。
编辑：
哦，您有多辆车(可能是vehicle_Id的目的)。您需要两个级别的聚合。第一个查询如下所示：
select yyyy, mm, sum(liters) as liters, sum(mileage_diff) as mileage_diff,
sum(mileage_diff) / sum(liters) as mileage_per_liter
from (select vehicle_id, year(date) as yyyy, month(date) as mm,
sum(liters) as liters,
(max(mileage) - min(mileage)) as mileage_diff
from Tanking
where Date >= dateadd(month, -5, getdate())
group by vehicle_year(date), month(date)
) t
group by yyyy, mm;对第二个查询(在vehicle_id中使用partition by子句)的类似更改将适用于第二个版本。
Stack Overflow用户
发布于 2016-11-28 15:07:33
尝试在子查询中获取每个月每辆车的金额。然后使用子查询的值计算外部查询中每月的平均值：
select year_month,
(1.0*sum(liters_per_car)/sum(mileage_per_car))*100.0 as Litres_per_100KM
from (
select convert(char(7), [Date], 127) as year_month,
sum(Liters) as liters_per_car,
max(Mileage)-min(Mileage) as mileage_per_car
from Tanking
group by convert(char(7), [Date], 127), Vehicle_ID) as t
group by year_monthStack Overflow用户
发布于 2016-11-28 15:48:50
您可以使用CTE获取dif(里程)，然后计算消耗：
可以在这里查看：http://rextester.com/OKZO55169
with cte (car, datec, difm, liters)
as
(
select
car,
datec,
mileage - lag(mileage,1,mileage) over(partition by car order by car, mileage) as difm,
liters
from #consum
)
select
car,
year(datec) as [year],
month(datec) as [month],
((cast(sum(liters) as float)/cast(sum(difm) as float)) * 100.0) as [l_100km]
from
cte
group by
car, year(datec), month(datec)https://stackoverflow.com/questions/40847203
复制相似问题
腾讯云开发者
