如何在Cloud Spanner中关联下一行以计算驾驶员总里程

我来帮你搞定这个Cloud Spanner里计算驾驶员总里程的问题！首先明确你的需求：基于给定的车辆里程记录表，计算每位驾驶员的总行驶公里数，而且Cloud Spanner不支持ROW_NUMBER()、LAG()这类窗口函数，得换一种方式实现“关联下一行”的逻辑。

先回顾下你的表结构和数据：

表结构 #

vehicle_id | driver_id | odometer

示例数据 #

1     |      1    |     10  
     1     |      1    |     20  
     1     |      2    |     20  
     1     |      2    |     40  
     1     |      1    |     40  
     1     |      1    |     50  
     2     |      1    |     10  
     2     |      1    |     20  
     2     |      2    |     20  
     2     |      2    |     30  
     2     |      1    |     30 
     2     |      1    |     80 
     2     |      2    |     80 
     2     |      2    |     120 

预期结果 #

driver_id | total_mileage
      1    |     80  
      2    |     70  

解决方案：自关联+聚合函数模拟“下一行”逻辑 #

因为Cloud Spanner不支持窗口函数，我们可以用自关联+子查询聚合的方式来模拟获取下一行的效果，核心逻辑是：

• 为每条记录找到同一车辆中，里程数大于当前的最小里程（也就是“下一行”的里程）
• 只有当当前记录和下一行的驾驶员相同时，才计算里程差值
• 最后按驾驶员ID累加所有有效差值

对应的SQL语句如下：

SELECT
  t1.driver_id,
  SUM(CASE WHEN t1.driver_id = t2.driver_id THEN t2.odometer - t1.odometer ELSE 0 END) AS total_mileage
FROM
  your_table_name t1
LEFT JOIN (
  SELECT
    vehicle_id,
    odometer,
    -- 找到同一车辆中，比当前里程大的最小里程
    (SELECT MIN(odometer) FROM your_table_name t3 WHERE t3.vehicle_id = t2.vehicle_id AND t3.odometer > t2.odometer) AS next_odometer
  FROM
    your_table_name t2
) t_next ON t1.vehicle_id = t_next.vehicle_id AND t1.odometer = t_next.odometer
-- 关联到下一行的完整记录，拿到驾驶员ID
LEFT JOIN your_table_name t2 ON t_next.vehicle_id = t2.vehicle_id AND t_next.next_odometer = t2.odometer
GROUP BY
  t1.driver_id;

逻辑解释 #

1. 子查询t_next：为每条记录定位同一车辆的下一个里程点，用MIN(odometer)确保拿到的是当前记录之后的第一个里程值
2. 关联t2：通过t_next的next_odometer拿到下一行的完整记录，从而获取下一行的驾驶员ID
3. CASE判断：只有当前驾驶员和下一行驾驶员相同时，才计算里程差，否则不计入总和
4. 最终聚合：按驾驶员ID累加所有有效差值，得到总行驶里程

这个逻辑完全匹配你给出的中间计算结果，比如驾驶员1的总里程是10（10→20）+10（40→50）+10（10→20）+50（30→80）=80，驾驶员2的总里程是20（20→40）+10（20→30）+40（80→120）=70，和预期结果一致。

如果你的表中存在同一车辆下里程数重复的情况，建议补充一个有序的时间字段（比如记录创建时间）来更精准地定位“下一行”，避免歧义。

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