1.原始数据
原始数据
2.目标数据
目标数据
sql实现
update
dwd.dwd_reattribute_device_df a
set
end_date = b.stat_end_date
from(
select
row_number() over(partition by gameid,channelid,uuid order by start_date) as rowid
,lead(start_date,1) over(partition by gameid,channelid,uuid order by start_date) as stat_end_date -- 根据start_date排序的下一个日期
,gameid
,gameversion
,channelid
,uuid
,start_date
from
dwd.dwd_reattribute_device_df
)b
where
a.gameid = b.gameid
and a.channelid = b.channelid
and a.uuid = b.uuid
and a.start_date = b.start_date
and a.end_date <> b.stat_end_date
;
