下面是bigquery标准sql

# standardSQL

SELECT 
  user_pseudo_id, 
  AVG(duration) AS avg_duration, 
  SUM(duration) AS total_duration
FROM (
  SELECT *, LEAD(timestamp) OVER(win) - timestamp AS duration
  FROM `project.dataset.table`
  WHERE event IN ('event_a', 'event_b')
  WINDOW win AS (PARTITION BY user_pseudo_id ORDER BY timestamp)

) 
WHERE event = 'event_a'
GROUP BY user_pseudo_id

我会这样回答：

with data as (
  select user_pseudo_id, event_name, event_timestamp from `database` where event_name in ('event_a', 'event_b')
),
ea as (
 -- Get first event_a per user
 select user_pseudo_id, min(event_timestamp) as first_a_ts from data where event_name = 'event_a' group by 1
),
eb as (
 -- Get first event_b per user
 select user_pseudo_id, min(event_timestamp) as first_b_ts from data where event_name = 'event_b' group by 1
),
joined (
  -- Assume we only want to calculate duration if user has an event_b, hence inner join
  select * 
  from ea 
  inner join eb using(user_pseudo_id) 
  where first_b_ts > first_a_ts
)
select 
  avg(timestamp_diff(first_b_ts, first_a_ts, second))/60.0 as avg_duration_minutes
from joined

我没有包括你的 .2 因为我不知道你为什么要任意过滤掉12秒以内的差异。

如果要在事件a之后获得事件b的时间，可以使用条件累积最小值：

SELECT ab.*
FROM (SELECT user_pseudo_id, event_timestamp as event_a_timestamp,  
             MIN(CASE WHEN event_name = 'event_b' THEN event_timestamp END) OVER 
                 (PARTITION BY user_id 
                  ORDER BY event_timestamp  
                  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                 ) as event_b_timestamp  
     FROM  `database`
     WHERE event_name in ('event_a', 'event_b')
    ) ab
WHERE event_name = 'event_a'

你的问题没有提供足够的细节来决定还需要做什么。