我手头有一项有趣的任务,我正在想办法做
假设我在一个表中有以下数据:
Num1 Acct Amt Type1 Type2 AmtX AmtY AcctBadInd
X12 111 90 X 1 NULL NULL NULL
X12 222 -90 X 1 NULL NULL NULL
X12 333 90 X 1 NULL NULL NULL
Y33 111 75 Y 1 NULL NULL NULL
Y33 444 -75 Y 1 NULL NULL NULL
Z44 111 55 Y 1 NULL NULL NULL
Z44 111 55 Y 0 NULL NULL NULL
Z44 444 -65 Y 1 NULL NULL NULL
下面是几个例子。唯一需要注意的是,给定的Num1可以有任何数量的记录,但总是>=2.所以它可以是2,3,4,5,同样的逻辑适用于所有情况。
验证所有
Type2=1
记录的ABS(AMT)
是否相同。如果所有3条记录都具有相同的AMT,则该Num1的SET AMTX=ABS(AMT)
。或者,如果Type1 was Y for X12
-那么我们将更新AmtY = ABS(AMT)
Num1 = Y33
-在这种情况下,我们再次希望验证ABS(AMT)
与Type2=1
相同。如果它们相等,那么由于Type1=Y
,我们将设置AmtY =75
Num1=Z44
——在这种情况下,再次验证ABS(AMT(与Type2=1
相同。如果它们不相等,则不要更新AmtY,而是设置AcctBadInd = 1
最终结果
Num1 Acct Amt Type1 Type2 AmtX AmtY AcctBadInd
X12 111 90 X 1 90 NULL NULL
X12 222 -90 X 1 90 NULL NULL
X12 333 90 X 1 90 NULL NULL
Y33 111 75 Y 1 NULL 75 NULL
Y33 444 -75 Y 1 NULL 75 NULL
Z44 111 55 Y 1 NULL NULL 1
Z44 444 -65 Y 1 NULL NULL 1
Z44 111 55 Y 0 NULL NULL NULL
我正在努力解决这个问题,我不希望得到答案,但至少希望得到一个提示或任何帮助,这样我就可以上路了。更重要的是,如果这在我想象的不写多少代码的情况下是可行的。
如果我理解正确的话,下面是如何做到的:
with cte as (
select * , type2, max(abs(Amt)) minAmt, min(abs(Amt)) maxAmt, count(*) cnt
from table
group by Num1 , type2
);
update t1
set AmtX = case when cnt> 1 and Type2=1 and t1.type1 = 'X' and minAmt = maxAmt then minAmt end
, AmtY = case when cnt> 1 and Type2=1 and t1.type1 = 'Y' and minAmt = maxAmt then minAmt end
, AcctBadInd = case when cnt> 1 and Type2=1 and minAmt <> maxAmt then 1 end
from table t1
join cte on t1.Num1 = cte.Num1
and t1.type2 = cte.type2
使用窗口函数
with t as (
select *,
AmtXnew = case when min(ABS(AMT)) over(partition by Num1, Type2) = max(ABS(AMT)) over(partition by Num1, Type2)
and Type2 = 1 and Type1 ='X' then ABS(AMT)
else NULL end,
AmtYnew = case when min(ABS(AMT)) over(partition by Num1, Type2) = max(ABS(AMT)) over(partition by Num1, Type2)
and Type2 = 1 and Type1 ='Y' then ABS(AMT)
else NULL end,
AcctBadIndnew = case when min(ABS(AMT)) over(partition by Num1, Type2) <> max(ABS(AMT)) over(partition by Num1, Type2)
and Type2 = 1 then 1 else NULL end
from tbl
)
update t set AmtX = AmtXnew,AmtY = AmtYnew, AcctBadInd = AcctBadIndnew;