按两列分组的最大值和最小值

Question

按两列分组的最大值和最小值

3

我有一个表格，显示每次汽车加满油箱的时间，返回日期、汽车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      |

- valter.vx

我白天做不到，因为并不是每辆车每天都要加满油箱。难道日消耗量不就是月消耗量除以大约30吗？ - Strawberry

嗨，Strawberry。不是的。如果我想要“按天平均”，它会像那样工作。但是我所说的每日是指“第1天 - X的平均值”，“第2天 - Y的平均值”等。 - valter.vx

输出包括2016-09、2016-08、2016-07和2016-06的行。但是示例数据中没有这些月份的记录。您能否更新一下，以便我们有可操作的数据？ - David Rushton

你需要总共两个查询。你需要按月份、年份和车辆分组，以获取每辆车的最小/最大里程和燃油消耗量。一旦你知道了每辆车每个月的里程数，你就可以计算每月的总里程数/总燃料消耗量。 - Patrick Tucci

3个回答

0

尝试在子查询中获取每辆车每月的总和。然后使用子查询的值，在外部查询中计算每月的平均值：

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_month

- Giorgos Betsos

0

您可以使用公共表达式（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)

- McNets

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

这是一个比看起来更复杂的问题。问题在于您不希望在月份之间失去里程数。做类似以下操作是很诱人的：

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;

对于第二个版本，类似的更改可以应用到第二个查询中（在partition by子句中使用vehicle_id）。