按列和在另一列上聚合的最佳方式

Question

我想使用现有的秩和二进制列创建一个秩列。例如，假设一个表包含ID、RISK、CONTACT和DATE。现有的等级是RISK，比如1,2,3，NULL，其中3是最高的。二进制值为CONTACT with 0,1或FAILURE/SUCESS。我想创建一个新的RANK，一旦超过一定数量的成功联系人，它将由RISK订购。

例如，假设约束条件是至少有2个成功的联系人。然后，应在以下两个实例中按如下方式创建等级：

实例1。三个ID，都有至少两个成功的联系人。在这种情况下，排名反映了风险：

ID  risk  contact  date  rank
1   3     S        1     3
1   3     S        2     3
1   3     F        3     3
1   3     F        4     3
2   2     S        1     2
2   2     S        2     2
2   2     F        3     2
2   2     F        4     2
3   1     S        1     1
3   1     S        2     1
3   1     S        3     1

实例2。假设ID＝1只有一个成功的联系人。在这种情况下，它被降级到最低等级，等级=1，而ID＝2获得最高值，等级＝3，并且ID＝3映射到等级＝2，因为它满足约束但具有比ID＝2更低的风险值：

ID  risk  contact  date  rank
1   3     S        1     1
1   3     F        2     1
1   3     F        3     1
1   3     F        4     1
2   2     S        1     3
2   2     S        2     3
2   2     F        3     3
2   2     F        4     3
3   1     S        1     2
3   1     S        2     2
3   1     S        3     2

这是SQL，特别是配置单元。提前谢谢。

编辑-我认为Gordon Linoff的代码做得很正确。最后，我使用了三个临时表格。代码看起来是这样的：

首先，

--numerize risk, contact
select A.* ,
case when A.risk = 'H' then 3
when A.risk = 'M' then 2
when A.risk = 'L' then 1
when A.risk is NULL then NULL
when A.risk = 'NULL' then NULL
else -999 end as RISK_RANK,
case when A.contact = 'Successful' then 1
else NULL end as success

第二，

-- sum_successes_by_risk
select A.* ,
B.sum_successes_by_risk
from T  as A 
inner join 
(select A.person, A.program, A.risk, sum(a.success) as sum_successes_by_risk
from T as A
group by A.person, A.program, A.risk
) as B
on A.program = B.program 
and A.person = B.person
and A.risk = B.risk

第三，

--Create table that contains only max risk category
select A.* ,
B.max_risk_rank
from T as A 
inner join 
(select A.person, max(A.risk_rank) as max_risk_rank
from T as A
group by A.person
) as B
on A.person = B.person
and A.risk_rank = B.max_risk_rank

Answer 1

这很难理解，但我认为您只需要窗口函数：

select t.*,
(case when sum(case when contact = 'S' then 1 else 0 end) over (partition by id) >= 2
then risk
else 1
end) as new_risk
from t;