SQL Server多表关联按CarType分组统计车辆油耗的正确实现方法
原SQL计算结果失真的核心踩坑点
- 一对多连接产生笛卡尔积数值膨胀:一台车可能对应多笔维保记录、多笔加油记录,直接将两张业务表和车辆主表关联时,两表的行数会交叉相乘,生成远大于实际的结果集,最终求和得到的维保成本、加油成本、加油量全是重复计算的虚高值。
- 里程统计维度错位:原写法直接在车型维度下取所有加油记录的最大、最小里程做差,没有逐车计算单台车辆自身的行驶里程,会把不同车辆的里程读数混算,完全不符合“先逐车统计指标、再按车型汇总”的规则要求。
- 缺少异常值防护:当某车型下所有车辆在统计周期内里程差为0时,计算百公里油耗的分母为0,会直接触发SQL运行报错。
- 时间边界存在截断漏洞:原写法的时间范围截止到
'2021-01-31 23:59:29',会漏掉该时间点到当日23:59:59之间的业务记录,导致统计范围不准确。
正确实现逻辑
严格按照两层聚合的规则计算,从根源规避上述问题:
- 车级粒度统计:先对每台车单独核算统计周期内的总维保成本、总加油量、总加油成本、单台自身的行驶里程(单台最大里程读数-单台最小里程读数),将一对多的业务表数据压缩为每车单行的统计结果后再关联车辆主表,彻底消除笛卡尔积。
- 车型粒度汇总:在车级统计结果的基础上,按
CarType分组,汇总同车型下所有车辆的各项指标,最后用汇总后的总加油量计算整体百公里油耗。
可直接运行的正确SQL
-- 定义统计周期变量,后续调整时间范围仅需修改变量值 DECLARE @StatStartDate date = '2021-01-01', @StatEndDate date = '2021-01-31'; WITH CarLevelAgg AS ( SELECT c.CarID, c.CarType, -- 无维保/加油记录时默认为0,避免空值干扰求和结果 ISNULL(agg_m.TotalMaintenanceCost, 0) AS SingleCarMaintenanceCost, ISNULL(agg_f.TotalFuelCost, 0) AS SingleCarFuelCost, ISNULL(agg_f.TotalFuelQuantity, 0) AS SingleCarFuelQuantity, ISNULL(agg_f.SingleCarTravelDistance, 0) AS SingleCarTravelDistance FROM Cars c -- 先聚合每台车的维保数据,确保关联时每车仅对应1条聚合结果 LEFT JOIN ( SELECT CarID, SUM(MaintenanceCost) AS TotalMaintenanceCost FROM Maintenances WHERE MaintenanceDate >= @StatStartDate AND MaintenanceDate < DATEADD(day, 1, @StatEndDate) -- 采用左开右闭的时间范围,无边界漏数 GROUP BY CarID ) agg_m ON agg_m.CarID = c.CarID -- 先聚合每台车的加油数据,确保关联时每车仅对应1条聚合结果 LEFT JOIN ( SELECT CarID, SUM(Cost) AS TotalFuelCost, SUM(Quantity) AS TotalFuelQuantity, MAX(Odometer) - MIN(Odometer) AS SingleCarTravelDistance FROM Fuelings WHERE FuelingDate >= @StatStartDate AND FuelingDate < DATEADD(day, 1, @StatEndDate) GROUP BY CarID ) agg_f ON agg_f.CarID = c.CarID ) SELECT CarType AS [车型], SUM(SingleCarMaintenanceCost) AS [总维护成本], SUM(SingleCarFuelCost) AS [总燃油成本], SUM(SingleCarFuelQuantity) AS [总加油量], SUM(SingleCarTravelDistance) AS [总行驶里程], -- 通过NULLIF将0里程转为空值,规避除零报错 (SUM(SingleCarFuelQuantity) * 100.0) / NULLIF(SUM(SingleCarTravelDistance), 0) AS [百公里油耗(L/100km)] FROM CarLevelAgg GROUP BY CarType;
结果校验
对SUV车型的计算逻辑完全匹配预期:逐车累加所有车辆的加油量得到总加油量301,逐车累加所有车辆的行驶里程得到总行驶里程1600,最终计算百公里油耗为(301 * 100)/1600 = 18.8125,和目标结果完全一致。
内容的提问来源于stack exchange,提问作者milo2011
相关产品推荐
相关产品推荐

