PostgreSQL finding durations-CodePudding

I'm trying to find the duration of an event in high (1) state. The states are logged into a table as the following

time    |state|
-------  ----- 
01:00:00|    0|
01:01:00|    1|
01:02:35|    1|
01:03:20|    0|
01:04:00|    0|
01:05:00|    0|
01:06:00|    1|
01:07:00|    1|
01:08:00|    1|
01:09:10|    0|
01:10:00|    0|
01:11:00|    1|
01:12:45|    0|

The result I'm looking for is this:

starttime    | endtime    | duration    | 
------------- ------------ ------------- 
01:01:00     | 01:03:20   |   00:02:20  |
01:06:00     | 01:09:10   |   00:03:10  |
01:11:00     | 01:12:45   |   00:01:45  |

Almost the same as this post, but not quite the same

not quite sure how to acomplish this.. any help would be appreciated

CodePudding user response：

You have to use window functions (result here).

with y as (
select x.time, lead(time,1) over (), lead(time,1) over () - x.time,state,previous_state
from (select *,lag(state,1) over (order by time) previous_state from t1) x
where (x.state = 1 and x.previous_state = 0) or (x.state = 0 and x.previous_state = 1) 
order by time
)
select * from y where state = 1 and previous_state = 0

CodePudding user response：

It might be a gap and islands problem, we need to use LAG window function & CASE WHEN to determine a group on your logic condition column (event in high (1) state.)

Then use ROW_NUMBER to make a group continuous number.

WITH CTE AS (
    SELECT *,
          ROW_NUMBER() OVER (PARTITION BY condition ORDER BY time) - 
            ROW_NUMBER() OVER ( ORDER BY time) grp
    FROM (
     SELECT *,
           (CASE WHEN state = 1 OR  LAG(state,1,state) OVER(ORDER BY TIME)  = 1 THEN 1 END) condition
     FROM T
    ) t1
)
SELECT MIN(time) starttime ,
       MAX(time) endtime ,
       MAX(time) - MIN(time) duration    
FROM CTE 
WHERE condition IS NOT NULL
GROUP BY grp
ORDER BY 1

sqlfiddle

CodePudding user response：

try this :

WITH list AS
( SELECT time AS starttime
       , state
       , CASE
           WHEN state = 0 
           THEN min(time) FILTER (WHERE state = 1) OVER (ORDER BY time ASC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
           ELSE min(time) FILTER (WHERE state = 0) OVER (ORDER BY time ASC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
         END AS endtime
    FROM test
)
SELECT min(starttime)
     , endtime
     , endtime - min(starttime) AS duration
  FROM list
 WHERE state = 1
 GROUP BY endtime

test result in dbfiddle