全量数据的拉链表实现

拉链表

--拉链表的实现
insert overwrite table ods_lalian
select  id
	   ,a_score    
	   ,b_score    
	   ,c_score    
	   ,d_score    
	   ,creator    
	   ,create_time
	   ,start_date --生命周期开始时间
	   ,end_date   --生命周期结束时间
	   ,row_number()over(partition by id order by create_time desc ) as order_flag
	   ,load_time
from (
	--1)完全新增数据和完全删除逻辑-----------------------------------------------------------------------------
		select nvl(t1.id,t2.id)                            as id
			  ,nvl(t1.a_score    ,t2.a_score    )                            as a_score       
			  ,nvl(t1.creator    ,t2.creator    )                            as creator    
			  ,nvl(t1.create_time,t2.create_time)                            as create_time
			  ,case when t2.id is null  then t1.create_time  
					when t1.id is null  then t2.create_time  
			   end                                                           as start_date --生命周期开始时间
			  ,case when t2.id is null  then '2099-12-31 00:00:00'
					-- 下面判断是为了满足，当某id只有一条数据且第二天就被删除的情况
					when t1.id is null  and  t2.end_date = '2099-12-31 00:00:00'  then from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') 
					when t1.id is null  and  t2.end_date <> '2099-12-31 00:00:00' then t2.end_date 
			   end                                                          as end_date   --生命周期结束时间
			  ,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')        as load_time
		from ods_data1      as t1   -- t1数据全量表
		full join (select * 
					 from ods_lalian  -- t2拉链表
					where order_flag = 1
				   )as t2
		on t1.id = t2.id
		where t1.id is null  -- t1是手工表的全量数据，t1为null代表该条数据被删除了
		   or t2.id is null  -- t2是拉链表的全量数据，t2为null代表新增数据

	--2)生命周期不变和生命周期结束逻辑---处理拉链表上的旧数据的船舰---------------
	union all 
		select t2.id
			  ,t2.a_score      
			  ,t2.creator    
			  ,t2.create_time
			  ,case when t1.create_time = t2.create_time   then t2.start_date
					when t1.create_time <> t2.create_time  then t2.start_date 
			   end                                                          as start_date --生命周期开始时间
			  ,case when t1.create_time = t2.create_time   then t2.end_date
					when t1.create_time <> t2.create_time  then t1.create_time
			   end                                                          as end_date   --生命周期结束时间
			  ,case when t1.create_time = t2.create_time   then t2.load_time
					when t1.create_time <> t2.create_time  
					then from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')
			   end  as load_time
		from ods_data1     as t1
		inner join (select * 
					 from ods_lalian
					where order_flag = 1
				   )as t2
		on t1.id = t2.id

	--3)生命周期延续------------------------------------------------------------------------------------------
	union all 
		select t2.id
			  ,t1.a_score      
			  ,t1.creator    
			  ,t1.create_time
			  ,t1.create_time                        as start_date --生命周期开始时间
			  ,'2099-12-31 00:00:00'                 as end_date   --生命周期结束时间
			  ,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')        as load_time
		from ods_data1     as t1
		inner join (select * 
					 from ods_lalian
					where order_flag = 1
				   )as t2
		on t1.id = t2.id
	where t1.create_time <> t2.create_time

	--4)历史生命周期数据------------------------------------------------------------------------------------------
	union all 
	select * from ods_lalian
	where order_flag > 1
) as t 
;