In PostgreSQL, I have a table with a column that has long repeats of 0's and 1's. I would like to create another column that ranks these with repeating ranks. Here is an example of what I'd like to see:
| Column | New column |
|---|---|
| 0 | 1 |
| 0 | 1 |
| 0 | 1 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 0 | 3 |
| 0 | 3 |
| 0 | 3 |
| 0 | 3 |
| 0 | 3 |
| 1 | 4 |
| 1 | 4 |
| 0 | 5 |
| 0 | 5 |
| 0 | 5 |
and so on. Can the table functions with rank be used effectively to do this? Or is this something that is beyond the window function capabilities? Thanks.
You can use window functions; we can get the previous value with lag, then compute the window count of value changes to define the group each record belongs to.
Of course this requires a column to order the table, say id:
select id, col,
count(*) filter(where col is distinct from lag_col) over(order by id) grp
from (
select t.*, lag(col) over(order by id) as lag_col
from mytable t
) t
order by id
| id | col | grp |
|---|---|---|
| 1 | 0 | 1 |
| 2 | 0 | 1 |
| 3 | 0 | 1 |
| 4 | 1 | 2 |
| 5 | 1 | 2 |
| 6 | 1 | 2 |
| 7 | 1 | 2 |
| 8 | 0 | 3 |
| 9 | 0 | 3 |
| 10 | 0 | 3 |
| 11 | 0 | 3 |
| 12 | 0 | 3 |
| 13 | 1 | 4 |
| 14 | 1 | 4 |
| 15 | 0 | 5 |
| 16 | 0 | 5 |
| 17 | 0 | 5 |