Comment "trainer" une valeur ?

Version imprimable

19/01/2009, 18h18
PhunkyBob

Comment "trainer" une valeur ?
Bonjour,

J'ai besoin de "trainer" une valeur dans une table, et je ne sais pas comment m'y prendre.

Supposons que j'ai une table avec l'évolution des prix de mon produit. A chaque fois que le prix change, je rajoute une nouvelle entrée.
Code:

1 2 3 4 EVOL_PRIX date valeur
La table contient les valeurs suivantes :
Code:

1 2 3 4 5 6 1/1/2009;10 3/1/2009;11 5/1/2009;11,5 6/1/2009;11 ...
Je souhaite faire un graphique d'évolution de prix au jour le jour.
En croisant avec un calendrier, j'obtiens la table suivante :
Code:

1 2 3 4 5 6 7 8 9 10 11 1/1/2009;10 2/1/2009;NULL 3/1/2009;11 4/1/2009;NULL 5/1/2009;11,5 6/1/2009;11 7/1/2009;NULL 8/1/2009;NULL 10/1/2009;NULL ...
Si je me base là dessus pour faire mon graphique, j'aurai des valeurs nulles, ce qui n'est pas vraiment vrai.

Je voudrais donc "trainer" mes valeurs pour que quand une valeur est NULL, je prends la dernière valeur connue.

Je voudrais obtenir le résultat suivant :

Citation:

1/1/2009;10
2/1/2009;10
3/1/2009;11
4/1/2009;11
5/1/2009;11,5
6/1/2009;11
7/1/2009;11
8/1/2009;11
10/1/2009;11
...

Existe-t-il méthode qui permet de faire ça avec Oracle ? (SQL ou PL/SQL)

Sinon, comment vous y prendriez-vous ?

Merci d'avance,

PhunkyBob

En SQL simple (enfin, avec fonction analytique, il vous faut au moins 9i enterprise edition), vous pouvez calculer votre colonne de fin de validité :

Code:

1
2
3
4
5
6
7
8
select jour debut, lead(jour) over(order by jour asc) fin, nb
from
(
select to_date('01/01/2009', 'dd/mm/yyyy') jour, 10 nb from dual union all
select to_date('03/01/2009', 'dd/mm/yyyy'), 11 from dual union all
select to_date('05/01/2009', 'dd/mm/yyyy'), 11.5 from dual union all
select to_date('06/01/2009', 'dd/mm/yyyy'), 11 from dual
)

Code:

1
2
3
4
5
DEBUT		FIN		NB
01/01/2009	03/01/2009	10
03/01/2009	05/01/2009	11
05/01/2009	06/01/2009	11.5
06/01/2009	{null}		11

A vous de voir si la date de fin est égale ou inférieure à la date de début suivante, dans mon exemple est elle égale.

A partir de là, vous faite une jointure avec votre table calendrier en utilisant un between (le where dans mon exemple est pour limiter la table calendrier) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select
    c.day,
    T.nb
from
    (
    select jour debut, lead(jour) over(order by jour asc) fin, nb
    from (
    select to_date('01/01/2009', 'dd/mm/yyyy') jour, 10 nb from dual union all
    select to_date('03/01/2009', 'dd/mm/yyyy'), 11 from dual union all
    select to_date('05/01/2009', 'dd/mm/yyyy'), 11.5 from dual union all
    select to_date('06/01/2009', 'dd/mm/yyyy'), 11 from dual
         )
    ) T
    INNER JOIN calendar c ON c.day between T.debut and nvl(T.fin-1, to_date('31/12/9999', 'dd/mm/yyyy'))
where
    c.day between to_date('01/01/2009', 'dd/mm/yyyy') and to_date('10/01/2009', 'dd/mm/yyyy')     
order by
    c.day asc

Code:

1
2
3
4
5
6
7
8
9
10
11
DAY		NB
01/01/2009	10
02/01/2009	10
03/01/2009	11
04/01/2009	11
05/01/2009	11.5
06/01/2009	11
07/01/2009	11
08/01/2009	11
09/01/2009	11
10/01/2009	11

Cette jointure-ci :

Code:

c.day BETWEEN T.debut AND nvl(T.fin-1, to_date('31/12/9999', 'dd/mm/yyyy'))

Peut très bien s'écrire de la forme suivante, qui est peut-être plus facile à visualiser :

Code:

1
2
    c.day >= T.debut
AND (c.day < T.fin OR T.fin is null)

J'ai cherché un peu :aie:

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
 
SQL> r
  1  With data As
  2  (
  3    Select to_date('01/01/2009','DD/MM/YYYY') dat,10 val from dual union all
  4    Select to_date('03/01/2009','DD/MM/YYYY'),11 from dual union all
  5    Select to_date('05/01/2009','DD/MM/YYYY'),11.5 from dual union all
  6    Select to_date('06/01/2009','DD/MM/YYYY'),11 from dual
  7  ), times As
  8  (
  9    Select to_date(lpad(level,2,'0')||'01/2009','DD/MM/YYYY') day from dual connect by level <= 10
 10  )
 11  Select day, LAST_VALUE(val IGNORE NULLS) over(order by day)
 12    From (
 13          Select day, val
 14            From data
 15                 RIGHT OUTER JOIN times ON (times.day = data.dat)
 16          )
 17* order by 1
 
