I’d like to increment an id by group, for each variation of an attribute with only 2 values. like this :
1 banana 2 banana 3 egg 4 egg 5 banana 6 egg 7 egg
result wanted
id food grp 1 banana 1 2 banana 1 3 egg 2 4 egg 2 5 banana 3 6 egg 4 7 egg 4
I tried dense_rank() with window function but i only gives me 1 or 2 in grp column
Advertisement
Answer
You can use window functions to solve this gaps and islands problem. Her is an approch using lag()
and a window count()
:
select id, food, count(*) filter(where food is distinct from lag_food) over(order by id) grp from ( select t.*, lag(food) over(order by id) lag_food from mytable t ) t
The logic is to compare each food to the value of the “previous” row, and count how many times it changed.
id | food | grp -: | :----- | --: 1 | banana | 1 2 | banana | 1 3 | egg | 2 4 | egg | 2 5 | banana | 3 6 | egg | 4 7 | egg | 4