0

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
)
0

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.

1
  • Thanks for the answer but i think there is still something im missing. The group number and group count just keep static over the rows. I use the following code: WITH cte1 AS (SELECT *, LAG(state) OVER(ORDER BY currentdate) AS state_yesterday FROM table), cte2 AS (SELECT *, CASE WHEN state = state_yesterday THEN 0 ELSE 1 END AS state_changed FROM cte1), cte3 AS (SELECT *, SUM(state_changed) OVER(ORDER BY currentdate) AS group_number FROM cte2) SELECT *, COUNT(*) OVER(PARTITION BY group_number) AS group_count FROM cte3 – Lauri Huhta Mar 27 '20 at 14:10
0

You can find the "groups" where state has not changed like:

SELECT t.* 
     , ROW_NUMBER() OVER (PARTITION BY item_id
                          ORDER BY currentdate)
     - ROW_NUMBER() OVER (PARTITION BY item_id, state
                          ORDER BY currentdate) as grp
FROM table t

You can then apply whatever aggregate you want to on this group. Example:

SELECT MIN(currentdate) OVER (PARTITION BY item_id, grp) as FIRST_DATE
     , MAX(currentdate) OVER (PARTITION BY item_id, grp) as LAST_DATE
     , COUNT(1) OVER (PARTITION BY item_id, grp) as #SIZE
     , t.itemid, t.state
FROM ( 
    SELECT t.* 
      , ROW_NUMBER() OVER (PARTITION BY item_id
                           ORDER BY currentdate)
      - ROW_NUMBER() OVER (PARTITION BY item_id, state
                           ORDER BY currentdate) as grp
    FROM table t
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.