我在Teradata中有一个表,由以下五个主要列组成:
Old New Type_of_Old Type_of_New Change_Dt
----------------------------------------------------
A B 0 1 Date 1
B C 1 1 Date 2
C D 1 0 Date 3
D B 0 1 Date 4
B E 1 1 Date 5
我想从type_of_old=0的第一个元素开始绑定新旧值,并在到达最后一个type_of_fold=1时剪切绑定。最后我想要这个数据:
Fırst Next Change Dt
-------------------------------
A B Date 1
A C Date 2
A D Date 3
D B Date 4
D E Date 5
问题是,元素B是A和D在不同变更日期的下一个元素。因此,当我使用WITH RECURSIVE脚本时,它绑定了B的下一个元素,A和D都是起始元素。
到目前为止,我写了这个脚本,但当它达到type_of_old=1 时,我无法剪切绑定
CREATE MULTISET TABLE TEST.TABLE_A ( OLD_ELM VARCHAR(1), NEW_ELM VARCHAR(1),
TYPE_OF_OLD BYTEINT , TYPE_OF_NEW BYTEINT, CHANGE_DT DATE)
INSERT INTO TEST.TABLE_A ('A','B',0, 1, DATE '2018-01-01');
INSERT INTO TEST.TABLE_A ('B','C',1, 1, DATE '2018-02-01');
INSERT INTO TEST.TABLE_A ('C','D',1, 0, DATE '2018-03-01');
INSERT INTO TEST.TABLE_A ('D','B',0, 1, DATE '2018-04-01');
INSERT INTO TEST.TABLE_A ('B','E',1, 1, DATE '2018-05-01');
WITH RECURSIVE ELEMENT_PATH ( FIRST_ELM, NEXT_ELM, CHANGE_DT ) AS
( SELECT OLD_ELM, NEW_ELM, CHANGE_DT
FROM TEST.TABLE_A
WHERE TYPE_OF_OLD = 0
AND TYPE_OF_NEW = 1
UNION ALL
SELECT P.FIRST_ELM, A.NEW_ELM, A.CHANGE_DT
FROM ELEMENT_PATH P
JOIN TEST.TABLE_A A ON A.OLD_ELM = P.NEXT_ELM
WHERE A.CHANGE_DT > P.CHANGE_DT)
SELECT * FROM ELEMENT_PATH
此脚本中的数据如下:
FIRST_ELM NEXT_ELM CHANGE_DT
A B 01.01.2018
A C 01.02.2018
A D 01.03.2018
D B 01.04.2018
D E 01.05.2018
A B 01.04.2018 (Wrong)
A E 01.05.2018 (Wrong)
A E 01.05.2018 (Wrong)
我该怎么修?
提前感谢
问候
您需要在where子句中添加一个额外条件(AND TYPE_OF_OLD<>0(来删除错误的记录。
WITH RECURSIVE ELEMENT_PATH ( FIRST_ELM, NEXT_ELM, CHANGE_DT ) AS
( SELECT OLD_ELM, NEW_ELM, CHANGE_DT
FROM DQRC.TABLE_A
WHERE TYPE_OF_OLD = 0
AND TYPE_OF_NEW = 1
UNION ALL
SELECT P.FIRST_ELM, A.NEW_ELM, A.CHANGE_DT
FROM ELEMENT_PATH P
JOIN DQRC.TABLE_A A
ON A.OLD_ELM = P.NEXT_ELM
WHERE A.CHANGE_DT > P.CHANGE_DT
AND TYPE_OF_OLD <> 0)
SELECT * FROM ELEMENT_PATH
不确定它现在对你有用,就像我今天刚刚看到的那样。