请选择 进入手机版 | 继续访问电脑版
MSIPO技术圈 首页 IT技术 查看内容

数据仓库-拉链算法

2023-07-13

数据仓库-拉链算法,如何处理开链、闭链数据

\timing on
set client_encoding='GBK';
/******程序功能说明*********************************/


/*****以下根据任务不同进行变量设置*****************/

SELECT
	:'AUTO_PDMVIEW' 	AS "PDM_VIEW"
	,:'20230710' 		AS "20230710"
	 ,:'AUTO_exampleDB' 		AS "example_DATA"
	,:'AUTO_SDATADB' 	AS "SDATA"
	,:'AUTO_exampleVIEW' 	AS "example_VIEW"
	,:'AUTO_ODSVIEW' 	AS "ODS_VIEW"
	,:'AUTOVIEW' 	AS "SUM_VIEW"
	,:'AUTOEVIEW' 	AS "CDE_VIEW"
	--,'examplesc_read'      AS    "example_DATA"	
	,:'MAX_DT' 			AS "MAX_DT"
	,:'NULL_DT' 		AS "NULL_DT"
; 
\gset

/******以下为程序主体******************************/

数据示例:
Start_Dt   End_Dt
20230601  30001231
20230710  30001231 (正常无数据)
20230501  20230710 (正常无数据)

DELETE FROM :example_DATA.INT_D_example_ACCT_CAP_CHANGE
WHERE Start_Dt = TO_DATE(:'20230710','YYYYMMDD')
and sys_src='exam';


Start_Dt   End_Dt
20230601  30001231
20230501  20230710

UPDATE :example_DATA.int_d_example_acct_cap_change
SET End_Dt = TO_DATE('30001231','YYYYMMDD')
WHERE End_Dt = TO_DATE(:'20230710','YYYYMMDD')
and sys_src='exam';


Start_Dt   End_Dt
20230601  30001231
20230501  30001231

CREATE TEMPORARY TABLE int_d_example_acct_cap_change_ND(LIKE :example_DATA.int_d_example_acct_cap_change INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
ON COMMIT PRESERVE ROWS;



INSERT INTO int_d_example_acct_cap_change_ND
(asset_acct
,securities_code
 ,TO_DATE(:'TX_DATE','YYYYMMDD')AS  update_dt
 ,TO_DATE(:'TX_DATE','YYYYMMDD') AS Start_Dt     
 ,TO_DATE('30001231','YYYYMMDD') AS End_Dt 
)
select 
  COALESCE(TRIM(CAST(A.fundid AS bigint)), '')  asset_acct  ---资金账户
, case when trim(b.belong_exampleflag)='0' then 'E1005731000018' else 'E1013565000016' end as securities_code   ---金融机构编码
from  :ODS_VIEW.ODS_exam_FUNDINFO   A 
left join :CDE_VIEW.examplebroke_branch  B 
on (case  when  TRIM(A.orgid) = '0000' then  'ORG-999'
          else  COALESCE('ORG-' || TRIM(A.orgid), '')
    END)=trim(B.branch)
   WHERE A.Ods_Start_Dt <=:'20230710'
  AND A.Ods_End_Dt >:'20230710' 
;
-----插入原数据
CREATE TEMPORARY TABLE int_d_example_acct_cap_change_OD(LIKE :example_DATA.int_d_example_acct_cap_change INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
ON COMMIT PRESERVE ROWS;
--插入新数据
CREATE TEMPORARY TABLE int_d_example_acct_cap_change_W_I(LIKE :example_DATA.int_d_example_acct_cap_change INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
ON COMMIT PRESERVE ROWS;

----插入更新的数据
CREATE TEMPORARY TABLE int_d_example_acct_cap_change_W_U(LIKE :example_DATA.int_d_example_acct_cap_change INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
ON COMMIT PRESERVE ROWS;9号全部开链的切片数据插入

数据错乱情况:
1、该闭链的开链了,这样的话该条数据取到,但是在处理的时候会被闭链,10号数据里会新增一条,开链
2、该开链的闭链了,这样的话该条数据取不到,10号数据里会新增一条,并且开链
INSERT INTO int_d_example_acct_cap_change_OD
SELECT * FROM :example_DATA.int_d_example_acct_cap_change
WHERE Start_Dt <= TO_DATE(:'20230710','YYYYMMDD')
AND   End_Dt   > TO_DATE(:'20230710','YYYYMMDD')
AND   sys_src='exam'
;


全字段找出有变动、或者新增的数据,插入W_I表(比如10号的数据,不在9号的里面),如果数据有减少,则找不出来
INSERT INTO int_d_example_acct_cap_change_W_I
(
asset_acct
,securities_code
     
)
SELECT 
asset_acct
,securities_code   
FROM int_d_example_acct_cap_change_ND
WHERE    sys_src='exam' AND (
COALESCE(asset_acct,'')
,COALESCE( securities_code,'')
 )  NOT IN (
   SELECT 
COALESCE(asset_acct,'')
,COALESCE( securities_code,'')
FROM int_d_example_acct_cap_change_OD
  WHERE   sys_src='exam'  )
;


  
找出在9号切片,OD(old)里,但是不在10号ods表中,ND(new)里的数据,找出有变动或者减少的数据
INSERT INTO int_d_example_acct_cap_change_W_U
(
asset_acct
,securities_code 
)
SELECT 
asset_acct
,securities_code
FROM int_d_example_acct_cap_change_OD
WHERE  sys_src='exam' AND (
COALESCE(asset_acct,'')
,COALESCE( securities_code,'')
 )  NOT IN (
   SELECT 
COALESCE(asset_acct,'')
,COALESCE( securities_code,'')
FROM int_d_example_acct_cap_change_ND
WHERE   sys_src='exam'
    )
;

把变动的数据闭链,减少的数据也闭链
UPDATE :example_DATA.int_d_example_acct_cap_change
SET End_Dt = TO_DATE(:'20230710','YYYYMMDD')
WHERE (
COALESCE(asset_acct,'')
,COALESCE( securities_code,'')
) IN (
SELECT 
COALESCE(asset_acct,'')
,COALESCE( securities_code,'')
FROM int_d_example_acct_cap_change_W_U
WHERE  sys_src='exam')
AND Start_Dt <= TO_DATE(:'20230710','YYYYMMDD')
AND End_Dt > TO_DATE(:'20230710','YYYYMMDD')
AND  sys_src='exam'
;


插入新增的数据或者有变动的数据(闭链日期已提前置为30001231)

INSERT INTO :example_DATA.int_d_example_acct_cap_change
(asset_acct
,securities_code
)
SELECT 
asset_acct
,securities_code
    FROM int_d_example_acct_cap_change_W_I
	WHERE  sys_src='exam'
;

/**************************退出主程序****************************/




相关阅读

热门文章

    手机版|MSIPO技术圈 皖ICP备19022944号-2

    Copyright © 2024, msipo.com

    返回顶部