Reset Row_Number Window Function-CodePudding

I have data that looks like this in postgres

My row_num column is created like this: ROW_NUMBER() OVER (PARTITION BY dept_id, name, status ORDER BY dept_id, name, status) as row_num

dept_id      name     status     row_num
1            227      occupied   1
1            227      occupied   2
1            227      vacant     1
1            227      vacant     2
1            227      occupied   3
1            227      occupied   4
1            227      vacant     3
1            227      vacant     4

This is what I want it to look like:

dept_id      name     status     row_num
1            227      occupied   1
1            227      occupied   2
1            227      vacant     1
1            227      vacant     2
1            227      occupied   1
1            227      occupied   2
1            227      vacant     1
1            227      vacant     2

Any suggestions?

CodePudding user response：

You can use recursive CTE to create the result you expected.

Data structure and query result: dbfiddle

with recursive
    cte_r as (
        select dept_id,
               name,
               status,
               row_number() over (partition by dept_id, name) as rn
        from test),
    cte as (
        select dept_id,
               name,
               status,
               rn,
               1 as grp
        from cte_r
        where rn = 1
        union all
        select cr.dept_id,
               cr.name,
               cr.status,
               cr.rn,
               case
                   when cr.status = c.status then grp   1
                   else 1
                   end
        from cte c,
             cte_r cr
        where c.rn = cr.rn - 1)
select dept_id,
       name,
       status,
       grp as row_num
from cte;