如何基于里程计算每日行驶距离？解决跨日里程缺失问题

我来帮你搞定这个问题！你之前只取当日MAX-MIN里程的方法，确实会漏掉一些场景——比如当天车辆只有一条里程记录时，就没法算出实际行驶距离。这时候我们需要结合**前一天的最新里程（也就是前一天最后一次记录的里程）**来计算，下面是具体的实现思路和SQL代码：

核心思路 #

1. 先按车辆和日期分组，获取每日的最大里程（代表当天最后一次记录的里程）和最小里程（当天第一次记录的里程）；
2. 使用窗口函数LAG()，按车辆分组、日期排序，获取每个日期的前一天的最大里程（即前一天的最新里程）；
3. 分情况计算当日行驶距离：
   • 如果存在前一天的里程数据，用当日最大里程减去前一天的最新里程；
   • 如果是车辆的第一个记录日期，就用当日最大里程减去当日最小里程（即当天的起止里程差）。

具体SQL实现 #

WITH daily_mileage AS (
    -- 第一步：获取每日的最大、最小里程
    SELECT
        vehicle_id,
        date_int,
        MAX(mileage) AS daily_max,
        MIN(mileage) AS daily_min
    FROM vehicle_mileage
    GROUP BY vehicle_id, date_int
),
previous_day_summary AS (
    -- 第二步：获取前一天的最新里程（前一天的daily_max）
    SELECT
        *,
        LAG(daily_max) OVER (
            PARTITION BY vehicle_id 
            ORDER BY date_int
        ) AS prev_day_last_mileage
    FROM daily_mileage
)
-- 第三步：计算每日行驶距离
SELECT
    vehicle_id,
    date_int,
    CASE
        WHEN prev_day_last_mileage IS NOT NULL 
            THEN daily_max - prev_day_last_mileage
        ELSE daily_max - daily_min
    END AS daily_distance
FROM previous_day_summary
ORDER BY vehicle_id, date_int;

额外说明 #

• 如果你实际场景中用的是标准日期类型（比如DATE或DATETIME），只需要把date_int替换成对应的日期字段即可，ORDER BY的逻辑完全适用；
• 如果需要处理“当天无里程记录”的场景（比如车辆全天未行驶，没有数据），可以结合一个日期维度表做左连接，确保所有日期都能被统计到——不过这个是扩展需求，你可以根据实际情况调整。

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