Cfrgtkky

I have a PostgreSQL table containing: person_identifier, period_identifier, status

 person | period | status 

 -------+--------+--------

   Bob  |   Jan  | new    

   Bob  |   Feb  | retained    

   Bob  |   Mar  | retained 

   Bob  |   Apr  | dormant 

   Bob  |   May  | dormant 

   Bob  |   Jun  | resurected 

   Bob  |   Jul  | retained

   Bob  |   Agu  | dormant

   Jim  |   Jan  | new

   Jim  |   Feb  | dormant

   Jim  |   Mar  | dormant

   Jim  |   Apr  | dormant 

   Jim  |   May  | dormant

   Jim  |   Jun  | resurected

   Jim  |   Jul  | dormant

   Jim  |   Agu  | resurected

What I need is to include a counter grouping by person, status, with the restriction that the counter needs to restart down to 1 whenever the status changes.

I tried the following query, but this doesn't reset the counter down to 1 whenever a status changes:

SELECT

    *,

    ROW_NUMBER() OVER (PARTITION BY person, status ORDER BY period) AS wrong_counter

FROM

    my_table

Here's the difference of my query and what I actually need; * stands for wrong value:

 person | period |    status   | wrong_counter | needed_counter

 -------+--------+-------------+ --------------+---------------

   Bob  |   Jan  | new         |      1        |       1       

   Bob  |   Feb  | retained    |      1        |       1       

   Bob  |   Mar  | retained    |      2        |       2       

   Bob  |   Apr  | dormant     |      1        |       1       

   Bob  |   May  | dormant     |      2        |       2       

   Bob  |   Jun  | resurected  |      1        |       1       

   Bob  |   Jul  | retained    |      3*       |       1       

   Bob  |   Agu  | dormant     |      3*       |       1       

   Jim  |   Jan  | new         |      1        |       1       

   Jim  |   Feb  | dormant     |      1        |       1       

   Jim  |   Mar  | dormant     |      2        |       2       

   Jim  |   Apr  | dormant     |      3        |       3       

   Jim  |   May  | dormant     |      4        |       4       

   Jim  |   Jun  | resurected  |      1        |       1       

   Jim  |   Jul  | dormant     |      5*       |       1       

   Jim  |   Agu  | resurected  |      2*       |       1       

Can anyone help me with this?

i did some normalisation :

person -> person_fk,

period -> period_int, Jan = 1 ...

status -> status_fk

and use as basetable : public.tbl_test

then i do some calculations: first get the status in the line before the actual line and is it a status_change or not.
make a help_partition and then get the row_number over the help_partition.

with temp_base_data as 

(

select

 *,

lag(status_fk,1,-1) over(ORDER BY person_fk, period_int) as status_before,

case

when lag(status_fk,1,-1)  over(ORDER BY person_fk, period_int) = status_fk and  lag(person_fk ,1,-1) over(ORDER BY person_fk, period_int) = person_fk 

    then 0

else 1

end as status_change

from public.tbl_test

order by person_fk, period_int

),

temp_partition  AS

(

select 

*,

sum(status_change) over ( order by  person_fk, period_int  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as help_partition

from temp_base_data

order by person_fk, period_int

)



select

 * ,

row_number() over (PARTITION by help_partition order by person_fk, period_int) as counter

from temp_partition 

order by id

and result in : (last row is the counter you need)

person  period  status  id  period_int  person_fk   status_fk   status_before   status_change   help_partition  counter

   Bob     Jan   new        1   1   1   1   -1  1   1   1

   Bob     Feb   retained   2   2   1   2   1   1   2   1

   Bob     Mar   retained   3   3   1   2   2   0   2   2

   Bob     Apr   dormant    4   4   1   3   2   1   3   1

   Bob     May   dormant    5   5   1   3   3   0   3   2

   Bob     Jun   resurected 6   6   1   4   3   1   4   1

   Bob     Jul   retained   7   7   1   2   4   1   5   1

   Bob     Agu   dormant    8   8   1   3   2   1   6   1

   Jim     Jan   new        9   1   2   1   3   1   7   1

   Jim     Feb   dormant    10  2   2   3   1   1   8   1

   Jim     Mar   dormant    11  3   2   3   3   0   8   2

   Jim     Apr   dormant    12  4   2   3   3   0   8   3

   Jim     May   dormant    13  5   2   3   3   0   8   4

   Jim     Jun   resurected 14  6   2   4   3   1   9   1

   Jim     Jul   dormant    15  7   2   3   4   1   10  1

   Jim     Agu   resurected 16  8   2   4   3   1   11  1