如何计算卡车地理围栏事件时间差并处理不匹配记录？

第一步：模式校验（过滤无效记录） #

核心是为每个Truck ID+Geolocation Event的组合，匹配合法的ENTERING-EXITING事件对，排除无对应配对的孤立事件、重复事件以及标注+的记录，具体实现分两种常用场景：

场景1：用SQL实现 #

通过窗口函数排序后配对相邻的合法事件：

-- 第一步：排序并标记行号
WITH sorted_events AS (
    SELECT 
        "Truck ID",
        "Trigger Event",
        "Geolocation Event",
        "Position Time UTC",
        ROW_NUMBER() OVER (
            PARTITION BY "Truck ID", "Geolocation Event" 
            ORDER BY "Position Time UTC"
        ) AS row_num
    FROM truck_events
    WHERE "Trigger Event" IN ('ENTERING', 'EXITING') -- 先排除带+的无效记录
),
-- 第二步：配对合法的ENTERING-EXITING事件对
paired_events AS (
    SELECT 
        s1."Truck ID",
        s1."Geolocation Event",
        s1."Position Time UTC" AS enter_time,
        s2."Position Time UTC" AS exit_time,
        -- 计算单次停留时长（这里以分钟为单位，可按需调整）
        DATEDIFF(minute, s1."Position Time UTC", s2."Position Time UTC") AS stay_minutes
    FROM sorted_events s1
    JOIN sorted_events s2 
        ON s1."Truck ID" = s2."Truck ID"
        AND s1."Geolocation Event" = s2."Geolocation Event"
        AND s1.row_num = s2.row_num - 1
        AND s1."Trigger Event" = 'ENTERING'
        AND s2."Trigger Event" = 'EXITING'
)
-- 第三步：按日期和卡车ID统计累计停留时间
SELECT 
    "Truck ID",
    CAST(enter_time AS DATE) AS event_date,
    "Geolocation Event",
    SUM(stay_minutes) AS total_stay_minutes
FROM paired_events
GROUP BY "Truck ID", CAST(enter_time AS DATE), "Geolocation Event";

场景2：用Python Pandas实现 #

通过分组排序+遍历校验，处理更复杂的异常情况：

import pandas as pd

# 读取原始数据
df = pd.read_csv('truck_events.csv')

# 先排除标注+的无效记录
df = df[df['Trigger Event'].isin(['ENTERING', 'EXITING'])]

# 按卡车、地理事件分组，按时间排序
df_sorted = df.sort_values(by=['Truck ID', 'Geolocation Event', 'Position Time UTC'])

# 自定义分组校验函数：只保留合法的ENTERING-EXITING配对
def validate_pairs(group):
    valid_pairs = []
    pending_enter = None
    for _, row in group.iterrows():
        if row['Trigger Event'] == 'ENTERING':
            pending_enter = row['Position Time UTC']
        elif row['Trigger Event'] == 'EXITING' and pending_enter is not None:
            valid_pairs.append((pending_enter, row['Position Time UTC']))
            pending_enter = None
    return pd.DataFrame(valid_pairs, columns=['Enter Time', 'Exit Time'])

# 执行分组校验与配对
paired_df = df_sorted.groupby(['Truck ID', 'Geolocation Event']).apply(validate_pairs).reset_index()

# 计算停留时长并统计
paired_df['Stay Duration'] = paired_df['Exit Time'] - paired_df['Enter Time']
result = paired_df.groupby([
    'Truck ID', 
    paired_df['Enter Time'].dt.date, 
    'Geolocation Event'
])['Stay Duration'].sum().reset_index()
result.rename(columns={'Enter Time': 'Event Date'}, inplace=True)

print(result)

关键逻辑说明 #

• 先按Truck ID和Geolocation Event分组，确保只在同一卡车、同一地理事件内配对
• 按时间排序后，通过状态追踪（如pending_enter变量）或行号匹配，确保每个ENTERING都有对应的后续EXITING
• 直接过滤掉孤立的ENTERING/EXITING以及标注+的记录，只保留合法配对用于后续统计

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