Insertion de données manquantes

Version imprimable

07/03/2013, 16h15
JGLord

Insertion de données manquantes
Bonjour à vous tous,

Encore une fois, j'aurais besoin de vos lumières pour une requête SQL.

Mon objectif est d'insérer les données manquantes dans une table, et ce, en fonction des dernières valeurs disponibles (valeurs précédentes).

À titre d'exemple, j'ai une table "DATA" qui contient une liste de données (colonnes "Pb" et "Code") qui sont fonction d'un numéro de semaine (NoSem). Malheureusement, cette liste est incomplète et je dois combler les "trous". Pour la colonne "NoSem", je dois inscrire le numéro de semaine manquant. Pour les colonnes "Pb" et "Code", je dois aller chercher les valeurs associées au numéro de semaine précédente (s'il n'y a pas de semaine précédente, alors il faut aller chercher la dernière disponible).

Aux fins de simplifications, on peut considérer qu'il n'y que 6 semaines possible (1 à 6). Donc, si ma table de données est la suivante:
Code:

1 2 3 4 5 6 with DATA as( select 2 as NoSem, 327 as Pb, 'bbb' as Code from dual union all select 3, 645, 'ccc' from dual union all select 5,349, 'eee' from dual )
J'aimerais avoir requête me permettant d'insérer dans ma table DATA les données suivantes:

NoSem, Pb, Code
1, 349, 'eee'
4, 645, 'ccc'
6, 349, 'eee'

Dans cet exemple, on doit ajouter les semaines 1, 4 et 6, car elles sont manquantes dans ma table DATA.
- Pour la semaine 1, on inscrit les valeurs des colonnes "Pb" et "Code" de la semaine 5, car il n'y a aucun NoSem précédant la semaine 1. Il faut savoir que les semaines bouclent (c'est la semaine 6 qui précède la semaine 1). Or, comme le NoSem=6 n'existe pas dans ma table DATA, il faut prendre les valeurs de la semaine précédente à cette 6e semaine manquante, ce qui correspond à la semaine NoSem=5. J'espère être assez clair...
- Pour la semaine 4, on inscrit les valeurs des colonnes "Pb" et "Code" de la semaine 3, car la semaine 3 correspond à la semaine précédente la semaine 4 manquante de ma table DATA.
- Pour la semaine 6, on inscrit les valeurs des colonnes "Pb" et "Code" de la semaine 5, car la semaine 5 correspond à la semaine précédente la semaine 6 manquante de ma table DATA.
Merci beaucoup!

Un premier jet pas très inspiré :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
WITH MaTable AS
(
SELECT 2 AS NoSem, 327 AS Pb, 'bbb' AS Code FROM dual union ALL
SELECT 3         , 645      , 'ccc'         FROM dual union ALL
SELECT 5         , 349      , 'eee'         FROM dual
)
  ,  Cpt AS
(
    SELECT LEVEL AS lvl
      FROM dual
CONNECT BY LEVEL <= 6
)
  , SR AS
(
    SELECT cp.lvl   AS NoSem
         , mt.NoSem AS NoSemOrigine 
         , coalesce( mt.Pb
                   , Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl   , 6))
                   , Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl +1, 6))
                   , Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl +2, 6))
                   , Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl +3, 6))
                   , Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl +4, 6))
                   , Lag(mt.Pb ignore nulls) over(ORDER BY Mod(cp.Lvl +5, 6))
                   ) AS Pb
         , coalesce( mt.Code
                   , Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl   , 6))
                   , Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl +1, 6))
                   , Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl +2, 6))
                   , Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl +3, 6))
                   , Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl +4, 6))
                   , Lag(mt.Code ignore nulls) over(ORDER BY Mod(cp.Lvl +5, 6))
                   ) AS Code
      FROM MaTable mt
RIGHT JOIN Cpt     cp
        ON mt.NoSem = cp.lvl
)
  SELECT NoSem, Pb, Code
    FROM SR
   WHERE NoSemOrigine IS null
ORDER BY NoSem asc;
 
NOSEM  PB CODE
----- --- ----
    1 349 eee 
    4 645 ccc 
    6 349 eee

@Waldar:

Wow, tu viens de me faire découvrir plein de nouvelles fonctions (CONNECT BY, coalesce et lag)!

Par contre, avec quelques tests, je viens de voir que la requête fonctionne très bien sauf un petit détail, que j'essaie de modifier sans trop de succès. En fait, pour les colonnes "Pb" et "Code", il ne faut pas aller chercher les valeurs non nulles précédentes, mais bien les valeurs associées à la NoSem non nulles précédentes.

