PostgreSQL row_number( ) window function starting counter from 1 for each change

Question

Can't find a way to implement a specific row_number() function in Postgres. See the table below:

table result

I want from row_number to start counter from one for each difference like for items a and c. Third column is what I want.

my code:

select item, flag, row_number() over (partition by item, flag order by item) as rownum from t

item	flag	day	row_number_current	row_number_required
a	0	1	1	1
a	0	2	2	2
a	1	3	1	1
a	1	4	2	2
a	1	5	3	3
a	0	6	3	1
a	1	7	4	1
a	1	8	5	2
b	0	1	1	1
b	1	2	1	1
b	1	3	2	2
b	1	4	3	3
b	1	5	4	4
c	1	1	1	1
c	1	2	2	2
c	0	3	1	1
c	0	4	2	2
c	1	5	3	1
c	1	6	4	2
c	1	7	5	3

Answer 1

demo:db<>fiddle

It's the same problem, I discussed in detail here:

Window functions: PARTITION BY one column after ORDER BY another

You are trying to create partitions by two columns, but try to keep the order indicated by a third. Problem is that a PARTITION BY clause internally orders before the partition is created. So you need to introduce an identifier which keeps the order.

SELECT 
    *, 
    SUM(is_diff) OVER (PARTITION BY item ORDER BY day) -- 2
FROM (
    SELECT 
        *,
        CASE                                           -- 1
            WHEN flag = lag(flag) OVER (PARTITION BY item ORDER BY day) 
            THEN 0 
            ELSE 1 
        END as is_diff
    FROM mytable 
)s

1. Check if the next value equals the current value. If not, make it 1 (indicates a value change), else 0 (no value change).
2. Cumulative sum: Each 1 increases the value, each 0 keeps the previous. This yields a new group id beginning at each 1. This is exactly the result you are expecting.