根据Teradata中的条件优先级填充列
根据Teradata中的条件优先级填充列
提问于 2018-10-29 10:53:45
我需要根据条件优先级填充一个列：
如果O_M不是零(例如：0.34)，我在同一列O_M中检查Prev. record(由TP_N排序)，如果用代码(OD03,OT03,MO03)编码的3个或更多实例为零，则应该使用当前的O_M值- 0.34填充To_Compute列。我需要对由DT,MNTH,P_ID,A_BR,D_BR,B_BR,DR排序的每个分区( TP_N )重复这一步骤。我只应该从列O_N - (OD03,OT03,MO03)中查找这些代码。
DT MNTH P_ID A_BR D_BR B_BR TP_N DR O_M O_N TO_Compute
9/29/2016 9 QT21 1506 05Y XS-123 487,006 0 0 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,007 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,008 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,009 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,010 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,011 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,012 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,013 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,014 0 0 MO03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,015 0 0 OT03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,016 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,017 0 0.34 ? 0.34
9/29/2016 9 QT21 1506 05Y XS-123 487,018 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,019 0 1.03 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,020 0 0.3 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,021 0 1.25 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,022 0 0 OP04 0
9/29/2016 9 QT21 1506 05Y XS-123 487,023 0 10.53 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,024 0 0.37 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,025 0 0.28 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,026 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,027 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,028 0 0.6 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,029 0 0.38 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,030 0 0.4 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,031 0 0.35 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,032 0 0.45 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,033 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,034 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,035 0 0 OD03 0
9/29/2016 9 QT21 1506 05Y XS-123 487,036 0 0.3 ? 0.3
9/29/2016 9 QT21 1506 05Y XS-123 487,037 0 0.35 ? 0
9/29/2016 9 QT21 1506 05Y XS-123 487,038 0 0.52 ? 0但是，如果O_M不是零(例如：0.6 -第11行从底部到O_M列)，我在同一列O_M中检查Prev. record，并且只有两个prev记录为zero (对于3个或更多使用代码(OD03,OT03,MO03)编码的实例，则应该使用0填充To_Compute列。
如果O_M不是零(例如：0.3最后一行用于O_M)，我在同一列O_M中检查Prev. record，在这里，对于使用代码(OD03,OD03,OD03)编码的3个或更多实例，它是零，那么我应该用当前的O_M值- 0.3填充To_Compute列。
我是新来的。任何关于这件事的线索都会有帮助。
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-10-29 11:49:45
还没有经过测试，但是这应该基于您的描述来工作：
case
when sum(O_M) -- previous three rows are all 0 (assuming no negative values exist)
over (partition by ??
order by TP_N
rows between 3 preceding and 1 preceding) = 0
and -- prvious three rows contain any of the searched codes
sum(case when O_N IN ('OD03','OT03','MO03') then 1 else 0 end)
over (partition by ??
order by TP_N
rows between 3 preceding and 1 preceding) = 3
then O_M
else 0
endStack Overflow用户
发布于 2018-10-30 01:45:37
我将提供两个可能的解决方案，让您决定哪一个最适合您的情况。第一种方法更简单，但更繁琐，因为它只需3次连接到同一个表。假设以上数据存在于DATASET表中：
select ds1.dt
,ds1.mnth
,ds1.p_id
,ds1.a_br
,ds1.d_br
,ds1.b_br
,ds1.tp_n
,ds1.dr
,ds1.o_m
,ds1.o_n
,case when zeroifnull(ds4.o_m) + zeroifnull(ds3.o_m) + zeroifnull(ds2.o_m) = 0 and ds4.o_n in ('OD03','OT03','MO03') and ds3.o_n in ('OD03','OT03','MO03') and ds2.o_n in ('OD03','OT03','MO03') then ds1.o_m
else 0 end as TO_COMPUTE
from dataset ds1
left join dataset ds2
on ds1.tp_n = ds2.tp_n +1
and ds1.dt = ds2.dt
and ds1.mnth = ds2.mnth
and ds1.p_id = ds2.p_id
and ds1.a_br = ds2.a_br
and ds1.d_br = ds2.d_br
and ds1.b_br = ds2.b_br
and ds1.dr = ds2.dr
left join dataset ds3
on ds1.tp_n = ds3.tp_n +2
and ds1.dt = ds3.dt
and ds1.mnth = ds3.mnth
and ds1.p_id = ds3.p_id
and ds1.a_br = ds3.a_br
and ds1.d_br = ds3.d_br
and ds1.b_br = ds3.b_br
and ds1.dr = ds3.dr
left join dataset ds4
on ds1.tp_n = ds4.tp_n +3
and ds1.dt = ds4.dt
and ds1.mnth = ds4.mnth
and ds1.p_id = ds4.p_id
and ds1.a_br = ds4.a_br
and ds1.d_br = ds4.d_br
and ds1.b_br = ds4.b_br
and ds1.dr = ds4.dr
order by 7;第二种方法使用子查询中的分区：
select sub.dt
,sub.mnth
,sub.p_id
,sub.a_br
,sub.d_br
,sub.b_br
,sub.tp_n
,sub.dr
,sub.o_m
,sub.o_n
,case when o_m2 = 0 and o_m3 = 0 and o_m4 = 0 and o_n2 in ('OD03','OT03','MO03') and o_n4 in ('OD03','OT03','MO03') and o_n4 in ('OD03','OT03','MO03') then sub.o_m
else 0 end as TO_COMPUTE
from
(
select ds.dt
,ds.mnth
,ds.p_id
,ds.a_br
,ds.d_br
,ds.b_br
,ds.tp_n
,ds.dr
,ds.o_m
,ds.o_n
,max(ds.o_m) over (partition by ds.dt, ds.mnth, ds.p_id, ds.a_br, ds.d_br, ds.b_br, ds.dr order by ds.tp_n rows between 1 preceding and 1 preceding) as O_M2
,max(ds.o_m) over (partition by ds.dt, ds.mnth, ds.p_id, ds.a_br, ds.d_br, ds.b_br, ds.dr order by ds.tp_n rows between 2 preceding and 2 preceding) as O_M3
,max(ds.o_m) over (partition by ds.dt, ds.mnth, ds.p_id, ds.a_br, ds.d_br, ds.b_br, ds.dr order by ds.tp_n rows between 3 preceding and 3 preceding) as O_M4
,max(ds.o_n) over (partition by ds.dt, ds.mnth, ds.p_id, ds.a_br, ds.d_br, ds.b_br, ds.dr order by ds.tp_n rows between 1 preceding and 1 preceding) as O_N2
,max(ds.o_n) over (partition by ds.dt, ds.mnth, ds.p_id, ds.a_br, ds.d_br, ds.b_br, ds.dr order by ds.tp_n rows between 2 preceding and 2 preceding) as O_N3
,max(ds.o_n) over (partition by ds.dt, ds.mnth, ds.p_id, ds.a_br, ds.d_br, ds.b_br, ds.dr order by ds.tp_n rows between 3 preceding and 3 preceding) as O_N4
from dataset ds
) sub
order by 7;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53043962
复制相关文章
相似问题
腾讯云开发者
