1.原始數(shù)據(jù)
原始數(shù)據(jù)
2.目標數(shù)據(jù)
目標數(shù)據(jù)
sql實現(xiàn)
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 -- 根據(jù)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
;
