Discussion :

[pat29]

Bonjour,

j'ai une vue qui me rend des données (en schématisant) de la forme suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
 ID    NUM     VALEUR
 A      1       100
 A      2       null
 A      3       120
 A      4       null
 B      1       null
 B      2       200
 B      3       null
 B      4       null

Il faudrait que je crée une 2è vue à partir de celle-ci qui remplacerait pour chaque couple ID/NUM les valeurs nulles par la dernière valeur non nulle dans l'ordre de NUM, soit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
 ID    NUM     VALEUR
 A      1       100
 A      2       100
 A      3       120
 A      4       120
 B      1       null
 B      2       200
 B      3       200
 B      4       200

j'ai pu le faire de la façon suivante (VUE étant la vue précédente)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
select ID, NUM1, NVL(VAL1,VAL2)
from
(
   select v1.ID, v1.NUM NUM1, v2.NUM NUM2, v1.VALEUR VAL1, v2.VALEUR VAL2,
           max(v2.NUM) over (partition by v1.ID) max_num
   from     VUE V1
   left join VUE V2
   on v1.ID = v2.ID
  and V2.NUM <= V1.NUM
  and V2.VALEUR is not null
)
where NUM2=max_num

L'inconvénient est que la jointure ouverte provoque un HASH JOIN très gourmand qui prend 90% du temps estimé par l'EXPLAIN PLAN.
Je n'ai pas trouvé de fonction analytique qui permettrait de ne faire qu'un appel à la 1ère vue....

Merci.

[xdescamp]

La solution suivante doit fonctionner si les valeurs du champ VALEUR sont bien croissantes comme dans l'exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select id, num,
       max (valeur) over (partition by id order by num1 asc) valeur
from vue;

[pat29]

Hélas non. C'est vrai que dans l'exemple elles augmentent mais c'est fortuit.
Il faut que la fonction remonte le max des NUM précédents avec VALEUR non nulle.

[xdescamp]

Est-ce que pour chaque ID, les valeurs de NUM sont bien numérotées de 1 à N, sans trou?

[mnitu]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> With Data As (
  2    Select 'A' As Id, 1 As Num, 100 As val from dual union all
  3    Select 'A', 2, NULL from dual union all
  4    Select 'A', 3, 120 from dual union all
  5    Select 'A', 4, NULL from dual union all
  6    Select 'B', 1, NULL from dual union all
  7    Select 'B', 2, 200 from dual union all
  8    Select 'B', 3, NULL from dual union all
  9    Select 'B', 4, NULL from dual
 10  )
 11  Select id, num, val,
 12         last_value(val ignore nulls) over (partition by id order by num)
 13    From Data
 14  order by id, num
 15  /
 
ID        NUM        VAL LAST_VALUE(VALIGNORENULLS)OVER
-- ---------- ---------- ------------------------------
A           1        100                            100
A           2                                       100
A           3        120                            120
A           4                                       120
B           1            
B           2        200                            200
B           3                                       200
B           4                                       200
 
8 rows selected
 
SQL>

[xdescamp]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
...
 12         last_value(val ignore nulls) over (partition by id order by num)
...

Je ne connaissais pas l'option "IGNORE NULLS". Respect...
Et toujours content d'apprendre qq chose!

[Waldar]

Surtout que MAX ne correspond pas au besoin, en modifiant un peu le jeu de données initial :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
with data as
(
select 'A' as id, 1 as num, 120 as val from dual union all
select 'A'      , 2       , null       from dual union all
select 'A'      , 3       , 100        from dual union all
select 'A'      , 4       , null       from dual union all
select 'B'      , 1       , null       from dual union all
select 'B'      , 2       , 200        from dual union all
select 'B'      , 3       , null       from dual union all
select 'B'      , 4       , null       from dual
)
  select id, num, val,
         max (val)                    over (partition by id order by num asc) as val_max,
         last_value(val IGNORE NULLS) over (partition by id order by num asc) as val_last
    FROM DATA
ORDER BY id asc, num asc;
 
ID        NUM        VAL    VAL_MAX   VAL_LAST
-- ---------- ---------- ---------- ----------
A           1        120        120        120
A           2                   120        120
A           3        100        120        100 -- Problème ici avec max !
A           4                   120        100
B           1                                 
B           2        200        200        200
B           3                   200        200
B           4                   200        200

[pat29]

les valeurs de NUM sont bien numérotées de 1 à N sans trou.

Eh bien merci à mnitu ! Je ne connaissais pas non plus cette option que j'ai cherchée en vain...