BigQuery导航函数使用求助：基于无活动时长计算Session_id

先说说你原代码里的几个问题：

• 分区用partition by event_id完全错误，每个event_id都是唯一值，LAG函数根本拿不到前一条事件的时间
• date_diff只比较日期，忽略了时分秒的差异，没法判断分钟级的间隔
• 时间差的计算逻辑混乱，没有正确关联当前事件和前一个事件的时间

下面是正确的实现思路和代码：

实现步骤 #

1. 用LAG()导航函数获取前一个事件的created_ts，按created_ts排序（因为会话是按时间顺序生成的）
2. 计算当前事件与前一个事件的时间间隔，用TIMESTAMP_DIFF()函数精确到分钟
3. 判断间隔是否超过1分钟，生成一个"会话切换标记"：超过1分钟记为1，否则记为0
4. 用累加窗口函数SUM() OVER()把标记累加，得到连续的Session ID

完整SQL代码 #

WITH event_with_prev_time AS (
  SELECT
    event_id,
    created_ts,
    -- 获取前一条事件的时间，第一条数据没有前一条，返回NULL
    LAG(created_ts) OVER(ORDER BY created_ts) AS prev_created_ts
  FROM table1
),
session_change_flags AS (
  SELECT
    event_id,
    created_ts,
    -- 判断当前事件与前一条的间隔是否超过1分钟，是则标记为1，否则0；第一条数据标记为0
    CASE
      WHEN TIMESTAMP_DIFF(created_ts, prev_created_ts, MINUTE) > 1 THEN 1
      ELSE 0
    END AS session_change_flag
  FROM event_with_prev_time
)
SELECT
  -- 累加标记得到Session ID，第一条数据从1开始
  SUM(session_change_flag) OVER(ORDER BY created_ts) + 1 AS session_id,
  created_ts
FROM session_change_flags
ORDER BY created_ts;

代码解释 #

• LAG(created_ts) OVER(ORDER BY created_ts)：按时间顺序排列事件，给每条数据带上前一条的时间，第一条数据的prev_created_ts为NULL
• TIMESTAMP_DIFF(created_ts, prev_created_ts, MINUTE)：计算两个时间戳的分钟差，第一条数据因为prev是NULL，这个函数会返回NULL，所以CASE里会默认返回0
• SUM(session_change_flag) OVER(ORDER BY created_ts)：按时间顺序累加标记，每次遇到间隔超过1分钟的情况，累加值+1，这样就生成了连续的Session ID，最后加1是让Session ID从1开始（默认累加从0开始）

执行这段代码后，得到的结果完全符合你的预期：

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