How to find end point of same value with interval?

Question

I have a table like this below.

ID        Time         State 
1          1         "active"
1          2         "active"
1          3         "active"
1          4        "inactive"
2          2        "inactive"
2          3         "active"
3          1         "active"
3          3         "active"
3          4        "inactive"

I want to sort table with start/end time by state.
It might need lag() window function but I don't know how to find end point of same state.

My expected table should look like this.

ID        Start          End           State 
1          1              4           "active"
1          4             NULL        "inactive"
2          2              3          "inactive"
2          3             NULL         "active"
3          1              4           "active"
3          4             NULL        "inactive"

In your expected output you wrote Start = 1, End = 4 for state inactive. Did you mean Start = 4? — S-Man, Dec 19 '18 at 15:28
Hi S-Man, Thank you for pointing out that. I corrected it :) — spacecat, Dec 19 '18 at 15:46

S-Man · Accepted Answer · 2018-12-19T15:55:55.517

demo:db<>fiddle

SELECT DISTINCT ON (sum)  -- 5
    id,
    -- 4
    first_value(time) OVER (PARTITION BY sum ORDER BY time) as start,
    first_value(lead) OVER (PARTITION BY sum ORDER BY time DESC) as end,
    state
FROM (
    SELECT
        *, 
        -- 3
        SUM(CASE WHEN is_prev_state THEN 0 ELSE 1 END) OVER (ORDER BY id, time)
    FROM (
        SELECT 
            *, 
            -- 1
            lead(time) OVER (PARTITION BY id ORDER BY time),
            -- 2                
            state = lag(state) OVER (PARTITION BY id ORDER BY time) as is_prev_state
        FROM states
    )s
)s

lead() takes the next value to the current row. To e.g. the time == 4 (id == 1) goes to the row with time == 3. The idea is to get a possible end of group onto the right row.
lag() does the opposite thing. It takes the previous value the current row. With that I can check whether a state has changed or not: Is the current state the same of the last one.
With this line I create the groups for every single state: If state change happened sum up one value. If not hold the same value (adding 0).
Now I have the possible last value per state (given through (1)) and can get the first value. This is done with the window function first_value() which gives you the first value of an ordered group. To get the last value you just have to order the group descending. (Why not using last_value())
DISTINCT ON filters only the very first row of the (with SUM() function generated) group

Thank you so much for extra explanation and your query! I understand clearly and I got what I want :D Happy holiday S-Man! — spacecat, Dec 19 '18 at 16:54

How to find end point of same value with interval?

1 Answers1

Linked