How to get a continuous group of id

Question

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

Answer 1

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.

Demo on DB Fiddle:

id | food   | grp
-: | :----- | --:
 1 | banana |   1
 2 | banana |   1
 3 | egg    |   2
 4 | egg    |   2
 5 | banana |   3
 6 | egg    |   4
 7 | egg    |   4