CODEWITHSUNDEEP
sqlpostgresqlrow-number
Alibek Galiyev
Alibek Galiyev

Reputation: 33

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

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

Upvotes: 2

Views: 1462

Answers (1)

S-Man
S-Man

Reputation: 23666

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.

Upvotes: 1

Related Questions