PostgresSQL/SQL查询

Question

我想要获取在"completed_order“活动之间发生的每个"email”活动的行的activity_id，在"first_in_between“列中我写了这个查询

SELECT  activity_id, customer , activity, ts,
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 First_in_between
    
    from activity_stream where customer = 'Lehmanns Marktstand'
        order by ts

通过上面的查询，我得到了这个结果。

​

​

我想要的结果应该是

​

​

Answer 1

您可以使用以下命令轻松获取电子邮件的时间戳：

select activity_id, customer , activity, ts,
       (case when activity = 'completed_order' and
                  (min(ts) filter (where activity = 'email') over (partition by customer order by ts desc) <
                   min(ts) filter (where activity = 'completed_order') over (partition by customer order by ts desc)
                  )
             then min(ts) filter (where activity = 'email') over (partition by customer order by ts desc)
        end) as First_in_between    
from activity_stream
where customer = 'Lehmanns Marktstand'
order by ts;

然后，您可以连接回表或使用另一级别的窗口函数来获取时间戳的相应activity_id。

实际上，我想我更喜欢另一种方法，那就是计算完成订单的数量，然后取最小的ts：

select a.*,
       min(ts) filter (where activity = 'email') over (partition by grp) as email_ts
from (select a.*,
             count(*) filter (where activity = 'completed_order') over (partition by customer order by ts) as grp
      from activity_stream a
      where customer = 'Lehmanns Marktstand'
     ) a;

这也应该允许您使用twist来获取活动id，而不需要附加子查询：

select a.*,
       (array_agg(activity_id order by ts) filter (where activity = 'email') over (partition by grp))[1] as email_activity_id
from (select a.*,
             count(*) filter (where activity = 'completed_order') over (partition by grp order by ts) as grp
      from activity_stream a
      where customer = 'Lehmanns Marktstand'
     ) a