PostgresSql查询

Question

我想要获取在不同列中的"completed_order“活动之间发生的第一个"email”活动的activity_id。我尝试了下面的代码，但是我没有得到第一个“电子邮件”活动的id，而是得到了"completed_order“活动的id。

SELECT 
  activity_id,  
  customer ,  
  activity, 
  ts,
  case 
    when 
     activity = 'completed_order' and lead(activity) over (partition by customer order by ts) ='email'
    then        
    activity_id
  end as cndn

from activity_stream 
where customer in (
   select customer where customer = 'Lehmanns Marktstand' 
) 
order by ts

​

​

​

​

Answer 1

我认为你需要使用这样的东西，但描述不是很清楚。在你的问题中添加一个想要的结果，如果是错误的，就删除评论

case 
 when 
  activity = 'email' and lag(activity) over (partition by customer order by ts) ='completed_order'
 then        
  lag(activity_id) over (partition by customer order by ts)
end as cndn

如果前一个活动是“已完成订单”，则会将前一行的活动id放在电子邮件行上。

编辑，严格地说，你想要的结果是由：

case 
 when 
  activity = 'completed_order' and lead(activity) over (partition by customer order by ts) ='email'
 then        
  lead(activity_id) over (partition by customer order by ts)
end as cndn