PostgreSQL Window Functions – Adding Running Count of State Changes

gaps-and-islandspostgresqlwindow functions

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?

Best Answer

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:

Related Solutions

PostgreSQL – Counting 3 Consecutive Unsynced Dates

This counts ranges of at least 3 days with minutes_asleep = 0.
A continuous range of 5 days still counts as 1.
A continuous range of 6 days counts as 2. Etc.
Ignoring all other entries where minutes_asleep is different.

SELECT sum(ct) AS total_count
FROM  (
   SELECT (count(*)/3)::int AS ct  -- integer division truncates as desired + see below
   FROM (
      SELECT user_id
           , the_date - (row_number() OVER (PARTITION BY user_id
                                            ORDER BY the_date))::int AS grp
      FROM   fitness_data
      WHERE  minutes_asleep = 0
      ) sub1
   GROUP  BY user_id, grp
   HAVING count(*) >= 3
   ) sub2
UNION ALL  -- see below
SELECT 0
LIMIT 1;

UNION ALL ... is just to return 0 instead of "no row" where no qualifying ranges are found. You could also use COALESCE in another subselect, but I find this more elegant.

Why (count(*)/3)::int? count() returns bigint. sum(bigint) returns numeric. I am pretty positive we don't need to go that far. Casting back to int, so we get a bigint result. Should be faster, too.

Postgresql – extract state changes from snapshot-based archive table

I came up with the following query:

    with city_before as (
        select person, city
        from (
            select 
                person, 
                timestamp, 
                first_value(timestamp) over (partition by person order by timestamp desc) as latest_timestamp, 
                first_value(city) over (partition by person order by timestamp desc) as city
            from snapshot
            where timestamp < '2006-02-01'
        ) _
        where timestamp = latest_timestamp
    ),
    city_during as (
        select person, city
        from snapshot
        where timestamp between '2006-02-01' and '2006-02-15'
        group by person, city
    )
    select distinct person
    from (
        select s.person, b.city as city_before, s.city as city
        from city_during s
        left outer join city_before b on b.person = s.person
        where b.city <> s.city and s.city = 'NY'
    ) _;

Best Answer

Related Solutions

PostgreSQL – Counting 3 Consecutive Unsynced Dates

Postgresql – extract state changes from snapshot-based archive table

Related Question