recherche de fonction analytique [Résolu]

[pat29]

Bonjour,

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

Code :

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 :

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 :

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 :

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 :

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]

Envoyé par mnitu

Code :

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

[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...

[Waldar]

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

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
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