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

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

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?

  • Please remember to always disclose your Postgres version. And typically also a table definition (CREATE TABLE statement). Example: It matters whether columns can be NULL ... – Erwin Brandstetter Sep 16 '18 at 20:39

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
   SELECT *, lag(status) OVER (ORDER BY ts) AS previous_status
   FROM   tbl
   ) sub;

db<>fiddle here

Assuming current Postgres and all columns NOT NULL.

| improve this answer | |

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.