Add running count of specific state changes

Question

I have a table in PostgreSQL with below fields, where timestamp is simplified as integers:

I want to break then aggregate rows based on the presence of the 's' status, to spit out an additional column which I can use to easily group rows:

ts  status  flag
1     m      1
2     m      1
3     i      1
4     s      1
5     s      1
6     i      2
7     i      2
8     m      2
9     s      2
10    m      3

The dynamic column flag shall increment whenever status changes from 's' to something else.

I don't see a clear pattern to partition by. How to do this?

Erwin Brandstetter · Accepted Answer · 2018-09-16T20:40:29.503

Two levels of window functions.
To keep the code short, you can combine the aggregate FILTER clause with the outer window function:

SELECT ts, status
     , 1 + count(*) FILTER (WHERE previous_status = 's' AND status <> 's')
                    OVER (ORDER BY ts) AS flag
FROM (
   SELECT *, lag(status) OVER (ORDER BY ts) AS previous_status
   FROM   tbl
   ) sub;

db<>fiddle here

Assuming current Postgres and all columns NOT NULL.
Related:

Add running count of specific state changes

1 Answers1