我有下表，我想創建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 為 TRUE 時type_id = 34和此后 5 秒內type_id，client_idoftype_id = 34也變為prospect_id。
換一種說法：對于每個 type_id = 34 執行操作 type_id = 34 的 client_id 需要在 5 秒內變為前景 ID
uj5u.com熱心網友回復：
此查詢適合您嗎？（結果在這里）
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/415503.html
標籤：
上一篇：計算兩個時間戳之間的總差值