[ Merge 拉链表] merge实现数据仓库历史拉链表
create table dm_channel_acct( --渠道账户开户表 cust_no varchar(20), --客户号 channel_account varchar(20), --渠道账号 sing_org varchar(10), --开户机构 sing_date varchar(10), --开户日期 logout_date varchar(10), --销户日期 acct_status varchar(1), --账号状态 start_date varchar(10), --拉链开始日期 end_date varchar(10) --拉链结束日期 ); create table ods_data( --渠道账户开户表 数仓增量数据 cust_no varchar(20), --客户号 channel_account varchar(20), --渠道账号 sing_org varchar(10), --开户机构 sing_date varchar(10), --开户日期 logout_date varchar(10), --销户日期 acct_status varchar(1) --账号状态 ); -- 放入原数据 insert into dm_channel_acct (cust_no, channel_account, sing_org, sing_date, logout_date, acct_status, start_date, end_date) values('1001', '201', 'zfb', '5701', '2020-01-05', '', 'Y', '2020-01-05', '9999-12-31') ; insert into dm_channel_acct (cust_no, channel_account, sing_org, sing_date, logout_date, acct_status, start_date, end_date) values('1001', '202', 'jd', '5701', '2020-01-07', '', 'Y', '2020-01-07', '9999-12-31') ; --设置数仓增量数据 insert into ods_data (cust_no, channel_account, sing_org, sing_date, logout_date, acct_status, start_date, end_date) values('1001', '201', 'zfb', '5701', '2020-01-05', '2020-01-15', 'N') ; insert into ods_data (cust_no, channel_account, sing_org, sing_date, logout_date, acct_status, start_date, end_date) values('1001', '202', 'jd', '5701', '2020-01-05', '', 'Y') ; insert into ods_data (cust_no, channel_account, sing_org, sing_date, logout_date, acct_status, start_date, end_date) values('1001', '202', 'cft', '5705', '2020-01-15', '', 'Y') ; insert into ods_data (cust_no, channel_account, sing_org, sing_date, logout_date, acct_status, start_date, end_date) values('1002', '204', 'jd', '5705', '2020-01-15', '', 'Y') ; insert into ods_data (cust_no, channel_account, sing_org, sing_date, logout_date, acct_status, start_date, end_date) values('1002', '201', 'jd', '5705', '2020-01-15', '', 'Y') ; --写出要更新的语句，即dm_channel_acct和ods_data不同的记录 select nvl(dm.cust_no, ods.cust_no) as cust_no ,nvl(dm.channel_account, ods.channel_account) as channel_account ,nvl(dm.channel_type, ods.channel_type) as channel_type ,nvl(dm.sign_org, ods.sign_org) as sign_org ,nvl(dm.sign_date, ods.sign_date) as sign_date ,nvl(dm.logout_date, ods.logout_date) as logout_date ,nvl(dm.acct_status, ods.acct_status) as acct_status ,case when ods.channel_account is null then dm.start_date else '2020-01-17' end as start_date ,case when ods.channel_account is null then '2020-01-17' esle dm.end_date end as end_date --ods表中有该表数据的结束日期 from ods_data ods full join dm_channel_acct dm on ods.channel_account = dm.channel_account and ods.cust_no = dm.cust_no and ods.acct_status = dm.acct_status and ods.channel_type = dm.channel_type where dm.channel_account is null or (ods.channel_account is null and ccim.end_date = '999-12-31') ; --使用merge更新 merge into dm_channel_acct a using( select nvl(dm.cust_no, ods.cust_no) as cust_no ,nvl(dm.channel_account, ods.channel_account) as channel_account ,nvl(dm.channel_type, ods.channel_type) as channel_type ,nvl(dm.sign_org, ods.sign_org) as sign_org ,nvl(dm.sign_date, ods.sign_date) as sign_date ,nvl(dm.logout_date, ods.logout_date) as logout_date ,nvl(dm.acct_status, ods.acct_status) as acct_status ,case when ods.channel_account is null then dm.start_date else '2020-01-17' end as start_date ,case when ods.channel_account is null then '2020-01-17' esle dm.end_date end as end_date --ods表中有该表数据的结束日期 from ods_data ods full join dm_channel_acct dm on ods.channel_account = dm.channel_account and ods.cust_no = dm.cust_no and ods.acct_status = dm.acct_status and ods.channel_type = dm.channel_type where dm.channel_account is null or (ods.channel_account is null and ccim.end_date = '999-12-31') ) b on (a.cust_no = b.cust_no and a.channel_account = b.channel_account and a.channel_type = b.channel_type and a.channel_status = b.channel_status) when matched then update set a.acct_status = b.acct_status ,logout_date = b.logout_date ,a.end_date = '2020-01-17' when not matched then insert (cust_no, channel_account, channel_type, sign_org, sign_date, logout_date, acct_status, start_date, end_date) values (b.cust_no, b.channel_account, b.channel_type, b.sign_org, b.sign_date, b.logout_date, b.acct_status, b.start_date, b.end_date) ;
更多精彩
