Sous-requête utilisée plusieurs fois : Comment éviter ?

[StringBuilder]

Bonjour à tous,

J'ai une requête de ce type qui me sert de curseur :

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
 
SELECT produit, jour, valeur
FROM
(
  SELECT produit, jour, sum(ventes) valeur
  FROM tabledesventes
  WHERE jour BETWEEN sysdate - 28 AND sysdate
  GROUP BY produit, jour
) t1
WHERE valeur < (
  SELECT avg(valeur)
  FROM (
    SELECT sum(ventes) valeur
    FROM tabledesventes
    WHERE produit = t1.produit
    AND jour BETWEEN sysdate - 28 AND sysdate
  )
) * 5
OR 1 > (
  SELECT avg(valeur)
  FROM (
    SELECT sum(ventes) valeur
    FROM tabledesventes
    WHERE produit = t1.produit
    AND jour BETWEEN sysdate - 28 AND sysdate
  )
)

On voit clairement que j'ai deux fois exactement la même sous-requête sur tabledesventes (dans la clause where).

Et une troisième fois similaire dans la clause from.

D'un point de vue performances, Oracle a l'air de retrouver ses petits.

Mais d'un point de vue lisibilité (car vous vous en doutez, j'ai fais la version courte...) c'est pas terrible.

J'ai été tenté de créer une vue :

Code :

1
2
3
4
5
 
SELECT produit, jour, sum(ventes) valeur
FROM tabledesventes
WHERE jour BETWEEN sysdate - 28 AND sysdate
GROUP BY produit, jour

Mais je préfère éviter, dans la mesure où je voudrais éviter d'avoir 3 ou 4 vues par procédure stockées... (et dans ce cas, pourquoi ne pas faire aussi une vue pour la requête entière).

Je me demande alors quelles autres solutions s'offrent à mois...

Je suis tenté par le "with", mais est-ce que ça marche pour un curseur ?

Je pense à un truc du genre :

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
 
cursor c IS
WITH ventes_28_jours AS (
  SELECT produit, jour, sum(ventes) valeur
  FROM tabledesventes
  WHERE jour BETWEEN sysdate - 28 AND sysdate
  GROUP BY produit, jour
),
moyene_28_jour AS (
  SELECT produit, avg(valeur) valeur
  FROM ventes_28_jours
  GROUP BY produit
)
SELECT v1.produit, v1.jour, v1.valeur
FROM ventes_28_jours v1
WHERE v1.valeur < (
  SELECT v2.valeur
  FROM moyenne_28_jours v2
  WHERE v2.produit = v1.produit
) * 5
OR 1 > (
  SELECT v2.valeur
  FROM moyenne_28_jours v2
  WHERE v2.produit = v1.produit
)

Est-ce qu'il existe d'autres astuces du genre qui permettent de réduire le code (et améliorer les perfs) ?

[mnitu]

Le with marche avec curseur, avez vous testé ?
Avg sous sa forme analytique devrait permettre de simplifier la requête.

[StringBuilder]

J'avais testé, mais avec des erreurs

Je viens de réussir à compiler effectivement, et cette fois ça marche.

Par contre, l'algo est finalement bien plus complexe (bouh snif)

Ca marche, mais par contre est d'une lenteur abominable.

Je me souviens que les fonction analytiques pouvaient servir dans certains cas, mais là, je sèche... Pourtant, je suis certain que dans un cas précis, ça marche... si vous pouviez me rafraîchir la mémoire

Il s'agit de retrouver un champ qui se trouve sur la même ligne que celle qui contient le max d'un champ...

En gros, simplifier ce genre de code :

Code :

1
2
3
SELECT min(madate)
FROM matable
WHERE mavaleur = (SELECT max(mavaleur) FROM matable)

[StringBuilder]

