如何用SQL在用户5分钟无活动时创建新会话？

你之前用LEAD()函数的方向错了——LEAD()是取当前记录的下一条时间，但我们需要对比当前记录和用户上一条活动记录的时间间隔，判断是否超过5分钟来划分新会话，正确的做法是用LAG()函数。

下面是具体的SQL实现（以标准SQL为例，不同数据库可能有时间函数的细微差异，可根据实际情况调整）：

WITH user_activity AS (
  SELECT
    userid,
    timestamp,
    -- 获取用户上一次活动的时间
    LAG(timestamp) OVER (PARTITION BY userid ORDER BY timestamp) AS prev_timestamp,
    -- 判断当前记录是否为新会话起点：首次活动 或 与上一次间隔超5分钟
    CASE
      WHEN LAG(timestamp) OVER (PARTITION BY userid ORDER BY timestamp) IS NULL THEN 1
      WHEN TIMESTAMPDIFF(MINUTE, LAG(timestamp) OVER (PARTITION BY userid ORDER BY timestamp), timestamp) > 5 THEN 1
      ELSE 0
    END AS is_new_session
  FROM your_table_name
),
session_groups AS (
  SELECT
    userid,
    timestamp,
    -- 累加新会话标记，生成每个会话的分组ID
    SUM(is_new_session) OVER (PARTITION BY userid ORDER BY timestamp) AS session_group
  FROM user_activity
)
SELECT
  userid,
  timestamp,
  -- 生成示例格式的会话ID：userid_会话起始时间
  CONCAT(userid, '_', MIN(timestamp) OVER (PARTITION BY userid, session_group)) AS session
FROM session_groups
ORDER BY userid, timestamp;

逻辑说明 #

1. user_activity CTE：通过LAG()拉取用户上一条活动记录的时间，标记出所有新会话的起点。
2. session_groups CTE：对每个用户的新会话标记做累加，得到唯一的会话分组ID，同一会话内的记录会被分到同一组。
3. 最终查询：根据用户和会话分组，提取该会话的起始时间，拼接成示例中的会话ID格式。

适配不同数据库的时间差计算 #

如果你的数据库不支持TIMESTAMPDIFF，可以替换为对应数据库的时间差函数：

• PostgreSQL：EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (PARTITION BY userid ORDER BY timestamp))) / 60 > 5
• BigQuery：TIMESTAMP_DIFF(timestamp, LAG(timestamp) OVER (PARTITION BY userid ORDER BY timestamp), MINUTE) > 5

运行上述SQL后，会得到你示例中的预期结果。

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