基于MS SQL Server按班次聚合动作日志数据的SQL查询需求

现有Action_Log表记录单名员工的操作及对应时间戳，无员工排班表，需仅依靠日志将操作按预设班次类型（ShiftA：11:00-23:00、ShiftB：13:00-21:00）聚合，区分不同班次编号。

原表结构及示例数据 #

期望输出 #

实现SQL查询 #

WITH ShiftInfo AS (
    SELECT 
        Action_ID,
        Action_DTTM,
        -- 优先匹配ShiftB，剩余符合ShiftA时段的归类为ShiftA
        CASE 
            WHEN CAST(Action_DTTM AS TIME) BETWEEN '13:00:00' AND '21:00:00' THEN 'ShiftB'
            WHEN CAST(Action_DTTM AS TIME) BETWEEN '11:00:00' AND '23:00:00' THEN 'ShiftA'
            -- 若存在超出两个班次的时间，可根据需求自定义逻辑，这里暂归为ShiftA
            ELSE 'ShiftA'
        END AS Shift_Type,
        CAST(Action_DTTM AS DATE) AS Action_Date
    FROM Action_Log
),
ShiftNumber AS (
    SELECT 
        Action_Date,
        Shift_Type,
        DENSE_RANK() OVER (ORDER BY Action_Date, Shift_Type) AS Shift_Number
    FROM ShiftInfo
    GROUP BY Action_Date, Shift_Type
)
SELECT 
    si.Action_ID,
    sn.Shift_Number,
    si.Shift_Type
FROM ShiftInfo si
JOIN ShiftNumber sn ON si.Action_Date = sn.Action_Date AND si.Shift_Type = sn.Shift_Type
ORDER BY si.Action_ID;

逻辑说明 #

1. ShiftInfo CTE：提取操作日期，将每个操作的时间转换为时段后，优先匹配ShiftB的时间范围，剩余符合ShiftA时段的操作归类为ShiftA；
2. ShiftNumber CTE：按日期+班次类型分组，用DENSE_RANK()生成连续的班次编号；
3. 最后关联两个CTE，输出每个操作对应的班次编号和类型。

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