Discussion :

[PhunkyBob]

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 :

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

1
2
3
4
 
EVOL_PRIX
date
valeur

La table contient les valeurs suivantes :

Code :

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

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 :

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

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 :

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

[Waldar]

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 :

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

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 :

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

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 :

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

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

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 :

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

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 :

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

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

[mnitu]

J'ai cherché un peu

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

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

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

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

(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 !)

[mnitu]

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

[PhunkyBob]

Je pense que j'ai un gros souci.

Voici ma table :

Code :

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

1
2
3
4
5
6
7
 
CREATE TABLE ZZZ_TMP
(
  TEMPS    DATE,
  PRODUCT  VARCHAR2(32 BYTE),
  VAL    NUMBER
)

Voici les données :

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

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

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 :

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

[PhunkyBob]

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

Code :

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

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;

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

[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