0

I have a Postgres table like this:

 id | value
----+-------
  1 |   100
  2 |   100   
  3 |   100   
  4 |   100       
  5 |   200
  6 |   200    
  7 |   200       
  8 |   100
  9 |   100
 10 |   300

I'd have a table like this

 id | value   |new_id
----+---------+-----
  1 |   100   |  1
  2 |   100   |  1
  3 |   100   |  1
  4 |   100   |  1 
  5 |   200   |  2
  6 |   200   |  2
  7 |   200   |  2
  8 |   100   |  3
  9 |   100   |  3
 10 |   300   |  4

I'd have a new field with a new_id that change when value change and remain the same until value changes again. My question is similar this but I cannot found a solution.

Community
  • 1
  • 1
franco_b
  • 868
  • 3
  • 13
  • 31

1 Answers1

3

You can identify sequences where the value is the same by using a difference of row_number(). After getting the difference, you have a group identifier and can calculate the minimum id for each group. Then, dense_rank() will renumber the values based on this ordering.

It looks like this:

select t.id, t.value, dense_rank() over (order by minid) as new_id
from (select t.*, min(id) over (partition by value, grp) as minid
      from (select t.*,
                   (row_number() over (order by id) - row_number() over (partition by value order by id)
                   ) as grp
            from table t
           ) t
     ) t

You can see what happens to your sample data:

 id | value | grp | minid | new_id |
----+-------+-----+-------+--------+
  1 |   100 |  0  |   1   |    1   |
  2 |   100 |  0  |   1   |    1   | 
  3 |   100 |  0  |   1   |    1   |
  4 |   100 |  0  |   1   |    1   |
  5 |   200 |  4  |   5   |    2   |
  6 |   200 |  4  |   5   |    2   |
  7 |   200 |  4  |   5   |    2   |
  8 |   100 |  3  |   8   |    3   |
  9 |   100 |  3  |   8   |    3   |
 10 |   300 |  9  |  10   |    4   |
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I have naturally a more complicated table and I just realize that your method doesn't work. Casually in a certain point the difference from grp and minid return the same number. If you think it's possible. If you want i can post the real table – franco_b Nov 18 '14 at 11:03
  • @franco_b . . . I would suggest that you ask another question with more details. As for your comment, that the difference is the same is irrelevant, because the `min()` uses both `value` and `grp`. – Gordon Linoff Nov 18 '14 at 12:30