postgrecondition在一段时间内满足

Question

我有下表，我想创建condition_met列。condition_met列是我的预期输出。

timestamp  | client_id| type_id | prospect_id | condition_met
'2015-06-13 13:45:58'  | 240      | 34.     | 480         |TRUE
'2015-06-13 13:45:59'  | 480      | 56.     | 480         |FALSE
'2015-06-13 13:46:00'  | 480      | 76.     | 480         |FALSE
'2015-06-13 13:46:01'  | 480      | 76.     | 480         |FALSE
'2015-06-13 13:46:02'  | 240      | 35.     | 240         |FALSE
'2015-06-13 13:47:01'  | 480      | 34.     | 240         |FALSE
'2015-06-13 13:47:03'  | 240      | 56.     | 240         |FALSE
'2015-06-13 13:47:04'  | 240      | 76.     | 240         |FALSE
'2015-06-13 13:47:06'  | 240      | 76.     | 240         |FALSE
'2015-06-13 13:47:09'  | 480      | 98.     | 480         |FALSE
...

Condition_met在type_id = 34时为TRUE，并且在该type_id之后的5秒内，type_id = 34的client_id也变为prospect_id。

换句话说：对于每个type_id=34，执行操作type_id=33的client_id需要在5秒内变成prospect_id

Answer 1

这个查询适合您吗？(在此处重新搜索(

with operations as (
select *,lead(client_id,1) over(order by client_id,timestamp_op),lead(prospect_id,1) over(order by client_id,timestamp_op),timestamp_op + interval '5s',
case 
when type_id = 34 and lead(client_id,1) over(order by client_id,timestamp_op) = client_id 
and lead(client_id,1) over(order by client_id,timestamp_op) = lead(prospect_id,1) over(order by client_id,timestamp_op) 
and lead(timestamp_op,1) over(order by client_id,timestamp_op) <=timestamp_op + interval '5s' then true 
else false 
end as condition_met
from operation
order by 2,1
)
select timestamp_op,client_id,type_id,prospect_id,condition_met from operations order by timestamp_op