Postgresql – Update value in same view using CASE expression

postgresqlwindow functions

I am creating a view where i have daily data of different items and each item has a state. I created a column that shows what the column was yesterday using

LAG(state) OVER(PARTITION BY item_id ORDER BY currentdate) AS yesterday_state

Now I want to count how long the state has been the same value and im doing so with this:

COUNT (CASE WHEN state = yesterday_state THEN state ELSE NULL END) OVER(PARTITION BY item_id ORDER BY currentdate AS state_age

This is working properly but I need to find a way to set the value back to 0 when state != yesterday_state

This all is happening inside of SELECT statement as I'm creating a view. How could I go around doing this so that the state_age sets to 0 when state is not the same value as yesterday_state

SOLUTION:

CREATE VIEW view AS (
    WITH
        cte1 AS (SELECT *, LAG(state) OVER(PARTITION BY item_id ORDER BY currentdate) AS state_yesterday
            FROM table),
        cte2 AS (SELECT *, CASE WHEN state = state_yesterday THEN 1 ELSE 0 END AS state_remained
            FROM cte1),
        cte3 AS (SELECT *, CASE WHEN state_remained != 0 THEN SUM(state_remained) OVER(PARTITION BY item_id, state ORDER BY currentdate) ELSE 0 END AS state_age_days
            FROM cte2)
        SELECT *
        FROM cte3
)

Best Answer

Schematically

WITH 
cte1 AS ( SELECT *, LAG(state) OVER (ORDER BY datetime) AS prev_state 
          FROM sourcetable),
cte2 AS ( SELECT *, CASE WHEN state = prev_state THEN 0 ELSE 1 END AS state_changed 
          FROM cte1 ),
cte3 AS ( SELECT *, SUM(state_changed) OVER (ORDER BY datetime) AS group_number 
          FROM cte2 )
SELECT *, COUNT(*) OVER (PARTITION BY group_number) AS group_count 
FROM cte3

For multiple entities add proper partitioning / partitioning level.

Related Question