周期函数在 TB 中未按预期工作

Question

我有以下情况，我必须更正/合并不正确的历史处理记录：

Acct_Id     status_cd   Start_Dttm       End_Dttm    ROLE_CD
1,108,389,774   11     6/10/2009 18:03  7/18/2018 4:11   6
1,108,389,774   11     7/18/2018 4:11   8/8/2018 7:18    6
1,108,389,774   8       8/8/2018 7:18   ?                6

我必须将前两条记录合并为一条历史错误的记录。 我正在使用以下查询。

SELECT acct_id,acct_status_cd,acct_status_start_dttm  ,acct_status_end_dttm ,acct_status_role_cd,run_id, data_source_type_cd,
BEGIN(PD) AS  acct_status_start_dttm , NULLIF(END(PD), TIMESTAMP '9999-12-31 00:00:00') AS acct_status_end_dttm
FROM
( 
SELECT NORMALIZE -- 
acct_id,trim(acct_status_cd) as acct_status_cd ,acct_status_start_dttm  ,acct_status_end_dttm ,acct_status_role_cd,run_id, data_source_type_cd,
PERIOD(acct_status_start_dttm,COALESCE(acct_status_end_dttm, TIMESTAMP '9999-12-31 00:00:00')) AS PD
FROM dp_dqmt.acct_stat_hh    
where acct_status_start_dttm < acct_status_end_dttm  
) AS DT

谁能说出这里有什么问题？

Answer 1

您需要删除日期列，因为它们不同，因此normalize不会合并两行：

SELECT acct_id,acct_status_cd
-- acct_status_start_dttm  ,acct_status_end_dttm ,
,acct_status_role_cd,run_id, data_source_type_cd,
Begin(PD) AS  acct_status_start_dttm , NullIf(End(PD), TIMESTAMP '9999-12-31 00:00:00') AS acct_status_end_dttm
FROM
( 
SELECT NORMALIZE -- 
acct_id,Trim(acct_status_cd) AS acct_status_cd ,
-- acct_status_start_dttm  ,acct_status_end_dttm ,
acct_status_role_cd,run_id, data_source_type_cd,
PERIOD(acct_status_start_dttm,Coalesce(acct_status_end_dttm, TIMESTAMP '9999-12-31 00:00:00')) AS PD
FROM dp_dqmt.acct_stat_hh    
WHERE acct_status_start_dttm < acct_status_end_dttm  
) AS DT

该where还将删除带有 NULL acct_status_end_dttm的最后一行，除非您将其更改为

WHERE acct_status_start_dttm < Coalesce(acct_status_end_dttm, TIMESTAMP '9999-12-31 00:00:00')