Presto SQL：如何获取每次visit后对应最大focus timestamp

我有一张名为df的表，结构如下，需求是为每个[user_id:session_id]对，找到每次visit事件之后的最大focus timestamp（timestamp为BIGINT类型，按升序排列）。

表结构 #

期望输出 #

我尝试了以下SQL，但最后一列max_focus_timestamp始终为Null，请问如何解决？

with visit as 
(
select 
  user_id,
  session_id,
  timestamp as visit_timestamp,
  lead(timestamp) IGNORE NULLS over (PARTITION by user_id, session_id ORDER BY timestamp) as next_visit_timestamp 
  
from df 
where event = 'visit' 
),
focus as 
(
select 
  user_id,
  session_id,
  timestamp as focus_timestamp
  
from df 
where event != 'visit' 
)
select 
  distinct 
  v.user_id,
  v.session_id,
  v.visit_timestamp,
  max(f.focus_timestamp) as max_focus_timestamp
  
from visit v left join focus f on v.user_id = f.user_id and v.session_id = f.session_id 
 and f.focus_timestamp between v.visit_timestamp and v.next_visit_timestamp 
 
group by 1,2,3

你的代码返回Null的核心原因：最后一条visit事件的next_visit_timestamp为Null，而Null参与范围比较时结果为Unknown，导致无法匹配任何focus记录。另外，distinct是多余的——分组后已经不会有重复行。

以下提供两种可行的修正方案：

方案一：修复原逻辑的边界问题 #

将next_visit_timestamp的Null值替换为BIGINT类型的最大值，确保最后一条visit能包含后续所有focus记录；同时明确指定event='focus'避免误排除其他未知事件：

with visit as 
(
select 
  user_id,
  session_id,
  timestamp as visit_timestamp,
  -- 用BIGINT最大值填充最后一条visit的next_visit_timestamp
  COALESCE(lead(timestamp) over (PARTITION by user_id, session_id ORDER BY timestamp), 9223372036854775807) as next_visit_timestamp 
from df 
where event = 'visit' 
),
focus as 
(
select 
  user_id,
  session_id,
  timestamp as focus_timestamp
from df 
where event = 'focus'
)
select 
  v.user_id,
  v.session_id,
  v.visit_timestamp,
  max(f.focus_timestamp) as max_focus_timestamp
from visit v 
left join focus f 
  on v.user_id = f.user_id 
  and v.session_id = f.session_id 
  and f.focus_timestamp > v.visit_timestamp 
  and f.focus_timestamp < v.next_visit_timestamp 
group by 1,2,3

方案二：用窗口函数高效实现 #

无需拆分表，直接通过窗口函数为每个focus标记所属的最近visit，再分组求最大值，逻辑更简洁高效：

with tagged_events as (
select
  user_id,
  session_id,
  timestamp,
  event,
  -- 向前填充最近的visit时间戳
  last_value(case when event = 'visit' then timestamp end ignore nulls) over (
    partition by user_id, session_id 
    order by timestamp 
    rows between unbounded preceding and current row
  ) as visit_timestamp
from df
)
select
  user_id,
  session_id,
  visit_timestamp,
  max(timestamp) as max_focus_timestamp
from tagged_events
where event = 'focus'
group by user_id, session_id, visit_timestamp
-- 可选：补充没有后续focus的visit记录
union all
select
  user_id,
  session_id,
  timestamp as visit_timestamp,
  null as max_focus_timestamp
from df
where event = 'visit'
and timestamp not in (
  select distinct visit_timestamp from tagged_events where event = 'focus'
)
order by user_id, session_id, visit_timestamp;

内容的提问来源于stack exchange，提问作者Crubal Chenxi Li