如何在Google BigQuery中计算同一设备各来源事件时间差的均值与中位数

我正在使用Google Analytics和Google BigQuery，有应用上报的事件数据，数据样例如下：

vendor_id | event_name | source (from event_params) | event_timestamp
1         | check      | source_1                   | 2022-11-20 08:49:44.322000
2         | check      | source_1                   | 2022-11-21 04:49:44.322000
1         | check      | source_2                   | 2022-11-20 06:49:44.322000
1         | check      | source_2                   | 2022-11-23 04:49:44.322000
2         | check      | source_1                   | 2022-11-15 01:49:44.322000
2         | check      | source_1                   | 2022-11-25 08:49:44.322000
1         | check      | source_2                   | 2022-11-10 02:49:44.322000

需要计算同一vendor_id下各source内相邻event_timestamp的时间差的中位数与平均值，预期输出如下：

vendor_id | event_name | source (from event_params) | median      | average
1         | check      | source_1                   | 250 seconds | 350 seconds
1         | check      | source_2                   | 400 seconds | 850 seconds 
2         | check      | source_1                   | 950 seconds | 650 seconds 
2         | check      | source_2                   | 850 seconds | 850 seconds 

解决方案 #

可以通过以下BigQuery SQL实现需求，核心分为两步：计算相邻事件的时间差、按分组统计中位数和平均值：

WITH event_with_previous AS (
  SELECT
    vendor_id,
    event_name,
    `source (from event_params)` AS source,
    event_timestamp,
    -- 获取同一分组内的上一条事件时间戳
    LAG(event_timestamp) OVER (
      PARTITION BY vendor_id, event_name, `source (from event_params)`
      ORDER BY event_timestamp
    ) AS previous_timestamp
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的实际表名
)
SELECT
  vendor_id,
  event_name,
  source AS `source (from event_params)`,
  -- 计算时间差的中位数，转换为秒并拼接单位
  CONCAT(CAST(PERCENTILE_CONT(TIMESTAMP_DIFF(event_timestamp, previous_timestamp, SECOND), 0.5) AS INT64), ' seconds') AS median,
  -- 计算时间差的平均值，转换为秒并拼接单位
  CONCAT(CAST(AVG(TIMESTAMP_DIFF(event_timestamp, previous_timestamp, SECOND)) AS INT64), ' seconds') AS average
FROM
  event_with_previous
WHERE
  previous_timestamp IS NOT NULL -- 排除无前置事件的记录
GROUP BY
  vendor_id, event_name, source
ORDER BY
  vendor_id, source;

代码说明 #

• CTE event_with_previous: 使用LAG()窗口函数，按vendor_id、event_name、source分组，按时间戳排序，获取每条事件的上一条事件时间戳。
• 时间差计算: 直接用TIMESTAMP_DIFF()计算当前事件与前置事件的时间差（单位为秒），无需额外转换。
• 中位数统计: PERCENTILE_CONT(..., 0.5)用于计算连续型中位数，若需要离散型中位数可替换为PERCENTILE_DISC(..., 0.5)。
• 结果格式化: 通过CONCAT()将数值结果与单位字符串拼接，匹配预期输出格式。

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