CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
franco_b
franco_b

Reputation: 878

Postgresql change value based on the change of another field

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.

Upvotes: 0

Views: 619

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269863

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   |

Upvotes: 3

Related Questions