Retourner chaque enregistrement à son état original en sql

Question

Je veux revenir en arrière sur chaque enregistrement pour trouver tous ses états. La table des étudiants contient uniquement les valeurs mises à jour, mais l'historique des étudiants contient l'ancienne valeur pour les colonnes mises à jour.

create table student (    
    id number(6) primary key,
    name varchar2(50),
    city varchar2(50),
    address varchar2(100),
    createdDateTime date,
    updatedDatetime date
);

insert into student values(1,'abc1','abc1','abc1','09-Jan-20','12-Jan-20');
insert into student values(2,'pqr','pqr','pqr','09-Jan-20',null);

table des étudiants-

ID      Name    City     Address    Create_time UpdatedTime
1       abc1    abc1     abc1       09-Jan-20   12-Jan-20
2       pqr     pqr      pqr        09-Jan-20   null

create table studentHistory (
    id number(6) ,
    name varchar2(50),
    city varchar2(50),
    address varchar2(100),
    DatetimeCreated date
);

insert into StudentHistory values(1,null,'abc',null,'10-Jan-20');
insert into StudentHistory values(1,'abc',null,null,'11-Jan-20');
insert into StudentHistory values(1,null,null,'abc','12-Jan-20');

Tableau des antécédents des élèves-

ID  Name    City        Address DatetimeCreated
1   null    abc          null     10-Jan-20
1   abc     null         null     11-Jan-20
1   null    null         abc      12-Jan-20

La sortie requise sera chaque étape d'un enregistrement unique.

ID  Name    City         Address    DatetimeCreated     LastUpdated
1   abc     abc         abc        09-Jan-20            null
1   abc    abc1         abc        09-Jan-20          10-Jan-20
1   abc1    abc1        abc        09-Jan-20          11-Jan-20
1   abc1    abc1        abc1      09-Jan-20       12-Jan-20
2   pqr     pqr         pqr        09-Jan-20           null

Answer 1

Vous pouvez reconstruire l'histoire en utilisant la logique. Pour une colonne donnée, la valeur est la première non NULL valeur rencontrée dans cet ordre :

1. La valeur dans une ligne particulière.
2. Un précédent non NULL dans une ligne précédente pour l'élève
3. La valeur en student

Cela résout la plupart des problèmes. Vous devez ensuite introduire les données les plus récentes. Comme une requête :

select sh.id,
       coalesce(sh.name,
                lag(sh.name ignore nulls) over (partition by sh.id order by sh.DatetimeCreated),
                s.name
               ) as name,
       coalesce(sh.city,
                lag(sh.city ignore nulls) over (partition by sh.id order by sh.DatetimeCreated),
                s.city
               ) as city,
       coalesce(sh.address,
                lag(sh.address ignore nulls) over (partition by sh.id order by sh.DatetimeCreated),
                s.address
               ) as address,
        s.createdDateTime,
        sh.createdDateTime as updatedDateTime
from studenthistory sh join
     student s
     on s.id = sh.id
union all
select s.id, s.name, s.city, s.address, s.createdDateTime, s.updatedDateTime
from student s;

Answer 2

select sh.id,
       coalesce(sh.name,
                lag(sh.name ignore nulls) over (partition by sh.id order by sh.DatetimeCreated desc),
                s.name
               ) as name,
       coalesce(sh.city,
                lag(sh.city ignore nulls) over (partition by sh.id order by sh.DatetimeCreated desc),
                s.city
               ) as city,
       coalesce(sh.address,
                lag(sh.address ignore nulls) over (partition by sh.id order by sh.DatetimeCreated desc),
                s.address
               ) as address,
        s.createdDateTime,
        lag(sh.DatetimeCreated) over (partition by sh.id order by sh.DatetimeCreated asc ) as updatedDateTime
from studenthistory sh join
     student s
     on s.id = sh.id
union all
select s.id, s.name, s.city, s.address, s.createdDateTime, s.updatedDateTime
from student s;