计算错误的条件状态

Question

我有以下查询：

select rep_id, r.onboarded_at, user_id, u.created_at, pi.applied_at,
    case when count(user_id) over (partition by rep_id order by convert_timezone('PST', pi.applied_at) rows unbounded preceding) >= 5 then 'x'
         when count(user_id) over (partition by rep_id order by convert_timezone('PST', pi.applied_at) rows unbounded preceding) >= 3 then 'y'
         when count(user_id) over (partition by rep_id order by convert_timezone('PST', pi.applied_at) rows unbounded preceding) >= 1 then 'z'
         else null
         end status
    FROM users u
    left OUTER JOIN reps r on r.id=u.advisor_id
    left outer join info pi on r.id = pi.g_id

电流(不希望的(输出：

rep_id  onboarded_at    user_id created_at  applied_at  status
180     12/7/2016       2999    6/7/2017    6/7/2017    x
180     12/7/2016       4223    7/30/2017   8/11/2017   x
180     12/7/2016       25064   12/14/2017  12/25/2017  y
180     12/7/2016       33046   1/5/2018    (null)      z
180     12/7/2016       33818   1/8/2018    (null)      z
180     12/7/2016       43814   3/22/2018   3/28/2018   y
180     12/7/2016       43978   3/23/2018   (null)      z
180     12/7/2016       43445   3/27/2018   3/29/2018   z
180     12/7/2016       43567   3/30/2018   (null)      z

预期输出：

rep_id  onboarded_at    user_id created_at  applied_at  status
180     12/7/2016       2999    6/7/2017    6/7/2017    x
180     12/7/2016       4223    7/30/2017   8/11/2017   x
180     12/7/2016       25064   12/14/2017  12/25/2017  y
180     12/7/2016       33046   1/5/2018    (null)      **y**
180     12/7/2016       33818   1/8/2018    (null)      **y**
180     12/7/2016       43814   3/22/2018   3/28/2018   y
180     12/7/2016       43978   3/23/2018   (null)      **y**
180     12/7/2016       43445   3/27/2018   3/29/2018   z
180     12/7/2016       43567   3/30/2018   (null)      z

基本上，每当applied_at为null时：出于某种原因，它会获得自动状态='z'，但是上一个记录的状态应将其置于下一个；只有下一个非null applied_at才能更改状态(如果通过> = 1，> = 3，> = 5的相应阈值，则可以更改状态。

有什么想法如何纠正？谢谢。

Answer 1

您似乎想通过created_at订购，而不是applied_at或两者的组合：

select rep_id, r.onboarded_at, user_id, u.created_at, pi.applied_at,
       (case when count(user_id) over (partition by rep_id order by convert_timezone('PST', coalesce(pi.applied_at, pi.created_id)) rows unbounded preceding) >= 5 then 'x'
             when count(user_id) over (partition by rep_id order by convert_timezone('PST', coalesce(pi.applied_at, pi.created_id)) rows unbounded preceding) >= 3 then 'y'
             when count(user_id) over (partition by rep_id order by convert_timezone('PST', coalesce(pi.applied_at, pi.created_id)) nulls unbounded preceding) >= 1 then 'z'
             else null
        end) status
from users u left join
     reps r
     on r.id = u.advisor_id left join
     info pi
     on r.id = pi.g_id;