车辆油耗追踪系统：部分加油场景下的燃油效率计算问题

问题背景 #

在车辆加油记录追踪系统中，计算燃油效率时需要处理**部分加油（partial_fillup=1）**的场景：只有当再次加满油箱（partial_fillup=0）时，才计算从上一次加满以来的燃油效率——用当前里程表读数减去上一次加满时的读数，除以这段时间内的总加油量。

样例数据如下：

原有的满油场景SQL仅适用于每次都加满的情况，无法处理部分加油的场景。

解决方案 #

方法1：使用窗口函数（MySQL 8.0+/PostgreSQL等支持窗口函数的数据库） #

利用LAST_VALUE窗口函数标记每条记录对应的最近一次加满油的里程，再按该里程分组计算周期内的总里程差和总燃油量：

WITH readings_with_last_full AS (
    SELECT
        odometer,
        fuel,
        partial_fillup,
        -- 标记每条记录对应的最近一次加满油的里程
        LAST_VALUE(CASE WHEN partial_fillup = 0 THEN odometer ELSE NULL END) 
            IGNORE NULLS OVER (ORDER BY odometer) AS last_full_odometer
    FROM fuel
    WHERE vehicle_id = 1 AND odometer >= 61290
    ORDER BY odometer
),
period_stats AS (
    -- 计算每个周期的总里程差和总燃油量
    SELECT
        last_full_odometer,
        MAX(odometer) AS current_full_odometer,
        SUM(fuel) AS total_fuel_used
    FROM readings_with_last_full
    GROUP BY last_full_odometer
)
-- 仅在加满油的记录行展示燃油效率计算结果
SELECT
    r.odometer,
    ps.current_full_odometer - ps.last_full_odometer AS distance,
    r.fuel,
    ROUND((ps.current_full_odometer - ps.last_full_odometer)/ps.total_fuel_used, 2) AS mpg,
    r.partial_fillup
FROM readings_with_last_full r
JOIN period_stats ps ON r.last_full_odometer = ps.last_full_odometer
WHERE r.partial_fillup = 0
ORDER BY r.odometer;

方法2：使用变量（兼容老版本MySQL） #

如果数据库不支持窗口函数，可用变量手动标记最近一次加满油的里程，再分组计算：

SET @last_full_odometer = NULL;

-- 仅返回加满油时的燃油效率计算结果
SELECT
    r.odometer,
    ps.current_full_odometer - ps.last_full_odometer AS distance,
    r.fuel,
    ROUND((ps.current_full_odometer - ps.last_full_odometer)/ps.total_fuel_used, 2) AS mpg,
    r.partial_fillup
FROM (
    -- 为每条记录标记最近一次加满的里程
    SELECT
        odometer,
        fuel,
        partial_fillup,
        @last_full_odometer := CASE WHEN partial_fillup = 0 THEN odometer ELSE @last_full_odometer END AS last_full_odometer
    FROM (SELECT * FROM fuel WHERE vehicle_id = 1 AND odometer >= 61290 ORDER BY odometer) AS sorted_readings
) AS r
JOIN (
    -- 计算每个周期的统计数据
    SELECT
        last_full_odometer,
        MAX(odometer) AS current_full_odometer,
        SUM(fuel) AS total_fuel_used
    FROM (
        SELECT
            odometer,
            fuel,
            partial_fillup,
            @last_full_odometer := CASE WHEN partial_fillup = 0 THEN odometer ELSE @last_full_odometer END AS last_full_odometer
        FROM (SELECT * FROM fuel WHERE vehicle_id = 1 AND odometer >= 61290 ORDER BY odometer) AS sorted_readings
    ) AS temp
    GROUP BY last_full_odometer
) AS ps ON r.last_full_odometer = ps.last_full_odometer
WHERE r.partial_fillup = 0
ORDER BY r.odometer;

逻辑说明 #

1. 标记周期归属：通过窗口函数或变量，为每条记录绑定最近一次加满油的里程，将同一个周期（上一次加满→本次加满）的记录归为一组。
2. 计算周期统计：按周期分组，计算该周期的总行驶里程（本次加满里程 - 上一次加满里程）和总加油量。
3. 输出结果：仅在加满油的记录行展示该周期的燃油效率，部分加油的记录不参与计算（如需展示所有记录可去掉WHERE r.partial_fillup = 0，并为非加满行的计算字段设为NULL）。

内容的提问来源于stack exchange，提问作者Rod