如何查找司机行程数据中基于起止时间的重叠记录

嘿，这个需求在调度类数据清洗里太常见了！我来帮你一步步搞定——核心就是精准识别同一司机的时间重叠行程，然后把异常的挑出来，留下符合逻辑的正常记录。

首先得明确重叠的判断逻辑：对于同一司机的两段行程A和B，只要满足 A.start_datetime < B.end_datetime 并且 A.end_datetime > B.start_datetime，就属于时间重叠的异常情况（毕竟同一时段司机只能开一辆车）。

下面给你两种主流工具的解决方案，你可以根据自己的数据集存储方式选择：

1. SQL 解决方案（适用于数据库中的数据集） #

假设你的表名为 trips，核心字段包括：trip_id（唯一行程ID）、driver_id（司机ID）、vehicle_id（车辆ID）、start_datetime（行程开始时间）、end_datetime（行程结束时间）。

方法1：自连接找出所有重叠行程 #

通过自连接配对同一司机的行程，筛选出时间重叠的组合，再提取所有涉及重叠的行程ID：

WITH overlapping_pairs AS (
    SELECT 
        t1.trip_id AS trip_a,
        t2.trip_id AS trip_b,
        t1.driver_id,
        t1.vehicle_id AS vehicle_a,
        t2.vehicle_id AS vehicle_b,
        t1.start_datetime AS start_a,
        t1.end_datetime AS end_a,
        t2.start_datetime AS start_b,
        t2.end_datetime AS end_b
    FROM trips t1
    JOIN trips t2 
        ON t1.driver_id = t2.driver_id
        AND t1.trip_id < t2.trip_id -- 避免重复配对（比如A&B和B&A）
        AND t1.start_datetime < t2.end_datetime
        AND t1.end_datetime > t2.start_datetime
)
-- 提取所有涉及重叠的行程ID（去重）
SELECT DISTINCT trip_id 
FROM (
    SELECT trip_a AS trip_id FROM overlapping_pairs
    UNION ALL
    SELECT trip_b AS trip_id FROM overlapping_pairs
) AS all_overlapping;

拿到这些重叠行程ID后，你可以用 NOT IN 过滤原表得到正常行程，或者单独导出异常记录分析。

方法2：窗口函数标记行程状态 #

用 LAG 窗口函数对比当前行程和上一行（同司机）的结束时间，快速给每一行标记是否重叠：

SELECT 
    *,
    CASE 
        WHEN start_datetime < LAG(end_datetime) OVER (PARTITION BY driver_id ORDER BY start_datetime)
        THEN 'Overlapping'
        ELSE 'Normal'
    END AS trip_status
FROM trips
ORDER BY driver_id, start_datetime;

注意：如果遇到连续多段重叠的情况（比如A和B重叠，B和C重叠），这个方法会标记B和C，但A的标记需要结合前一行数据，若要覆盖所有连续重叠场景，可以再用 LEAD 函数对比下一行的开始时间。

2. Python Pandas 解决方案（适用于本地数据集） #

假设你的数据已读入为DataFrame df，字段与SQL表一致。

步骤1：先排序分组 #

按司机ID和行程开始时间排序，确保同司机的行程按时间顺序排列：

import pandas as pd

df = df.sort_values(by=['driver_id', 'start_datetime']).reset_index(drop=True)

步骤2：标记重叠行程 #

通过 shift 函数获取同司机的前一行结束时间、后一行开始时间，判断当前行程是否与前后行程重叠：

# 获取同司机上一行的结束时间
df['prev_end'] = df.groupby('driver_id')['end_datetime'].shift(1)
# 获取同司机下一行的开始时间
df['next_start'] = df.groupby('driver_id')['start_datetime'].shift(-1)

# 标记是否重叠：要么和前一行重叠，要么和后一行重叠
df['is_overlapping'] = df.apply(
    lambda row: 
        (row['start_datetime'] < row['prev_end'] if pd.notna(row['prev_end']) else False)
        | (row['end_datetime'] > row['next_start'] if pd.notna(row['next_start']) else False),
    axis=1
)

步骤3：筛选数据 #

# 提取正常行程
normal_trips = df[~df['is_overlapping']]
# 提取重叠异常行程
overlapping_trips = df[df['is_overlapping']]

额外注意事项 #

• 时间格式要统一：确保 start_datetime 和 end_datetime 是datetime类型，而非字符串，否则时间比较会出错。
• 边界值调整：如果业务上认为“行程结束时间刚好等于另一行程开始时间”不算重叠，可以把判断条件中的 < 改为 <=，> 改为 >=，根据需求灵活调整。
• 重复行程处理：如果存在完全重复的行程（司机、车辆、时间都一致），建议先去重再判断重叠。

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