Calculate Final outcome based on Results/ID

2019-08-23 02:28发布

问题:

For a Table T1

+----------+-----------+-----------------+
| PersonID |   Date    |   Employment    |
+----------+-----------+-----------------+
|        1 | 2/28/2017 | Stayed the same |
|        1 | 4/21/2017 | Stayed the same |
|        1 | 5/18/2017 | Stayed the same |
|        2 | 3/7/2017  | Improved        |
|        2 | 4/1/2017  | Stayed the same |
|        2 | 6/1/2017  | Stayed the same |
|        3 | 3/28/2016 | Improved        |
|        3 | 5/4/2016  | Improved        |
|        3 | 4/19/2017 | Worsened        |
|        4 | 5/19/2016 | Worsened        |
|        4 | 2/16/2017 | Improved        |
+----------+-----------+-----------------+

I'm trying to calculate a Final Result field partitioning on Employment/PersonID fields, based on the latest result/person relative to prior results. What I mean by that is explained in the logic behind Final Result:

For every Person,

  1. If all results/person are Stayed the same, then only should final result for that person be "Stayed the same"

  2. If Worsened/Improved are in the result set for a person, the final result should be the latest Worsened/Improved result for that person, irrespective of "Stayed the same" after a W/I result.

Eg:

  • Person 1 Final result -> Stayed the same, as per (1)
  • Person 2 Final result -> Improved, as per (2)
  • Person 3 Final result -> Worsened, as per (2)
  • Person 4 Final result -> Improved, as per (2)

Desired Result:

+----------+-----------------+
| PersonID |  Final Result   |
+----------+-----------------+
|        1 | Stayed the same |
|        2 | Improved        |
|        3 | Worsened        |
|        4 | Improved        |
+----------+-----------------+

I know this might involve Window functions or Sub-queries but I'm struggling to code this.

回答1:

Hmmm. This is a prioritization query. That sounds like row_number() is called for:

select t1.personid, t1.employment
from (select t1.*,
             row_number() over (partition by personid
                                order by (case when employment <> 'Stayed the same' then 1 else 2 end),
                                         date desc
                               ) as seqnum
      from t1
     ) t1
where seqnum = 1;