如何在Google BigQuery中计算邮件打开与最后点击的时间差

问题分析 #

你的核心问题出在窗口函数的分区逻辑和使用方式上：

1. 原SQL按event_timestamp分区，每个分区仅包含单条数据，导致FIRST_VALUE和LAST_VALUE无法跨事件取到对应的值
2. LAST_VALUE默认窗口范围是从分区开头到当前行，即便分区正确，也无法获取到全分区内的最后一次点击时间

修正后的SQL代码 #

假设你的数据中有用户唯一标识（对应截图中的第一列），可以用以下查询实现需求：

WITH event_data AS (
  SELECT 
    user_id,
    event_timestamp,
    event_type,
    -- 提取open事件的时间
    CASE WHEN event_type = 'open' THEN event_timestamp END AS open_time,
    -- 按用户分组，获取该用户的最后一次点击时间
    MAX(CASE WHEN event_type = 'click' THEN event_timestamp END) OVER (PARTITION BY user_id) AS last_click_time
  FROM `check-db`
  WHERE subject = 'check the summers out'
)
SELECT 
  user_id,
  event_timestamp,
  event_type,
  open_time,
  last_click_time,
  -- 仅对open事件计算时间差（单位：小时）
  CASE 
    WHEN open_time IS NOT NULL THEN DATETIME_DIFF(CAST(open_time AS DATETIME), CAST(last_click_time AS DATETIME), HOUR)
  END AS time_diff_hours
FROM event_data

关键修正点 #

• 调整分区键：将分区从event_timestamp改为用户标识（如user_id），确保同一用户的所有事件被归为一组
• 简化最后点击时间获取：用MAX()窗口函数直接获取用户的最后一次点击时间，比LAST_VALUE更简洁且不易出错
• 精准计算时间差：仅对open事件计算时间差，匹配预期结果的展示逻辑
• 类型转换验证：确保event_timestamp能正确转换为DATETIME，如果原字段是UNIX时间戳，需先转为TIMESTAMP再转DATETIME，例如：CAST(TIMESTAMP_SECONDS(event_timestamp) AS DATETIME)

原数据与预期结果 #

• 我的数据：
• 预期结果：

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