BQ SQL中按participant_id分区计算相邻行时间差值

你需要用LEAD()窗口函数（而非LAG()）获取同一participant_id分区内下一行的时间戳，结合时间转换和差值计算函数即可实现需求。以下是具体方案：

完整SQL示例 #

假设你的表名为your_table，Unix时间戳列名为unix_timestamp（秒级，若为毫秒级请替换对应函数）：

SELECT
  participant_id,
  unix_timestamp,
  -- 将Unix时间戳转换为UTC时区的Datetime
  TIMESTAMP_SECONDS(unix_timestamp) AS event_datetime,
  -- 计算当前行与下一行的时间差（分钟）
  TIME_DIFF(
    LEAD(TIMESTAMP_SECONDS(unix_timestamp)) OVER (
      PARTITION BY participant_id 
      ORDER BY unix_timestamp
    ),
    TIMESTAMP_SECONDS(unix_timestamp),
    MINUTE
  ) AS time_spent
FROM your_table

关键逻辑说明 #

• PARTITION BY participant_id：按用户ID分区，确保仅在同一用户的行内计算时间差，避免跨用户数据混淆。
• ORDER BY unix_timestamp：对每个分区内的行按时间戳排序，保证LEAD()获取的是当前行的后续事件时间。
• LEAD()函数：获取同一分区内下一行的转换后时间戳，最后一行因无后续行返回NULL，正好满足你最后一行time_spent为NULL的要求。
• TIME_DIFF()函数：计算两个时间戳的差值，指定MINUTE参数直接得到分钟数结果。

注意事项 #

• 若你的Unix时间戳是毫秒级，请将TIMESTAMP_SECONDS()替换为TIMESTAMP_MILLIS()。
• 如需指定其他时区，可通过DATETIME(TIMESTAMP_SECONDS(unix_timestamp), 'Asia/Shanghai')这类写法转换为对应时区的Datetime。

示例输入输出 #

输入表 #

输出表 #

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