时间序列“状态”计算

Question

样本数据：

rep_signup_date rep_id client_registration_date client_id 
1/2/2018        1      1/5/2018                 1          
1/2/2018        1      1/9/2018                 2
1/2/2018        1      2/15/2018                3
1/4/2018        2      2/3/2018                 4
1/4/2018        2      3/9/2018                 5
2/1/2018        3      2/2/2018                 6

我们对rep“状态”进行分类的方式是基于#客户端:1客户端-状态1，2客户端-状态2，3+客户端-状态3，因此在当前日期我们知道以下信息：

select rep_signup_date, rep_id,  
case when count(client_id) over (partition by rep_id) >=3 then '3'
     when count(client_id)  over (partition by rep_id) =2 then '2'
     when count(client_id)  over (partition by rep_id) =1 then '1'
     end status
from reps r
left join clients c on c.rep_id=r.id

rep_signup_date rep_id  status
1/2/2018        1       3     
1/4/2018        2       2
2/1/2018        3       1

但是，这些状态是从当前日期开始的；我尝试为月份添加date_trunc('month', client_registration_date)::date，但它仍然根据最大日期(而不是静态时间点)将数据作为当前快照。

我想要做的是在每个月底获得状态-例如，rep 1在1月底是状态2。

预期输出：

rep_signup_date rep_id month    status
1/2/2018        1      1/1/2018 2
1/2/2018        1      2/1/2018 3     
1/4/2018        2      2/1/2018 1
1/4/2018        2      3/1/2018 2
2/1/2018        3      2/1/2018 1

我该怎样走？谢谢。

Answer 1

使用order by

select rep_signup_date, rep_id,  
       (case when count(client_id) over (partition by rep_id order by client_registration_date rows between unbounded preceding and current row) >= 3 then '3'
             when count(client_id) over (partition by rep_id order by client_registration_date rows between unbounded preceding and current row) = 2 then '2'
             when count(client_id) over (partition by rep_id order by client_registration_date rows between unbounded preceding and current row) = 1 then '1'
        end) as status
from reps r left join
     clients c
     on c.rep_id = r.id;

您似乎每个客户端/rep有一行，所以使用row_number()比使用累积计数要简单得多：

select rep_signup_date, rep_id,  
       (case when row_number() over (partition by rep_id order by client_registration_date ) >= 3 then '3'
             when row_number() over (partition by rep_id order by client_registration_date rows) = 2 then '2'
             when row_number() over (partition by rep_id order by client_registration_date = 1 then '1'
        end) as status
from reps r left join
     clients c
     on c.rep_id = r.id;

这可以进一步简化为：

select rep_signup_date, rep_id,  
       (case row_number() over (partition by rep_id order by client_registration_date ) >= 3
             when 1 then '1'
             when 2 then '2'
             else '3'
        end) as status
from reps r left join
     clients c
     on c.rep_id = r.id;

甚至：

select rep_signup_date, rep_id,  
       greatest(row_number() over (partition by rep_id order by client_registration_date ), 3) as status
from reps r left join
     clients c
     on c.rep_id = r.id;