如何用SQL将用户行为按15分钟间隔分组为会话？

核心思路 #

先识别所有会话的起始点（首次行为或与上一行为间隔超15分钟的行为），再通过LEAD函数获取每个会话的结束时间（即下一个会话的起始时间），最后将原表的每条行为记录通过user_id+时间范围匹配到对应会话，生成指定格式的会话ID。

分数据库实现 #

1. BigQuery #

-- 生成会话边界表（包含会话起始、结束时间）
WITH session_boundaries AS (
  SELECT
    user_id,
    timestamp AS session_start,
    -- 用下一个会话的起始时间作为当前会话结束时间，无后续会话则取当前时间+1天
    LEAD(timestamp, 1, TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)) OVER (PARTITION BY user_id ORDER BY timestamp) AS session_end
  FROM (
    SELECT
      user_id,
      timestamp,
      -- 计算当前行为与上一行为的时间差（秒）
      TIMESTAMP_DIFF(timestamp, LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp), SECOND) AS time_diff
    FROM `your_project.your_dataset.your_table`
  )
  -- 筛选会话起始点：首次行为 或 与上一行为间隔超过15分钟（900秒）
  WHERE time_diff > 900 OR time_diff IS NULL
),
-- 关联原表生成带会话ID的结果
user_actions_with_session AS (
  SELECT
    t.user_id,
    t.action,
    t.timestamp,
    -- 格式化会话ID：User{User_ID}_{会话起始时间（转为无符号时间字符串）}
    CONCAT('User', t.user_id, '_', FORMAT_TIMESTAMP('%Y%m%d%H%M%S', sb.session_start)) AS session_id
  FROM `your_project.your_dataset.your_table` t
  LEFT JOIN session_boundaries sb
    ON t.user_id = sb.user_id
    AND t.timestamp >= sb.session_start
    AND t.timestamp < sb.session_end
)
SELECT * FROM user_actions_with_session ORDER BY user_id, timestamp;

2. PostgreSQL #

-- 生成会话边界表
WITH session_boundaries AS (
  SELECT
    user_id,
    timestamp AS session_start,
    -- 用下一个会话的起始时间作为当前会话结束时间，无后续会话则取当前时间+1天
    LEAD(timestamp, 1, CURRENT_TIMESTAMP + INTERVAL '1 day') OVER (PARTITION BY user_id ORDER BY timestamp) AS session_end
  FROM (
    SELECT
      user_id,
      timestamp,
      -- 计算当前行为与上一行为的时间差（秒）
      EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp))) AS time_diff
    FROM your_table
  )
  WHERE time_diff > 900 OR time_diff IS NULL
),
-- 关联原表生成带会话ID的结果
user_actions_with_session AS (
  SELECT
    t.user_id,
    t.action,
    t.timestamp,
    -- 格式化会话ID：User{User_ID}_{会话起始时间（转为无符号时间字符串）}
    CONCAT('User', t.user_id, '_', TO_CHAR(sb.session_start, 'YYYYMMDDHH24MISS')) AS session_id
  FROM your_table t
  LEFT JOIN session_boundaries sb
    ON t.user_id = sb.user_id
    AND t.timestamp >= sb.session_start
    AND t.timestamp < sb.session_end
)
SELECT * FROM user_actions_with_session ORDER BY user_id, timestamp;

3. MySQL #

-- 生成会话边界表
WITH session_boundaries AS (
  SELECT
    user_id,
    timestamp AS session_start,
    -- 用下一个会话的起始时间作为当前会话结束时间，无后续会话则取当前时间+1天
    LEAD(timestamp, 1, DATE_ADD(NOW(), INTERVAL 1 DAY)) OVER (PARTITION BY user_id ORDER BY timestamp) AS session_end
  FROM (
    SELECT
      user_id,
      timestamp,
      -- 计算当前行为与上一行为的时间差（秒）
      TIMESTAMPDIFF(SECOND, LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp), timestamp) AS time_diff
    FROM your_table
  )
  WHERE time_diff > 900 OR time_diff IS NULL
),
-- 关联原表生成带会话ID的结果
user_actions_with_session AS (
  SELECT
    t.user_id,
    t.action,
    t.timestamp,
    -- 格式化会话ID：User{User_ID}_{会话起始时间（转为无符号时间字符串）}
    CONCAT('User', t.user_id, '_', DATE_FORMAT(sb.session_start, '%Y%m%d%H%i%s')) AS session_id
  FROM your_table t
  LEFT JOIN session_boundaries sb
    ON t.user_id = sb.user_id
    AND t.timestamp >= sb.session_start
    AND t.timestamp < sb.session_end
)
SELECT * FROM user_actions_with_session ORDER BY user_id, timestamp;

要点提示 #

• 会话起始点判断：首次行为（time_diff IS NULL）或与上一行为间隔超过15分钟（900秒）
• 会话结束时间：通过LEAD函数获取下一个会话的起始时间，确保当前会话的所有行为都落在[session_start, session_end)区间内
• 会话ID格式化：将起始时间转为无符号时间字符串（如YYYYMMDDHHMMSS），避免特殊字符引发的问题

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