En fait, le but du jeu, c'est d'éviter que ce truc mette 15 ans à tourner, et c'est mal parti :/
=> 1 minute pour... ne rien faire (j'ai aucune donnée dans MSK qui rentre dans le critère)

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
CREATE OR REPLACE PROCEDURE SOC1."MOY_VENDUE_LISSEE" AS
begin
declare
 
cursor c IS
WITH
  vente_28_jours AS (
    SELECT msk.codsoc, msk.sigdep, msk.codpro, msk.datmvt, sum(msk.qtemvt) somme
    FROM msk
    INNER JOIN pro ON pro.codsoc = msk.codsoc AND pro.codpro = msk.codpro AND pro.codblocage IN('VC','VN')
    WHERE msk.codsoc = 1 AND msk.codosk = 'VMAG'
    AND msk.datmvt BETWEEN to_char((sysdate - 28),'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD')
    GROUP BY msk.codsoc, msk.sigdep, msk.codpro, msk.codosk, msk.datmvt
  ),
  pic AS (
    SELECT v.codsoc, v.sigdep, v.codpro, max(v.somme) valmax, avg(v.somme) valmoy
    FROM vente_28_jours v
    GROUP BY v.codsoc, v.sigdep, v.codpro
  ),
  datmax AS (
    SELECT v.codsoc, v.sigdep, v.codpro, min(v.datmvt) datmin
    FROM vente_28_jours v
    INNER JOIN pic p ON p.codsoc = v.codsoc AND p.sigdep = v.sigdep AND p.codpro = v.codpro AND p.valmax = v.somme AND p.valmax >= p.valmoy * 5 OR p.valmoy <= 1
    GROUP BY v.codsoc, v.sigdep, v.codpro 
  )
SELECT v.codsoc, v.sigdep, v.codpro, sum(v.somme) / decode(d.datmin, NULL, 28, 27) somme
FROM vente_28_jours v
LEFT OUTER JOIN datmax d ON d.codsoc = v.codsoc AND d.sigdep = v.sigdep AND d.codpro = v.codpro
WHERE v.datmvt != d.datmin
GROUP BY v.codsoc, v.sigdep, v.codpro, d.datmin
ORDER BY v.codsoc, v.sigdep, v.codpro;
 
cc c%rowtype;
cpt number(10);
 
 
 
begin
 
  UPDATE dsk
  SET c15=0
  WHERE codsoc=1;
 
commit;
 
cpt:=0;
open c;
  loop
  fetch c INTO cc;
  exit when c%notfound;
 
  cpt:=cpt+1;
 
  --commit;
 
  UPDATE dsk
  SET c15=cc.somme
  WHERE codsoc=cc.codsoc
  AND sigdep=cc.sigdep
  AND codpro=cc.codpro;
 
  IF cpt=1000 then 
  commit;
  cpt:=0;
  end IF;
 
  end loop;
 
  close c;
commit;
 
end;
end;

(oui oui, je sais, quand j'en fait, c'est de la bonne qui colle aux pieds...)

[McM]

Le OR me parait bizarre sans les (), j'ai toujours du mal à connaitre la priorité

Code :

1
2
3
INNER JOIN pic p ON p.codsoc = v.codsoc AND p.sigdep = v.sigdep 
AND p.codpro = v.codpro AND p.valmax = v.somme 
AND p.valmax >= p.valmoy * 5 OR p.valmoy <= 1

Après un test, il y a bien un souci sur cette jointure. Les AND sont prioritaires sur le OR.. donc

Code :

1
2
3
INNER JOIN pic p ON p.codsoc = v.codsoc AND p.sigdep = v.sigdep
AND p.codpro = v.codpro AND p.valmax = v.somme 
AND (p.valmax >= p.valmoy * 5 OR p.valmoy <= 1)

[StringBuilder]

A la fin de la journée, j'avais effectivement remarqué qu'il y avait une différence de résultat entre la requête originale et la finale.

Je devais regarder cet après-midi d'où ça venait (super de bosser sur plusieurs projets en même temps...)

Merci d'avoir trouvé ce bug, effectivement c'est un oubli