Problème de lenteur lors de l'exécution d'une requête en pl/sql

Version imprimable

1 pièce(s) jointe(s)

Bonjour,

J'ai un problème pour la création d'une requête en PL/SQL (Oracle).

Voici le problème,
Je dois afficher des données mensuelles pour les diffèrents produits que nous avons (nombre totals de produit par mois, la moyenne du nombre de produit journalier, le pic du nombre de produit dans le mois et la date de ce pic) comme suit :

Dans un mois nous pouvons avoir plusieurs date pour un même pic.
[IMG]C:\tmp\tabDonnesPrdt.bmp[/IMG]

Pour cela je voudrai faire la requête suivante :
Code:

1 2 3 4 5 6 7 8 9 select to_char(eventdate, 'yyyy/mm') as MOIS, Nom_Produit as Nom, SUM(Nb_Produits) as Nb_de_produits_Total, AVG(Nb_Produits) as Moyenne_nb_de_produits_journalier, MAX(Nb_Produits) as Pic_Nb_Produit, date as Date_Du_Pic from historic_1656379283 group by to_char(eventdate, 'yyyy/mm'),Nom_Produit order by to_char(eventdate, 'yyyy/mm') desc;
Le problème est au niveau de l'affichage de la Date du pic, je ne peux pas le mettre dans le regroupement(group by) car sinon je n'ai plus les moyennes et les sommes par mois mais par jour.
Cette requête envoie donc une erreur si je ne mets pas date dans le group by.

Pour contourner le problème j'ai créé deux curseurs une qui me donne tout les résultats sauf la date du pic et l'autre qui récupère la date du pic en fonction du mois, de nom du produit et du pic récupérés par les résultats du premier curseur.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Curseur statProduit is select to_char(eventdate, 'yyyy/mm') as MOIS, Nom_Produit as Nom, SUM(Nb_Produits) as Nb_de_produits_Total, AVG(Nb_Produits) as Moyenne_nb_de_produits_journalier, MAX(Nb_Produits) as Pic_Nb_Produit from historic_1656379283 group by to_char(eventdate, 'yyyy/mm'),Nom_Produit order by to_char(eventdate, 'yyyy/mm') desc; Curseur DatePic(mois varchar, nomPdt varchar, picduMois int) is select date as Date_Du_Pic from historic_1656379283 Where Nom_Produit = nomPdt and to_char(eventdate, 'yyyy/mm')=mois and Nb_Produits=picduMois group by to_char(eventdate, 'yyyy/mm'),Nom_Produit order by to_char(eventdate, 'yyyy/mm') desc;
Ce qui me donne le code suivant dans le begin :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 begin OPEN statProduit; LOOP FETCH statProduit INTO ChaquestatProduit; Exit when statProduit%NOTFOUND ; OPEN DatePic(ChaquestatProduit.MOIS,ChaquestatProduit.Nom,ChaquestatProduit.Pic_Nb_Produit); LOOP FETCH DatePic INTO ChaqueDatePic; Exit when DatePic%NOTFOUND ; REQ:='insert into TAB_STATSCRE_MEMensuel(MOIS,NOMPDT,MOYENNEPDT,PICPDT,DATE_DU_PIC) values (' || '''' || ChaquestatProduit.MOIS || '''' || ',' || '''' || ChaquestatProduit.Nom || '''' || ',' || '''' || ChaquestatProduit.Nb_de_produits_Total || '''' || ',' || '''' || ChaquestatProduit.Pic_Nb_Produit || '''' || ',' || '''' || ChaqueDatePic.Date_Du_Pic || '''' || ')'; dbms_output.put_line('insert into TAB_STATSCRE_MEMensuel(MOIS,NOMPDT,MOYENNEPDT,PICPDT,DATE_DU_PIC) values (' || '''' || ChaquestatProduit.MOIS || '''' || ',' || '''' || ChaquestatProduit.Nom || '''' || ',' || '''' || ChaquestatProduit.Nb_de_produits_Total || '''' || ',' || '''' || ChaquestatProduit.Pic_Nb_Produit || '''' || ',' || '''' || ChaqueDatePic.Date_Du_Pic || '''' || ')'); EXECUTE IMMEDIATE REQ; END LOOP; CLOSE DatePic; END LOOP; CLOSE statProduit;
Le problème de cette méthode et qu'elle prend enormément de temps à s'exécuter lors de la recherche de la date pour chaque ligne (pour 400 lignes de résultat = env 1h)

