如何在SQL Server中无游标计算每位专家的平均服务时长

针对你的需求，完全可以通过基于集合的SQL操作实现，不需要使用游标。以下提供两种可靠的实现方案：

方案一：使用窗口函数LAG配对签到签出记录 #

利用LAG()窗口函数，按专家ID、订单ID分组并按操作时间排序，直接为每条签出记录匹配对应的上一条签到记录，计算单次服务时长后再求平均值：

WITH ServiceDurations AS (
    SELECT
        ExpertId,
        -- 计算签出与对应签到的时间差（单位：秒）
        DATEDIFF(SECOND, 
                 LAG(CheckinoutTime) OVER (PARTITION BY ExpertId, OrderId ORDER BY CheckinoutTime), 
                 CheckinoutTime) AS DurationSeconds
    FROM dbo.OrderCheckInOutLogs
    WHERE OnCheckinoutStatus = 1 -- 仅处理签出记录
)
SELECT
    ExpertId,
    -- 转换为时分秒格式的平均时长
    CONVERT(VARCHAR, DATEADD(SECOND, AVG(DurationSeconds), 0), 108) AS AverageServiceDuration,
    -- 也可直接返回平均秒数
    AVG(DurationSeconds) AS AverageDurationSeconds
FROM ServiceDurations
WHERE DurationSeconds IS NOT NULL -- 排除无对应签到的签出记录
GROUP BY ExpertId;

方案说明： #

• PARTITION BY ExpertId, OrderId确保只在同一专家的同一订单内匹配签到签出
• LAG(CheckinoutTime)获取当前签出记录的上一条操作时间（即对应签到时间）
• 过滤掉DurationSeconds为NULL的记录，避免没有对应签到的签出数据影响平均值

方案二：使用JOIN+EXISTS精准配对签到签出 #

通过内连接关联签到和签出记录，并用EXISTS确保每个签出记录匹配最近的签到记录，避免一对多的错误关联：

SELECT
    c.ExpertId,
    AVG(DATEDIFF(SECOND, c.CheckinoutTime, co.CheckinoutTime)) AS AverageDurationSeconds,
    CONVERT(VARCHAR, DATEADD(SECOND, AVG(DATEDIFF(SECOND, c.CheckinoutTime, co.CheckinoutTime)), 0), 108) AS AverageServiceDuration
FROM dbo.OrderCheckInOutLogs c
INNER JOIN dbo.OrderCheckInOutLogs co
    ON c.ExpertId = co.ExpertId
    AND c.OrderId = co.OrderId
    AND c.OnCheckinoutStatus = 0 -- c是签到记录
    AND co.OnCheckinoutStatus = 1 -- co是签出记录
    AND co.CheckinoutTime > c.CheckinoutTime
-- 确保当前签到是签出之前最近的一次签到
AND NOT EXISTS (
    SELECT 1
    FROM dbo.OrderCheckInOutLogs c2
    WHERE c2.ExpertId = c.ExpertId
      AND c2.OrderId = c.OrderId
      AND c2.OnCheckinoutStatus = 0
      AND c2.CheckinoutTime > c.CheckinoutTime
      AND c2.CheckinoutTime < co.CheckinoutTime
)
GROUP BY c.ExpertId;

方案说明： #

• 内连接直接关联同一专家同一订单的签到和签出记录
• NOT EXISTS子句排除中间存在其他签到记录的情况，保证配对的是最近的签到与签出
• 同样支持转换为时分秒格式或保留秒数的平均时长

两种方案均为纯集合操作，性能远优于游标，适合SQL Server的批量数据处理场景。

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