Pfthb

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