计算卡车多站点Origin-Destination组合的Transit Time（支持X个后续站点）

核心前提说明 #

要实现“Origin到后续X个Destination”的筛选，首先得明确站点的先后顺序逻辑，通常分为两种常见情况：

情况一：所有卡车遵循固定全局站点路线 #

比如所有运输都走 A→B→C→D→E 的固定路线，此时可以预先定义每个站点的排序值（如A=1、B=2...），以此判断Destination是否属于Origin的后续X个站点。

SQL 实现 #

假设你有主运输数据表 truck_transport（包含Truck Number、Origin、Destination、Departure、Arrival），以及存储站点顺序的表 site_order（包含site和order_rank字段）：

WITH ranked_transits AS (
    SELECT 
        t.Truck_Number,
        t.Origin,
        o1.order_rank AS origin_rank,
        t.Destination,
        o2.order_rank AS dest_rank,
        -- 计算运输耗时，根据实际时间单位调整（这里用小时）
        DATEDIFF(hour, t.Departure, t.Arrival) AS Transit_Time
    FROM truck_transport t
    -- 关联站点顺序表，给Origin和Destination加上排序值
    JOIN site_order o1 ON t.Origin = o1.site
    JOIN site_order o2 ON t.Destination = o2.site
)
-- 筛选并计算平均耗时
SELECT 
    Origin,
    Destination,
    ROUND(AVG(Transit_Time), 2) AS Avg_Transit_Time
FROM ranked_transits
-- 替换这里的3为你需要的配置变量X
WHERE dest_rank <= origin_rank + 3
GROUP BY Origin, Destination
ORDER BY Origin, dest_rank;

Python Pandas 实现 #

import pandas as pd

# 加载数据
truck_data = pd.read_csv("truck_transport.csv", parse_dates=["Departure", "Arrival"])
# 定义全局站点顺序
site_order = pd.DataFrame({
    "site": ["A", "B", "C", "D", "E"],
    "order_rank": [1, 2, 3, 4, 5]
})

# 给Origin和Destination映射排序值
truck_data = truck_data.merge(
    site_order, left_on="Origin", right_on="site", how="left"
).rename(columns={"order_rank": "origin_rank"}).drop("site", axis=1)

truck_data = truck_data.merge(
    site_order, left_on="Destination", right_on="site", how="left"
).rename(columns={"order_rank": "dest_rank"}).drop("site", axis=1)

# 计算运输耗时（转为小时）
truck_data["Transit_Time"] = (truck_data["Arrival"] - truck_data["Departure"]).dt.total_seconds() / 3600

# 配置变量X
X = 3
# 筛选符合条件的运输组合
filtered_data = truck_data[truck_data["dest_rank"] <= truck_data["origin_rank"] + X]

# 计算每个组合的平均耗时
avg_transit = filtered_data.groupby(["Origin", "Destination"])["Transit_Time"].mean().reset_index()
print(avg_transit)

情况二：单卡车有独立运输路径序列 #

每个卡车的运输站点按Departure时间排序形成专属路径，“后续X个Destination”指该卡车从某Origin出发后，接下来X个到达的站点。

SQL 实现 #

WITH truck_trips AS (
    SELECT 
        Truck_Number,
        Origin,
        Destination,
        Departure,
        Arrival,
        DATEDIFF(hour, Departure, Arrival) AS Transit_Time,
        -- 给每个卡车的行程按出发时间排序，生成序列编号
        ROW_NUMBER() OVER (PARTITION BY Truck_Number ORDER BY Departure) AS trip_seq
    FROM truck_transport
),
origin_trip_seqs AS (
    -- 提取每个卡车每个Origin对应的行程序列位置
    SELECT 
        Truck_Number,
        Origin,
        trip_seq AS origin_trip_seq
    FROM truck_trips
)
SELECT 
    tt.Origin,
    tt.Destination,
    ROUND(AVG(tt.Transit_Time), 2) AS Avg_Transit_Time
FROM truck_trips tt
JOIN origin_trip_seqs ots 
    ON tt.Truck_Number = ots.Truck_Number AND tt.Origin = ots.Origin
-- 筛选同卡车中，当前行程在Origin行程之后的X个范围内
WHERE tt.trip_seq <= ots.origin_trip_seq + 3 -- 替换3为变量X
GROUP BY tt.Origin, tt.Destination
ORDER BY tt.Origin, tt.Destination;

Python Pandas 实现 #

import pandas as pd

truck_data = pd.read_csv("truck_transport.csv", parse_dates=["Departure", "Arrival"])

# 给每个卡车的行程按出发时间排序，生成序列编号
truck_data["trip_seq"] = truck_data.groupby("Truck_Number")["Departure"].rank(method="first", ascending=True).astype(int)

# 计算运输耗时
truck_data["Transit_Time"] = (truck_data["Arrival"] - truck_data["Departure"]).dt.total_seconds() / 3600

# 配置变量X
X = 3
# 合并每个Origin对应的行程序列位置
truck_data = truck_data.merge(
    truck_data[["Truck_Number", "Origin", "trip_seq"]].rename(columns={"trip_seq": "origin_trip_seq"}),
    on=["Truck_Number", "Origin"],
    how="left"
)

# 筛选后续X个站点的运输组合
filtered_data = truck_data[truck_data["trip_seq"] <= truck_data["origin_trip_seq"] + X]

# 计算平均耗时
avg_transit = filtered_data.groupby(["Origin", "Destination"])["Transit_Time"].mean().reset_index()
print(avg_transit)

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