使用SQL的CROSS APPLY实现订单2小时班次分组的技术求助

原始数据 #

需求 #

每笔订单对应2小时薪资，但同一2小时窗口内的多笔订单仅按1个班次结算；若订单超出当前班次的2小时窗口，则创建新班次，该订单归入新班次。

预期输出 #

问题 #

尝试用CROSS APPLY实现，但逻辑存在缺失，无法处理如订单5这类不在前一订单班次窗口内的情况，希望避免循环处理，寻求正确实现方案。

解决方案（SQL Server） #

可以使用递归CTE实现班次分组，无需循环：

场景1：Order ID连续递增

WITH OrderTimes AS (
    -- 将时间转换为可计算的datetime类型（日期可根据实际业务调整）
    SELECT 
        OrderID,
        StartTime = CAST(CONVERT(varchar(10), GETDATE(), 120) + ' ' + StartTime AS datetime)
    FROM YourOrdersTable
),
ShiftGroups AS (
    -- 初始化第一个班次
    SELECT 
        OrderID,
        StartTime,
        ShiftStart = StartTime,
        ShiftEnd = DATEADD(HOUR, 2, StartTime),
        ShiftNumber = 1
    FROM OrderTimes
    WHERE OrderID = (SELECT MIN(OrderID) FROM OrderTimes)

    UNION ALL

    -- 递归处理后续订单
    SELECT 
        ot.OrderID,
        ot.StartTime,
        -- 判断当前订单是否在前一班次窗口内，是则沿用原班次开始时间，否则启用新班次
        ShiftStart = CASE WHEN ot.StartTime <= sg.ShiftEnd THEN sg.ShiftStart ELSE ot.StartTime END,
        ShiftEnd = CASE WHEN ot.StartTime <= sg.ShiftEnd THEN sg.ShiftEnd ELSE DATEADD(HOUR, 2, ot.StartTime) END,
        -- 开启新班次则序号+1，否则沿用原序号
        ShiftNumber = CASE WHEN ot.StartTime <= sg.ShiftEnd THEN sg.ShiftNumber ELSE sg.ShiftNumber + 1 END
    FROM OrderTimes ot
    JOIN ShiftGroups sg ON ot.OrderID = sg.OrderID + 1
)
-- 格式化时间为HH:mm格式输出
SELECT 
    OrderID,
    StartTime = FORMAT(StartTime, 'HH:mm'),
    ShiftStart = FORMAT(ShiftStart, 'HH:mm'),
    EndTime = FORMAT(ShiftEnd, 'HH:mm'),
    ShiftNumber AS [Shift #]
FROM ShiftGroups
ORDER BY OrderID;

场景2：Order ID不连续（按Start Time排序）

如果Order ID不是连续递增的，先按时间排序生成连续序号再递归：

WITH OrderedOrders AS (
    SELECT 
        OrderID,
        StartTime = CAST(CONVERT(varchar(10), GETDATE(), 120) + ' ' + StartTime AS datetime),
        RowNum = ROW_NUMBER() OVER (ORDER BY StartTime)
    FROM YourOrdersTable
),
ShiftGroups AS (
    -- 初始化第一个班次
    SELECT 
        OrderID,
        StartTime,
        ShiftStart = StartTime,
        ShiftEnd = DATEADD(HOUR, 2, StartTime),
        ShiftNumber = 1,
        RowNum
    FROM OrderedOrders
    WHERE RowNum = 1

    UNION ALL

    -- 递归处理后续订单
    SELECT 
        oo.OrderID,
        oo.StartTime,
        ShiftStart = CASE WHEN oo.StartTime <= sg.ShiftEnd THEN sg.ShiftStart ELSE oo.StartTime END,
        ShiftEnd = CASE WHEN oo.StartTime <= sg.ShiftEnd THEN sg.ShiftEnd ELSE DATEADD(HOUR, 2, oo.StartTime) END,
        ShiftNumber = CASE WHEN oo.StartTime <= sg.ShiftEnd THEN sg.ShiftNumber ELSE sg.ShiftNumber + 1 END,
        oo.RowNum
    FROM OrderedOrders oo
    JOIN ShiftGroups sg ON oo.RowNum = sg.RowNum + 1
)
-- 格式化输出
SELECT 
    OrderID,
    StartTime = FORMAT(StartTime, 'HH:mm'),
    ShiftStart = FORMAT(ShiftStart, 'HH:mm'),
    EndTime = FORMAT(ShiftEnd, 'HH:mm'),
    ShiftNumber AS [Shift #]
FROM ShiftGroups
ORDER BY RowNum;

方案说明 #

• 递归CTE逐笔判断当前订单是否落在上一个班次的2小时窗口内，动态生成班次信息，完全避免循环处理，效率更高。
• 时间转换部分可根据实际业务场景调整（比如订单包含具体日期，直接用原datetime字段即可）。

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