基于DateTime在Snowflake SQL中构建高效会话逻辑的技术求助

需求说明 #

• 基于EVENTDATETIME字段，按USERID、DEVICEID分组构建会话逻辑
• 同一USERID+DEVICEID组合下：
  • 会话从当日首次事件时间启动，无需固定整点开始
  • 当前会话结束（事件间隔超过1小时）后自动开启新会话
  • 每个会话的起始事件需分配唯一ID

原实现问题 #

原递归CTE方案中，递归部分无法使用聚合/窗口函数，导致逻辑无法正常执行。目前仅能通过循环实现，需要更高效的基于自连接/窗口函数的方案。

原递归代码：

WITH CTEA AS (
    SELECT 1 AS USERID, 100 AS DEVICEID , '2024-05-02 01:01:00' AS EVENTDATETIME UNION ALL
    SELECT 1 AS USERID, 100 AS DEVICEID , '2024-05-02 01:02:00' UNION ALL
    SELECT 1 AS USERID, 100 AS DEVICEID , '2024-05-02 01:58:00' UNION ALL
    SELECT 1 AS USERID, 100 AS DEVICEID , '2024-05-02 02:01:00' UNION ALL
    SELECT 2 AS USERID, 200 AS DEVICEID , '2024-05-02 01:03:00' UNION ALL
    SELECT 2 AS USERID, 200 AS DEVICEID , '2024-05-02 01:04:00' UNION ALL
    SELECT 2 AS USERID, 200 AS DEVICEID , '2024-05-02 02:05:00' UNION ALL
    SELECT 2 AS USERID, 200 AS DEVICEID , '2024-05-02 02:06:00' UNION ALL
    SELECT 2 AS USERID, 200 AS DEVICEID , '2024-05-02 02:01:00' UNION ALL
    SELECT 1 AS USERID, 200 AS DEVICEID , '2024-05-02 03:02:00' 
),
CTE_SESSION AS (
-- 锚点查询
    SELECT 
            UUID_STRING() AS SESSIONID        
            ,USERID
            ,DEVICEID
            ,MIN(EVENTDATETIME) AS  EVENTDATETIME
        FROM CTEA
        GROUP BY
            USERID
            ,DEVICEID
) T
UNION ALL
-- 递归部分：此处无法使用聚合/窗口函数，导致逻辑失效
        SELECT
            UUID_STRING() AS SESSIONID
            ,FM.USERID 
            ,FM.DEVICEID
            ,MIN(FM.EVENTDATETIME) AS EVENTDATETIME
           FROM CTE_SESSION CSS 
        LEFT JOIN  CTEA FM 
            ON CSS.USERID = FM.USERID
                AND CSS.DEVICEID = FM.DEVICEID
        WHERE COALESCE(FM.EVENTDATETIME, GETDATE()) > DATEADD(HOUR, 1, CSS.EVENTDATETIME) 
            AND FM.USERID IS NOT NULL  
        GROUP BY 
            FM.USERID 
            ,FM.DEVICEID
)
SELECT * FROM CTE_SESSION

高效解决方案（窗口函数实现） #

通过窗口函数替代递归，逻辑清晰且执行效率更高：

WITH CTEA AS (
    SELECT 1 AS USERID, 100 AS DEVICEID , '2024-05-02 01:01:00' AS EVENTDATETIME UNION ALL
    SELECT 1 AS USERID, 100 AS DEVICEID , '2024-05-02 01:02:00' UNION ALL
    SELECT 1 AS USERID, 100 AS DEVICEID , '2024-05-02 01:58:00' UNION ALL
    SELECT 1 AS USERID, 100 AS DEVICEID , '2024-05-02 02:01:00' UNION ALL
    SELECT 2 AS USERID, 200 AS DEVICEID , '2024-05-02 01:03:00' UNION ALL
    SELECT 2 AS USERID, 200 AS DEVICEID , '2024-05-02 01:04:00' UNION ALL
    SELECT 2 AS USERID, 200 AS DEVICEID , '2024-05-02 02:05:00' UNION ALL
    SELECT 2 AS USERID, 200 AS DEVICEID , '2024-05-02 02:06:00' UNION ALL
    SELECT 2 AS USERID, 200 AS DEVICEID , '2024-05-02 02:01:00' UNION ALL
    SELECT 1 AS USERID, 200 AS DEVICEID , '2024-05-02 03:02:00' 
),
-- 1. 按用户+设备分组排序，判断是否为新会话起始
CTE_EVENT_ORDERED AS (
    SELECT 
        USERID,
        DEVICEID,
        EVENTDATETIME,
        CASE 
            -- 首次事件或与上一事件间隔超过1小时，标记为新会话
            WHEN DATEDIFF(HOUR, LAG(EVENTDATETIME) OVER (PARTITION BY USERID, DEVICEID ORDER BY EVENTDATETIME), EVENTDATETIME) > 1
            OR LAG(EVENTDATETIME) OVER (PARTITION BY USERID, DEVICEID ORDER BY EVENTDATETIME) IS NULL
            THEN 1 
            ELSE 0 
        END AS IS_NEW_SESSION
    FROM CTEA
),
-- 2. 累加新会话标识，生成会话组ID
CTE_SESSION_GROUPS AS (
    SELECT 
        USERID,
        DEVICEID,
        EVENTDATETIME,
        SUM(IS_NEW_SESSION) OVER (PARTITION BY USERID, DEVICEID ORDER BY EVENTDATETIME) AS SESSION_GROUP_ID
    FROM CTE_EVENT_ORDERED
)
-- 3. 按会话组聚合，提取会话起始时间并生成唯一ID
SELECT 
    USERID,
    DEVICEID,
    MIN(EVENTDATETIME) AS EVENTDATETIME,
    -- 用ROW_NUMBER生成唯一会话ID，也可替换为UUID_STRING()
    ROW_NUMBER() OVER (ORDER BY USERID, DEVICEID, MIN(EVENTDATETIME)) AS SESSIONID
FROM CTE_SESSION_GROUPS
GROUP BY USERID, DEVICEID, SESSION_GROUP_ID
ORDER BY USERID, DEVICEID, EVENTDATETIME;

方案逻辑说明 #

1. 事件排序与新会话判断：用LAG函数获取上一事件时间，判断当前事件是否为新会话起始（间隔超1小时或为首次事件）
2. 会话组生成：通过累加新会话标识，为同一会话的事件分配相同的组ID
3. 会话信息提取：按会话组聚合，取每个会话的起始时间，并生成唯一会话ID

执行结果 #

USERID    DEVICEID    EVENTDATETIME     SESSIONID
    1       100   2024-05-02 01:01:00   1
    1       100   2024-05-02 02:01:00   2
    1       200   2024-05-02 03:02:00   3
    2       200   2024-05-02 01:03:00   4
    2       200   2024-05-02 02:01:00   5

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