Bref, si dans MaTable j'avais eu des NULLs dans les colonnes "Pb" et "Code", alors il aurait fallu retourner ces valeurs nulles. Théoriquement, je crois que ça devrait simplifier les choses.

Bref, si MaTable est défini comme ceci:
Code:

1 2 3 4 5 6 WITH MaTable AS ( SELECT 2 AS NoSem, 327 AS Pb, 'bbb' AS Code FROM dual union ALL SELECT 3 , null , 'ccc' FROM dual union ALL SELECT 5 , 349 , null FROM dual )
Alors, il je m'attends au résultat suivant:

NoSem, Pb, Code
1, 349, null
4, null, 'ccc'
6, 349, null

Merci encore!

Pour l'instant, voici mon code. Tel que mentionné précédemment, je ne suis pas capable de retourner les colonnes "Pb" et "Code" en fonction de la valeur de "DataFromId" (ça me donne une erreur de fenêtrage interdit ORA-30483)... Ici, j'ai mis une clause "where NoId=2", simplement pour mes tests. En fait, j'ai mon erreur lorsque je remplace le "2" par ce qui est retourné par la fonction "coalesce".
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 with MaTable as( select 2 as NoId, 327 as Pb, 'bbb' as Code FROM dual union ALL select 3 , null , 'ccc' FROM dual union ALL select 5 , 349 , null FROM dual ), SEQ as( select LEVEL AS lvl FROM dual CONNECT BY LEVEL <= 6 ) select SEQ.lvl ,MaTable.NoId ,coalesce(MaTable.NoId ,Lag(MaTable.NoId IGNORE nulls) over(ORDER BY SEQ.lvl) ,(select max(MaTable.NoId) from MaTable) ) as DataFromId ,(select Pb from MaTable where NoId=2) as PbTest from SEQ left join MaTable on SEQ.lvl=MaTable.NoId --where MaTable.NoId is null order by 1,2 ;

J'y suis presque!

Le seul petit truc qu'il me reste à faire c'est d'afficher seulement les NoId "manquants" de MaTable. Je croyais que je pouvais le faire avec la clause "where" qui est en commentaire, mais lorsque je l'utilise, je n'arrive plus aux bonnes données...

Voici ma requête qui fait ce que je veux à un détail près: J'affiche toutes les rows et non pas seulement celles manquantes:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
with MaTable as(
select 2 as NoId, 327 as Pb, 'bbb' as Code FROM dual union ALL
select 3        , null     , 'ccc'         FROM dual union ALL
select 5        , 349      , null          FROM dual
),
 
  SEQ as(
select LEVEL AS lvl FROM dual CONNECT BY LEVEL <= 6
),
 
  DataFromId as (
select SEQ.lvl
      ,MaTable.NoId
      ,coalesce(MaTable.NoId
                ,Lag(MaTable.NoId IGNORE nulls) over(ORDER BY SEQ.lvl)
                ,(select max(MaTable.NoId) from MaTable)
                ) as FromID
from SEQ
  left join MaTable on SEQ.lvl=MaTable.NoId
--where MaTable.NoId is null
order by 1,2
)
 
select DataFromId.lvl, DataFromId.FromID, MaTable.Pb, MaTable.Code
from MaTable
  left join DataFromId on MaTable.NoId=DataFromId.FromID
;

Hé bien voilà. Ça fait exactement ce que je voulais. Un gros merci à toi Waldar! :ccool:

Par contre, je reste ouvert à toute optimisation!

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
with MaTable as(
select 2 as NoId, 327 as Pb, 'bbb' as Code FROM dual union ALL
select 3        , null     , 'ccc'         FROM dual union ALL
select 5        , 349      , null          FROM dual
),
 
  SEQ as(
select LEVEL AS lvl FROM dual CONNECT BY LEVEL <= 6
),
 
  DataFromId as (
select SEQ.lvl
      ,MaTable.NoId
      ,coalesce(MaTable.NoId
                ,Lag(MaTable.NoId IGNORE nulls) over(ORDER BY SEQ.lvl)
                ,(select max(MaTable.NoId) from MaTable)
                ) as FromID
from SEQ
  left join MaTable on SEQ.lvl=MaTable.NoId
order by 1,2
)
select DataFromId.lvl, DataFromId.FromID, MaTable.Pb, MaTable.Code
from MaTable
  left join DataFromId on MaTable.NoId=DataFromId.FromID
where DataFromId.NoId is null
;
 
 
LVL                    FROMID                 PB                     CODE 
---------------------- ---------------------- ---------------------- ---- 
1                      5                      349                         
4                      3                                             ccc  
6                      5                      349                         
 
3 rows selected