如何用SQL计算每位司机各状态的耗时（修正结果错位问题）

需要计算每位司机在各状态下的停留时长，已通过SQL计算时间差，但结果对应到了错误的司机和单元格中，请求修正SQL语句。

原始数据 #

期望输出 #

当前使用的SQL语句 #

select 
id,
driver_id,
driver_status,
driver_status.entry_time as cur_time,
LEAD(driver_status.entry_time) over (order by driver_status.id)  as next_time,
DATEDIFF(minute,driver_status.entry_time,lead(driver_status.entry_time) over (order by id))  as timespent in minutes
from driver_status;

修正方案 #

原SQL核心问题是未按司机分组，LEAD函数全局排序会跨司机取数据，导致时间差对应错误，同时存在字段名、语法错误。修正后的SQL如下：

SELECT
    id,
    driver_id,
    status,
    entry_time AS cur_time,
    LEAD(entry_time) OVER (PARTITION BY driver_id ORDER BY entry_time) AS next_time,
    CASE
        WHEN LEAD(entry_time) OVER (PARTITION BY driver_id ORDER BY entry_time) IS NOT NULL
        THEN DATEDIFF(minute, entry_time, LEAD(entry_time) OVER (PARTITION BY driver_id ORDER BY entry_time))
        ELSE '-'
    END AS `Output(time spend in minutes)`
FROM driver_status;

修改说明： #

1. 按司机分组：添加PARTITION BY driver_id，让LEAD函数仅在同一司机的记录中取下一条状态时间，避免跨司机匹配。
2. 排序逻辑：用ORDER BY entry_time保证按状态切换的时间顺序取数（若id与时间严格同步，也可用ORDER BY id）。
3. 字段修正：原SQL中driver_status是表名，字段应为status，去掉冗余的表名前缀（若存在表别名冲突可保留）。
4. 空值处理：用CASE WHEN将最后一条记录的NULL时间差转换为'-'，匹配期望输出格式。
5. 语法修正：修正别名的错误写法，将as timespent in minutes改为合法的别名格式。

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