PostgresSql相关查询

Question

我有一个名为"activity“的列，在"activity”列中有3种活动。1.Completed_order，2.viewed_page，3.email。现在，我想要编写一个查询，我将在其中获取每次‘completed_order’活动之后发生的第一个'email‘的activity_id。

我编写的查询是

select 
        activity_id, ts, customer,activity,
        case 
        when activity = 'email' and lag(activity) over (partition by customer order by ts) !='email' 
        then activity_id else 0 
        end as cndn
        from activity_stream as az1 where customer = 'Lehmanns Marktstand' and customer in (select customer from activity_stream where activity='email') order by ts

The results I am getting.

And the output I want

输出应该是发生在“activity_id”活动行中每个"completed_order“活动之间的最小"email”活动。

Answer 1

这样的子查询会实现您想要的结果吗？

select activity_id, ts, customer, activity,
case when activity = 'completed_order' then (select activity_id from activity_stream b where a.customer = b.customer and b. activity = 'email' and a.ts < b.ts limit 1) else 0 end as cndn
from activity_stream as a 
where customer = 'Lehmanns Marktstand' 
and customer in (select customer from activity_stream where activity='email') 
order by ts

编辑:刚刚看到您编辑了问题，这将只获得completed_order行之后的第一个电子邮件活动，它不会特定于每个completed_order行之间发生的活动。

编辑2:这应该可以用，但它很脏，我的postgresql不是最好的：

select activity_id, ts, customer, activity,
case when a.activity = 'completed_order' then
  case when (select ts from activity_stream b where a.customer = b.customer and b. activity = 'completed_order' and a.ts < b.ts limit 1) is null then (select activity_id from activity_stream b where a.customer = b.customer and b. activity = 'email' and a.ts < b.ts limit 1)
    when (select ts from activity_stream b where a.customer = b.customer and b. activity = 'email' and a.ts < b.ts limit 1) < (select ts from activity_stream b where a.customer = b.customer and b. activity = 'completed_order' and a.ts < b.ts limit 1) then (select activity_id from activity_stream b where a.customer = b.customer and b. activity = 'email' and a.ts < b.ts limit 1)
  end 
end as cndn
from activity_stream as a 
where customer = 'Lehmanns Marktstand' 
and customer in (select customer from activity_stream where activity='email') 
order by ts

编辑3:我对其进行了重构，因此您可以将第3行末尾的order by b.ts desc更改为asc表示最小email activity_id或desc表示最大email activity_id。

select a.activity_id, a.ts, a.customer, a.activity,
case when a.activity = 'completed_order' then
  (select b.activity_id from activity_stream b where a.customer = b.customer and b.activity = 'email' and b.ts between a.ts and (select c.ts from activity_stream c where a.customer = c.customer and c.activity = 'completed_order' and a.ts < c.ts limit 1) order by b.ts desc limit 1)
end as cndn
from activity_stream as a 
order by ts

Answer 2

根据您的输出，您将尝试为已完成的行而不是电子邮件行创建计算值-因此，请在已完成的行上使用LEAD函数，而不是在电子邮件行上使用lag函数