postgres how to increment counter only if value changes

Question

I have a table with positions of animals sorted by time and locations. I want to increment a counter when ever the location changes from one to the next ID. So each "Stay" of the animal should get an unique ID (Counter). I have a non-SQL description:

counter = 0
CASE (location at ID = location at ID-1) SET counter+1

I tried to use Dense_Rank() but failed to set the partition right. Here is a sample with the desired counter.

ID Location Counter
1    3          1
2    3          1
3    2          2
4    2          2
5    3          3 
6    1          4 
7    3          5 
8    3          5 
9    3          5

CREATE TABLE locations (idn serial PRIMARY KEY, location integer);
INSERT INTO locations (location ) VALUES (3);
INSERT INTO locations (location ) VALUES (3);
INSERT INTO locations (location ) VALUES (2);
INSERT INTO locations (location ) VALUES (2);
INSERT INTO locations (location ) VALUES (3);
INSERT INTO locations (location ) VALUES (1);
INSERT INTO locations (location ) VALUES (3);
INSERT INTO locations (location ) VALUES (3);
INSERT INTO locations (location ) VALUES (3);

Tsvetelina Mariyanova · Accepted Answer · 2020-05-26 10:11:30Z

3

you can try the lag function.

Here is one example that works in Big SQL:

select id, location,
    sum(case when previousVal = location then 0 else 1 end) over(order by id) as Counter
from (
    select Id, location, lag(location, 1) over(order by Id) as previousVal
    from temp.locations
) tbl
order by Id;

Output:

ID LOCATION COUNTER1
1       3       1
2       3       1
3       2       2
4       2       2
5       3       3
6       1       4
7       3       5
8       3       5
9       3       5

answered May 26, 2020 at 10:11

Tsvetelina Mariyanova

3594 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres how to increment counter only if value changes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related