So I have a vehicle_states table like this:
vehicle_id | state | updated_at
------------ ---------------- -------------------------------
ACX-685 | idle | 2021-12-24 15:13:09.071822 00
ACX-685 | active | 2021-12-24 16:03:41 00
ACX-685 | out-of-service | 2021-12-24 16:15:34.822808 00
ACX-685 | idle | 2021-12-24 16:15:35.822808 00
ACX-685 | active | 2021-12-24 16:40:23 00
ACX-685 | idle | 2021-12-24 16:40:37.436949 00
ACX-685 | active | 2021-12-24 16:40:38 00
For each vehicle and out-of-service state, I want to find out the time it took to transition out of this state. How much time the vehicle was out-of-service.
I would like to get a table with vehicle_id, timestamp when the vehicle was out-of-service and a third column specifying the interval of time that the vehicle was out-of-service, like so:
vehicle_id | out_of_service_at | out_of_service_for
------------ ------------------------------- --------------------
ACX-685 | 2021-12-24 16:15:34.822808 00 | 00:00:01
BRT-162 | 2021-12-25 11:26:31.827361 00 | 00:00:27
CodePudding user response：
You can solve the problem using window functions:
with data as (
select
*,
lead(updated_at) over (partition by vehicle_id order by updated_at) next_state
from vehicles
) select
*,
(next_state - updated_at) out_of_service_for
from data where state = 'out-of-service';