如何使用T-SQL计算同一ID下相邻出发、到达记录的时间间隔

以下代码默认你的表名为TripRecords，可根据实际表名替换。

1. 计算每段行程的分钟级时长 #

WITH RankedTrips AS (
    SELECT 
        Id,
        Date,
        Reason,
        -- 同ID下按时间升序排序打行号
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Date) AS RowNum
    FROM TripRecords
)
SELECT 
    d.Id,
    DATEDIFF(MINUTE, d.Date, a.Date) AS [时长（分钟）]
FROM RankedTrips d
-- 匹配同ID下紧邻的下一条记录
INNER JOIN RankedTrips a 
    ON d.Id = a.Id 
    AND d.RowNum + 1 = a.RowNum
-- 确保前一条是出发、后一条是到达
WHERE d.Reason = 'Departure' 
AND a.Reason = 'Arrival'
ORDER BY d.Id, d.RowNum;

执行后会输出和你示例完全一致的单段行程时长结果。

2. 统计每个ID的平均行程时长 #

WITH RankedTrips AS (
    SELECT 
        Id,
        Date,
        Reason,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Date) AS RowNum
    FROM TripRecords
),
TripDurations AS (
    -- 先计算所有单段行程时长
    SELECT 
        d.Id,
        DATEDIFF(MINUTE, d.Date, a.Date) AS Duration
    FROM RankedTrips d
    INNER JOIN RankedTrips a 
        ON d.Id = a.Id 
        AND d.RowNum + 1 = a.RowNum
    WHERE d.Reason = 'Departure' 
    AND a.Reason = 'Arrival'
)
-- 按ID分组求平均时长
SELECT 
    Id,
    AVG(Duration) AS [平均行程时长（分钟）]
FROM TripDurations
GROUP BY Id;

注意：上述逻辑默认表中同ID的记录是出发、到达严格交替的正常数据，如果存在连续出发/连续到达的异常记录，可额外加过滤逻辑剔除异常数据。

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