Y'a t'il un moyen de récupérer la date avec la première requête sans mettre la date dans le group by?
Ou bien avez vous une idée pour réduire le temps de recherche de la date?

Merci d'avance.

28/09/2011, 13h15
ojo77

=> http://lalystar.developpez.com/fonctionsAnalytiques/
28/09/2011, 13h32
Waldar

Que voulez-vous obtenir quand le pic se produit à plusieurs dates distinctes ?

Bonjour ,

en essayant de reprendre le besoin et la requette initiale :

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
 
 
WITH historic_1656379283 AS
(
SELECT to_date('01/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('01/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('02/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,11 AS Nb_Produits,to_date('02/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('03/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,35 AS Nb_Produits,to_date('03/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('04/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,9  AS Nb_Produits,to_date('04/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('05/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,0  AS Nb_Produits,to_date('05/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('06/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,58 AS Nb_Produits,to_date('06/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('07/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,16 AS Nb_Produits,to_date('07/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('08/01/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('08/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('01/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,11 AS Nb_Produits,to_date('01/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('02/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,12 AS Nb_Produits,to_date('02/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('03/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,13 AS Nb_Produits,to_date('03/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('04/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,14 AS Nb_Produits,to_date('04/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('05/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,15 AS Nb_Produits,to_date('05/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('06/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,16 AS Nb_Produits,to_date('06/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('07/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,17 AS Nb_Produits,to_date('07/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('08/01/2011','DD/MM/YYYY') AS eventdate,'yoyo'  AS Nom_Produit,18 AS Nb_Produits,to_date('08/01/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('01/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('01/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('02/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,11 AS Nb_Produits,to_date('02/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('03/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,35 AS Nb_Produits,to_date('03/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('04/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,9  AS Nb_Produits,to_date('04/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('05/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,0  AS Nb_Produits,to_date('05/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('06/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,58 AS Nb_Produits,to_date('06/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('07/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,16 AS Nb_Produits,to_date('07/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL UNION ALL
SELECT to_date('08/02/2011','DD/MM/YYYY') AS eventdate,'jouet' AS Nom_Produit,10 AS Nb_Produits,to_date('08/02/2011','DD/MM/YYYY')AS date_vente FROM DUAL 
)
select DISTINCT
to_char(eventdate, 'yyyy/mm') as MOIS,
Nom_Produit as Nom,
SUM(Nb_Produits) OVER (partition by to_char(eventdate, 'yyyy/mm'),Nom_Produit) as Nb_de_produits_Total,
AVG(Nb_Produits) OVER (partition by to_char(eventdate, 'yyyy/mm'),Nom_Produit) as Moyenne_nb,
MAX(Nb_Produits) OVER (partition by to_char(eventdate, 'yyyy/mm'),Nom_Produit) as Pic_Nb_Produit,
MAX(date_vente)  KEEP (DENSE_RANK FIRST ORDER BY Nom_Produit,Nb_Produits DESC) OVER (partition by to_char(eventdate, 'yyyy/mm'),Nom_Produit) as Date_Du_Pic
from historic_1656379283 t1
order by to_char(eventdate, 'yyyy/mm') desc;

28/09/2011, 14h05
yoni75

Citation:

Envoyé par Waldar

Que voulez-vous obtenir quand le pic se produit à plusieurs dates distinctes ?

Bonjour,

Quand le pic se produit à plusieurs dates distinctes je veux les afficher aussi ex :
Mois;Nom Nb_de_prouits_Total;Moyenne_nb_de_produits_journalier;Pic_Nb_Produit;Date_Du_Picnalier
2011/08 tata 3600 196.54 354 12/08/2011
2011/08 tata 3600 196.54 354 18/08/2011
28/09/2011, 14h16
yoni75

Citation:

Envoyé par ORA-007

en essayant de reprendre le besoin et la requête initiale :

Bonjour,

Merci je vais tester.

Cordialement.

Dans ce cas-là, on peut résoudre le problème avec une jointure.
Je reprends les données d'ORA-007 que je modifie pour introduire plusieurs dates avec le même pic :

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
47
48
49
50
51
52
53
54
55
56
57
58
59
WITH historic_1656379283 as
(
select date '2011-01-01' as eventdate, 'jouet' as nom_produit, 10 as nb_produits from dual union all
select date '2011-01-02'             , 'jouet'               , 11                from dual union all
select date '2011-01-03'             , 'jouet'               , 35                from dual union all
select date '2011-01-04'             , 'jouet'               ,  9                from dual union all
select date '2011-01-05'             , 'jouet'               ,  0                from dual union all
select date '2011-01-06'             , 'jouet'               , 58                from dual union all
select date '2011-01-07'             , 'jouet'               , 58                from dual union all
select date '2011-01-08'             , 'jouet'               , 10                from dual union all
select date '2011-01-01'             , 'yoyo'                , 11                from dual union all
select date '2011-01-02'             , 'yoyo'                , 12                from dual union all
select date '2011-01-03'             , 'yoyo'                , 13                from dual union all
select date '2011-01-04'             , 'yoyo'                , 14                from dual union all
select date '2011-01-05'             , 'yoyo'                , 15                from dual union all
select date '2011-01-06'             , 'yoyo'                , 16                from dual union all
select date '2011-01-07'             , 'yoyo'                , 17                from dual union all
select date '2011-01-08'             , 'yoyo'                , 18                from dual union all
select date '2011-02-01'             , 'jouet'               , 10                from dual union all
select date '2011-02-02'             , 'jouet'               , 11                from dual union all
select date '2011-02-03'             , 'jouet'               , 35                from dual union all
select date '2011-02-04'             , 'jouet'               ,  9                from dual union all
select date '2011-02-05'             , 'jouet'               ,  0                from dual union all
select date '2011-02-06'             , 'jouet'               , 58                from dual union all
select date '2011-02-07'             , 'jouet'               , 16                from dual union all
select date '2011-02-08'             , 'jouet'               , 10                from dual 
)
  ,  sr as
(
  select to_char(eventdate, 'yyyy/mm') as mois
       , nom_produit
       , sum(nb_produits) as nb_de_produits_total 
       , avg(nb_produits) as moyenne_nb_produits 
       , max(nb_produits) as pic_nb_produit
    from historic_1656379283
group by to_char(eventdate, 'yyyy/mm')
       , nom_produit
)
  select sr.mois
       , sr.nom_produit
       , sr.nb_de_produits_total
       , sr.moyenne_nb_produits
       , sr.pic_nb_produit
       , tb.eventdate
    from SR
         inner join historic_1656379283 tb
           on tb.nom_produit                   = sr.nom_produit
          and to_char(tb.eventdate, 'yyyy/mm') = sr.mois
   where tb.nb_produits = sr.pic_nb_produit
order by sr.mois desc
       , sr.nom_produit asc;
 
 
MOIS    NOM_P NB_DE_PRODUITS_TOTAL MOYENNE_NB_PRODUITS PIC_NB_PRODUIT EVENTDATE 
------- ----- -------------------- ------------------- -------------- ----------
2011/02 jouet                  149              18.625             58 06/02/2011
2011/01 jouet                  191              23.875             58 06/01/2011
2011/01 jouet                  191              23.875             58 07/01/2011
2011/01 yoyo                   116                14.5             18 08/01/2011

C'est d'ailleurs ce que vous faites avec vos curseurs !

Alors plutôt requête analytique de type TopN

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
47
48
49
50
51
52
53
54
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> WITH historic_1656379283 AS
  2  (
  3    SELECT date '2011-01-01' AS eventdate, 'jouet' AS nom_produit, 10 AS nb_produits FROM dual union ALL
  4    SELECT date '2011-01-02'             , 'jouet'               , 11                FROM dual union ALL
  5    SELECT date '2011-01-03'             , 'jouet'               , 35                FROM dual union ALL
  6    SELECT date '2011-01-04'             , 'jouet'               ,  9                FROM dual union ALL
  7    SELECT date '2011-01-05'             , 'jouet'               ,  0                FROM dual union ALL
  8    SELECT date '2011-01-06'             , 'jouet'               , 58                FROM dual union ALL
  9    SELECT date '2011-01-07'             , 'jouet'               , 58                FROM dual union ALL
 10    SELECT date '2011-01-08'             , 'jouet'               , 10                FROM dual union ALL
 11    SELECT date '2011-01-01'             , 'yoyo'                , 11                FROM dual union ALL
 12    SELECT date '2011-01-02'             , 'yoyo'                , 12                FROM dual union ALL
 13    SELECT date '2011-01-03'             , 'yoyo'                , 13                FROM dual union ALL
 14    SELECT date '2011-01-04'             , 'yoyo'                , 14                FROM dual union ALL
 15    SELECT date '2011-01-05'             , 'yoyo'                , 15                FROM dual union ALL
 16    SELECT date '2011-01-06'             , 'yoyo'                , 16                FROM dual union ALL
 17    SELECT date '2011-01-07'             , 'yoyo'                , 17                FROM dual union ALL
 18    SELECT date '2011-01-08'             , 'yoyo'                , 18                FROM dual union ALL
 19    SELECT date '2011-02-01'             , 'jouet'               , 10                FROM dual union ALL
 20    SELECT date '2011-02-02'             , 'jouet'               , 11                FROM dual union ALL
 21    SELECT date '2011-02-03'             , 'jouet'               , 35                FROM dual union ALL
 22    SELECT date '2011-02-04'             , 'jouet'               ,  9                FROM dual union ALL
 23    SELECT date '2011-02-05'             , 'jouet'               ,  0                FROM dual union ALL
 24    SELECT date '2011-02-06'             , 'jouet'               , 58                FROM dual union ALL
 25    SELECT date '2011-02-07'             , 'jouet'               , 16                FROM dual union ALL
 26    SELECT date '2011-02-08'             , 'jouet'               , 10                FROM dual
 27  ), topN As (
 28    Select to_char(eventdate, 'yyyy/mm') As Mois,
 29           eventdate, nom_produit, nb_produits,
 30           sum(nb_produits) over (partition by to_char(eventdate, 'yyyy/mm') , nom_produit) As nb_de_produits_total,
 31           avg(nb_produits) over (partition by to_char(eventdate, 'yyyy/mm') , nom_produit) As moyenne_nb_produits,
 32           max(nb_produits) over (partition by to_char(eventdate, 'yyyy/mm') , nom_produit) As pic_nb_produit,
 33           dense_rank()     over (partition by to_char(eventdate, 'yyyy/mm') , nom_produit order by nb_produits desc) rn
 34      From historic_1656379283
 35  )
 36  Select Mois, Nom_Produit, nb_de_produits_total, moyenne_nb_produits,  pic_nb_produit, eventdate
 37    From TopN
 38   Where rn = 1
 39  Order By Mois Desc, nom_produit  Asc
 40  /
 
MOIS    NOM_PRODUIT NB_DE_PRODUITS_TOTAL MOYENNE_NB_PRODUITS PIC_NB_PRODUIT EVENTDATE
------- ----------- -------------------- ------------------- -------------- -----------
2011/02 jouet                        149              18,625             58 06/02/2011
2011/01 jouet                        191              23,875             58 06/01/2011
2011/01 jouet                        191              23,875             58 07/01/2011
2011/01 yoyo                         116                14,5             18 08/01/2011
 
SQL>

28/09/2011, 16h18
yoni75

Bonjour,

Je vous remercie beaucoup pour votre aide je suis passé de plus d'une heure à moins de 15 secondes!!!!

Encore Merci

Cordialement.