使用SQL窗口函数计算游戏会话的起止时间（SQLite环境）

你现有代码的核心问题是使用PARTITION BY date(handDate)按自然天对数据分区，直接切断了跨自然天的会话，完全不符合允许跨天的规则，导致结果错误。

WITH step1 AS (
    -- 第一步：计算每条记录与上一条记录的时间差，标记新会话起点
    SELECT 
        handDate,
        CASE 
            WHEN LAG(handDate) OVER (ORDER BY handDate) IS NULL 
                THEN 1
            WHEN strftime('%s', handDate) - strftime('%s', LAG(handDate) OVER (ORDER BY handDate)) >= 3600 
                THEN 1
            ELSE 0
        END AS is_new_session
    FROM hands
),
step2 AS (
    -- 第二步：累加新会话标记，生成唯一会话ID
    SELECT 
        handDate,
        SUM(is_new_session) OVER (ORDER BY handDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
    FROM step1
)
-- 第三步：按会话ID聚合得到会话起止时间
SELECT 
    DATE(MIN(handDate)) AS day,
    MIN(handDate) AS session_start,
    MAX(handDate) AS session_end
FROM step2
GROUP BY session_id
ORDER BY session_start;

• 所有记录先全局按handDate升序排序，不限制自然天，满足跨天会话的需求
• 相邻两条记录时间差≥3600秒（1小时）时，后续记录判定为新会话起点
• 累加新会话标记作为会话唯一ID，同一会话的所有记录会分到同一个ID下
• 最终按会话ID聚合，取会话最早时间作为起始时间、最晚时间作为结束时间，起始时间对应的日期作为day字段

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