根据结果​​/ID 计算最终结果 [英] Calculate Final outcome based on Results/ID

问题描述

对于表 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        |
+----------+-----------+-----------------+

我正在尝试根据相对于先前结果的最新结果/人员计算就业/人员 ID 字段上的 Final Result 字段分区.Final Result 背后的逻辑解释了我的意思:

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:

对于每个人，

1. 如果所有结果/人都保持不变，则只应最终那个人的结果是保持不变"

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

如果恶化/改善在一个人的结果集中，最终结果应该是该人的最新恶化/改善结果，无论在 W/I 结果后是否保持不变".

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.

例如:

• 第 1 个人的最终结果 -> 保持不变，按照 (1)
• 第 2 个人最终结果 -> 改进，根据 (2)
• 第 3 个人最终结果 -> 恶化，如 (2)
• 第 4 个人的最终结果 -> 改进，根据 (2)

预期结果:

+----------+-----------------+
| 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.

推荐答案

嗯.这是一个优先级查询.这听起来像 row_number() 被调用:

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;

这篇关于根据结果​​/ID 计算最终结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！