1.原始數(shù)據(jù)

原始數(shù)據(jù)

2.目標(biāo)數(shù)據(jù)

目標(biāo)數(shù)據(jù)

sql實(shí)現(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排序的下一個(gè)日期
        ,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
;