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
;