基于DateTime生成跨夜间持久化的SQL会话Key

问题背景 #

现有一个时序数据集，需为每条记录生成ExpectedKey字段，核心难点是处理跨夜间的会话——同一会话可能跨越两天（如样例中1-20220101-23对应的会话从2022-01-01延续到2022-01-02），现有SQL无法正确识别这类跨天会话的分组逻辑。

数据表结构 #

CarId           
EventDateTime
Event
SessionFlag

数据表样例 #

现有尝试代码 #

CASE 
     WHEN SessionFlag<> 0 
     AND 
     SessionFlag= LAG(SessionFlag) OVER (PARTITION BY Carid ORDER BY EventDateTime) 
THEN FIRST_VALUE(CarId+'-'+Convert(CHAR(8),EventDateTime,112)+'-'+CAST(DATEPART(HOUR,EventDateTime)AS            
    VARCHAR))OVER (PARTITION BY CarId ORDER BY EventDateTime)
ELSE CarId+'-'+Convert(CHAR(8),EventDateTime,112)+'-'+CAST(DATEPART(HOUR,EventDateTime)AS VARCHAR) END AS SessionId

解决方案 #

核心思路是先为每个会话生成唯一分组ID，再基于会话的起始时间构建ExpectedKey，确保跨天会话的所有记录共享同一个Key。以下是兼容SQL Server的实现代码：

WITH SessionGroups AS (
    SELECT 
        *,
        -- 标记新会话的起始行：SessionFlag=1且前一条是0或为第一条记录
        CASE 
            WHEN SessionFlag = 1 
                 AND (LAG(SessionFlag) OVER (PARTITION BY CarId ORDER BY EventDateTime) = 0 
                      OR LAG(SessionFlag) OVER (PARTITION BY CarId ORDER BY EventDateTime) IS NULL)
            THEN 1 
            ELSE 0 
        END AS IsNewSession,
        -- 累计求和生成会话分组ID，同一会话的所有记录共享ID
        SUM(CASE 
                WHEN SessionFlag = 1 
                     AND (LAG(SessionFlag) OVER (PARTITION BY CarId ORDER BY EventDateTime) = 0 
                          OR LAG(SessionFlag) OVER (PARTITION BY CarId ORDER BY EventDateTime) IS NULL)
                THEN 1 
                ELSE 0 
            END) OVER (PARTITION BY CarId ORDER BY EventDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SessionGroupId
    FROM YourTableName
),
SessionStartTimes AS (
    SELECT 
        CarId,
        SessionGroupId,
        MIN(EventDateTime) AS SessionStartTime
    FROM SessionGroups
    GROUP BY CarId, SessionGroupId
)
SELECT 
    sg.CarId,
    sg.EventDateTime,
    sg.Event,
    sg.SessionFlag,
    CONCAT(
        sg.CarId, '-',
        CONVERT(CHAR(8), sst.SessionStartTime, 112), '-',
        DATEPART(HOUR, sst.SessionStartTime)
    ) AS ExpectedKey
FROM SessionGroups sg
JOIN SessionStartTimes sst 
    ON sg.CarId = sst.CarId 
    AND sg.SessionGroupId = sst.SessionGroupId
ORDER BY sg.CarId, sg.EventDateTime;

代码说明 #

1. SessionGroups CTE：通过LAG函数识别新会话的起始点，再用SUM累计生成会话分组ID，确保跨天的同一会话记录分到同一组。
2. SessionStartTimes CTE：按车辆和会话分组聚合，获取每个会话的起始时间——这是构建ExpectedKey的核心依据。
3. 最终查询：关联两个CTE，用会话起始时间拼接出符合要求的ExpectedKey，无论会话是否跨天，都以起始时间的日期和小时作为Key的组成部分。

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