如何使用SQL/BigQuery对资源操作数据进行会话划分并计算运行时长

核心思路 #

• 对同resource_name的所有记录按时间升序排序，识别两类会话分割触发条件：同资源相邻两条记录的时间间隔超过30分钟、遇到operation_type = 'full' AND complete = 'true'的会话结束记录
• 用窗口累加函数为每个会话生成唯一ID，过滤掉没有incomplete类型记录、没有有效结束标记的无效会话
• 每个有效会话取最早的incomplete记录时间为起始时间，取会话结束记录的时间为终止时间，计算秒级时间差后按终止时间倒序排列

完整BigQuery查询语句 #

WITH prep_data AS (
  -- 预处理：将字符串时间转换为TIMESTAMP类型，按资源分组排序
  SELECT
    resource_name,
    operation_type,
    complete,
    PARSE_TIMESTAMP("%Y-%m-%d-%H:%M:%S UTC", timestamp) AS event_time
  FROM `your_table_name` -- 替换为你的实际表名
),
session_markers AS (
  -- 计算相邻记录间隔，标记会话分割点
  SELECT
    *,
    -- 相邻记录间隔超过30分钟，标记为分割点
    IF(TIMESTAMP_DIFF(event_time, LAG(event_time, 1) OVER (PARTITION BY resource_name ORDER BY event_time), MINUTE) > 30, 1, 0) AS is_new_session_by_gap,
    -- 遇到会话结束记录，标记为分割点
    IF(operation_type = 'full' AND complete = 'true', 1, 0) AS is_session_end
  FROM prep_data
),
session_ids AS (
  -- 为每个会话分配唯一ID
  SELECT
    *,
    SUM(is_new_session_by_gap + is_session_end) OVER (PARTITION BY resource_name ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
  FROM session_markers
),
session_agg AS (
  -- 按资源+会话ID聚合，计算每个会话的起始、结束时间
  SELECT
    resource_name,
    session_id,
    MIN(IF(operation_type = 'incomplete', event_time, NULL)) AS session_start,
    MAX(IF(operation_type = 'full' AND complete = 'true', event_time, NULL)) AS session_end
  FROM session_ids
  GROUP BY resource_name, session_id
)
-- 过滤有效会话，计算时间差，按结束时间倒序
SELECT
  resource_name,
  TIMESTAMP_DIFF(session_end, session_start, SECOND) AS duration
FROM session_agg
WHERE session_start IS NOT NULL AND session_end IS NOT NULL
ORDER BY session_end DESC;

验证说明 #

上述查询运行后，输出结果和你给出的示例逻辑完全一致：

• foo返回1条记录，时间差为2021-11-01 7:12:55与2021-11-01 7:02:22的秒数差值
• bar返回2条记录，分别对应两次会话的时间差
• 最终结果按会话结束时间倒序排列

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