DAY      LAST_VALUE(VALIGNORENULLS)OVER(ORDERBYDAY)
-------- ------------------------------------------
01/01/09                                         10
02/01/09                                         10
03/01/09                                         11
04/01/09                                         11
05/01/09                                       11,5
06/01/09                                         11
07/01/09                                         11
08/01/09                                         11
09/01/09                                         11
10/01/09                                         11

19/01/2009, 23h36
PhunkyBob
Merci pour ces très bonnes pistes !

Je ne connaissais pas ces fonctions...

En réalité, ma table est "un poil" plus compliquée : je n'ai pas que "date / tarifs", mais "date / produit / prix".

Je veux donc trainer les prix uniquement pour un même produit.

Est ce que si je mets
Code:

1 2 3 4 [...] SELECT day, LAST_VALUE(val IGNORE NULLS) OVER(PARTITION BY produit ORDER BY day) [...]
cela fera ce que je souhaite ?
(une sorte de "group by produit", qui fait qu'il prendra la dernière valeur pour un même produit)

Merci beaucoup pour votre aide :king:

(j'ai du rater un cours de SQL il y a 10 ans, parceque je n'ai jamais entendu parler de ça, et pourtant ca me parait super utile !)
20/01/2009, 10h52
mnitu

Regarde aussi "Using Partitioned Outer Joins: Examples" dans la doc d'Oracle 10g.

Je pense que j'ai un gros souci.

Voici ma table :
Code:

1 2 3 4 5 6 7 CREATE TABLE ZZZ_TMP ( TEMPS DATE, PRODUCT VARCHAR2(32 BYTE), VAL NUMBER )
Voici les données :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT * FROM ZZZ_TMP ORDER BY temps, product; 1/1/2009|Carottes|null 1/1/2009|Pommes de terre|10 1/2/2009|Carottes|null 1/2/2009|Pommes de terre|null 1/3/2009|Carottes|50 1/3/2009|Pommes de terre|12 1/4/2009|Carottes|null 1/4/2009|Pommes de terre|null 1/5/2009|Carottes|55 1/5/2009|Pommes de terre|null 1/6/2009|Carottes|null 1/6/2009|Pommes de terre|13 1/7/2009|Carottes|null 1/7/2009|Pommes de terre|null
Pour avoir la dernière valeur connue, il faudrait que j'exécute la requête suivante :
Code:

1 2 3 4 5 SELECT temps, product, val, LAST_VALUE(val IGNORE NULLS) OVER(PARTITION BY product ORDER BY temps RANGE BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) AS lv FROM ZZZ_TMP WHERE product LIKE 'Pom%' ORDER BY temps, product;
Qui devrait me retourner :

Citation:

1/1/2009|Carottes|null|null
1/1/2009|Pommes de terre|10|10
1/2/2009|Carottes|null|null
1/2/2009|Pommes de terre|null|10
1/3/2009|Carottes|50|50
1/3/2009|Pommes de terre|12|12
1/4/2009|Carottes|null|50
1/4/2009|Pommes de terre|null|12
1/5/2009|Carottes|55
1/5/2009|Pommes de terre|null|12
1/6/2009|Carottes|null|55
1/6/2009|Pommes de terre|13|13
1/7/2009|Carottes|null|55
1/7/2009|Pommes de terre|null|13

Mon problème, c'est que "IGNORE NULLS" ne fonctionne pas avec ma version de Oracle (9i) !!!

Je ne vois pas comment faire pour faire comme le "IGNORE NULLS", pour que ça me retourne la dernière valeur non nulle :cry:

J'ai trouvé une facon de faire, mais je suis presque sur que ce n'est pas la facon la plus optimisée :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT t1.temps, t1.product, t1.val, 
                (SELECT val 
                FROM ZZZ_TMP t3 
                WHERE t3.product = t1.product 
                AND t3.temps = (SELECT MAX(t2.temps) 
                                FROM ZZZ_TMP t2 
                                WHERE t1.temps >= t2.temps 
                                    AND t2.val IS NOT NULL 
                                    AND t1.product = t2.product)) AS previous
FROM ZZZ_TMP t1
ORDER BY t1.temps, t1.product;

20/01/2009, 14h28
Waldar

Si j'ai bien suivi votre exemple, vous avez croisé votre calendrier avec vos produits / prix, puis vous chercher à renseigner le prix.

Dans ce que je vous ai proposé je fais l'opposé : à partir des produits / prix je construit une plage de validité de la donnée que je croise ensuite avec le calendrier.
Comme ça, vous n'avez pas de null à gérer.
20/01/2009, 18h26
PhunkyBob

J'avais du mal à comprendre la première requête, qui en fait est juste l'équivalent d'un "SELECT *".

J'ai essayé avec votre façon de faire, et je dois bien avouer que c'est largement plus efficace !

Avec ma méthode comprenant 3 "select" imbriqués, il me fallait plus de 15 minutes pour obtenir mes infos.
Avec la méthode "on joint avec le calendrier en dernier", je mets seulement 10 secondes !

Merci :)