Modification / Optimisation requete

Bonjour messieurs dames,

J'ai deux tables

mvt (id_mvt, id_affaire, mt_mvt)
reg (id_reg, id_mvt, id_affaire, mt_reg)

Le but est de récupérer pour une affaire ses montants dans mvt mais aussi les montants dans reg sachant qu'un mouvement peut avoir 0 ou plusieurs reglements

Première idée :
Code:

1 2 3 4 5 select id_affaire, sum(mt_mvt), sum(mt_reg) from mvt left outer reg on mvt.id_mvt = reg.id_mvt and mvt.id_affaire = reg.id_affaire where mvt.id_affaire = :pid group by id_affaire ;
Sauf que là mes montants dans mvt sont multipliés par le nombre d'enregistrements de reg. => pas bien :(

Deuxième idée :
Code:

1 2 3 4 5 6 7 8 9 10 select id_affaire, (select sum(mt_mvt) from mvt where mvt.id_affaire = :pid) mt_mvt, (select sum(mt_reg) from reg where reg.id_affaire = :pid and exists (select 1 from mvt where mvt.id_mvt = reg.id_mvt)) mt_reg from dual ;
Là çà fonctionne mais on remarque qu'on passe deux fois sur la table mvt (c'est uen table à 20 millions d'enreg et reg à 100 millions). => requete un peu lente

Ma question : existe-t-il une méthode pour faire une sorte de "rupture" sur mvt.id_affaire du genre sum(mt_mvt distinct id_affaire)

Merci d'avance.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT id_affaire, SUM(mt_mvt), SUM(mt_reg)
FROM (
	SELECT id_mvt, id_affaire, mt_mvt,
	                    (SELECT SUM(mt_reg) 
	                     FROM REG
	                     WHERE reg.id_affaire = mvt.id_affaire 
			AND reg.id_mvt 	= mvt.id_mvt) mt_reg
 
	FROM MVT
	WHERE mvt.id_affaire = :pid
	)
GROUP BY id_affaire

Merci pour ta proposition, je compare les explain et je vous tiens au courant.

Celà fonctionne, l'explain semble correct mais le temps d'exécution est supérieur pour récupérer toutes mes affaires (11 minutes sans rendre la main) au temps de la deuxième idée (3 minutes) :s

Salut,

J'ai une petite remarque sur la solution de MCM

Code:

1
2
3
4
5
 
(SELECT SUM(mt_reg) 
	                     FROM REG
	                     WHERE reg.id_affaire = mvt.id_affaire 
			AND reg.id_mvt 	= mvt.id_mvt) mt_reg

A la place on met ca

Code:

1
2
3
4
5
6
 
(SELECT SUM(mt_reg) 
	                     FROM REG
	                     WHERE reg.id_affaire = mvt.id_affaire 
			AND reg.id_mvt 	= mvt.id_mvt
            and mvt.id_affaire = :pid) mt_reg

Oui salim11 mais le but est de faire cette requete pour toute les affaires donc la solution de MCM me convient en terme syntaxique :lol:

la fonction analytique SUM me parait bien utile dans ce cas ;)

Bonjour ,

Je n'ai pas bien compris une chose, dans la requette dans la clause where
WHERE mvt.id_affaire = :pid donc tu vas chercher les montants mt_mvt et mt_reg de l'affaire :pid, Alors la mettre dans la clause where et dans la clause de la sous-requette pour moi ca va te donner uniquement les montant d'une affaire bien preçise :lol:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT id_affaire, SUM(mt_mvt), SUM(mt_reg) FROM ( SELECT id_mvt, id_affaire, mt_mvt, (SELECT SUM(mt_reg) FROM REG WHERE reg.id_affaire = mvt.id_affaire AND reg.id_mvt = mvt.id_mvt) mt_reg FROM MVT WHERE mvt.id_affaire = :pid ) GROUP BY id_affaire

Je me souviens que j'avais fait ceci pour une question de perf :

Code:

1
2
3
4
5
6
SELECT id_affaire, SUM(mt_mvt) / DECODE(COUNT(reg.idreg), 0,1, COUNT(reg.irreg)), 
				SUM(mt_reg)
FROM mvt LEFT OUTER reg ON mvt.id_mvt = reg.id_mvt
                        AND mvt.id_affaire = reg.id_affaire
WHERE mvt.id_affaire = :pid
GROUP BY id_affaire

Salut ,

Si on essaie ce code

Code:

1
2
3
4
5
6
7
 
SELECT   id_affaire, SUM (mt_mvt) mt_mvt, (SELECT SUM (mt_reg)
                                             FROM reg
                                            WHERE :pid = id_affaire) mt_reg
    FROM mvt
   WHERE mvt.id_affaire = :pid
GROUP BY id_affaire

J'y avais pensé, mais si un REG correspond à une affaire sans MVT, c'est pas bon.
C'est pour ça qu'il fait un EXISTS dans sa seconde requete.

salut,

J'ai compris, mais on peux rajouter une condition
id_mvt is not null, comme ca on va éliminer les reglements qui n'ont pas de mouvements

Code:

1
2
3
4
5
6
7
8
 
SELECT   id_affaire, SUM (mt_mvt) mt_mvt, (SELECT SUM (mt_reg)
                                             FROM reg
                                            WHERE :pid = id_affaire and 
                                             id_mvt is not null ) mt_reg
    FROM mvt
   WHERE mvt.id_affaire = :pid
GROUP BY id_affaire

Citation:

Envoyé par salim11

salut,

J'ai compris, mais on peux rajouter une condition
id_mvt is not null, comme ca on va éliminer les reglements qui n'ont pas de mouvements

Code:

1
2
3
4
5
6
7
8
 
SELECT   id_affaire, SUM (mt_mvt) mt_mvt, (SELECT SUM (mt_reg)
                                             FROM reg
                                            WHERE :pid = id_affaire and 
                                             id_mvt is not null ) mt_reg
    FROM mvt
   WHERE mvt.id_affaire = :pid
GROUP BY id_affaire

Dans ce cas là on prend tous les reg même des autres mouvements de l'affaire.

Citation:
Envoyé par McM
Je me souviens que j'avais fait ceci pour une question de perf :
Code:

1 2 3 4 5 6 SELECT id_affaire, SUM(mt_mvt) / DECODE(COUNT(reg.idreg), 0,1, COUNT(reg.irreg)), SUM(mt_reg) FROM mvt LEFT OUTER reg ON mvt.id_mvt = reg.id_mvt AND mvt.id_affaire = reg.id_affaire WHERE mvt.id_affaire = :pid GROUP BY id_affaire
Je croyais que c'étais bon mais

Code:

SUM(mt_mvt) / DECODE(COUNT(reg.idreg), 0,1, COUNT(reg.irreg))

Avec :
mvt reg
100 50
100 50
200 200

donne (100+100+200) / 3 => 400/3 => 133 alors qu'il me faut 300
Il faudrait

Code:

SUM(mt_mvt /DECODE(COUNT(reg.idreg), 0,1, COUNT(reg.irreg)))

Sauf que là oracle n'aime pas trop ;)

Citation:

Envoyé par orafrance

la fonction analytique SUM me parait bien utile dans ce cas ;)

Je suis preneur de toute piste car j'ai beau lire le cours sur les fonctions analytiques je n'y comprend toujours rien :oops: :pleure:

Salut,

il va faire la somme de tous les réglements de différents mouvements (non null) pour une affaire

OK. merci pour l'explication mais la requete ne passe pas le parseur : "not a single group function" sur la somme du reg

Code:

1
2
3
4
5
6
7
8
 
SELECT   id_affaire, SUM (mt_mvt) mt_mvt, (SELECT SUM (mt_reg)
                                             FROM reg
                                            WHERE :pid = id_affaire AND 
                                             id_mvt IS NOT NULL ) mt_reg
    FROM mvt
   WHERE mvt.id_affaire = :pid
GROUP BY id_affaire

J'ai tenté les fonctions analytiques mais sans succès :(
Code:

1 2 3 4 5 6 7 SELECT id_affaire, SUM(mt_mvt) OVER (PARTITION BY reg.id_affaire) mt_mvt, SUM(mt_reg) OVER (PARTITION BY reg.id_affaire) mt_reg FROM mvt LEFT OUTER reg ON mvt.id_mvt = reg.id_mvt AND mvt.id_affaire = reg.id_affaire WHERE mvt.id_affaire = :pid
Bien sur le montant des reglement est correct mais pas celui des mouvement car il somme toutes les lignes de id_affaire alors que je voudrait qu'il somme toutes les lignes avec le id_mvt distinct :s

Une fois de plus je n'ai pas de bol avec cette approche :aie:

Salut,

Je pense il faut utiliser le group by rollup ou group by cube

essaie ce code

Code:

1
2
3
4
5
6
 
select mvt.id_affaire,mvt.id_mvt,reg.id_reg , sum(mt_mvt),sum(reg.mt_reg)
from mvt LEFT OUTER join   reg ON mvt.id_mvt = reg.id_mvt
                        AND mvt.id_affaire = reg.id_affaire
where  mvt.id_affaire = :pid
group by rollup(mvt.id_affaire,mvt.id_mvt,reg.id_reg)

Il me donne le résultat suivant
la somme par id_affaire , id_affaire-id_mvt-id_reg, id_affaire-id_mvt

Code:

1
2
3
4
5
6
7
8
9
10
11
 
ID_AFFAIRE  ID_MVT  ID_REG   SUM(MT_MVT)	SUM(REG.MT_REG)
 
10	      1	     1	        30	               1000
10	      1	     2	        30	               4000
10	      1	     3	        30	               5000
10	      1		        90	               10000
10	      2	     4	        60	               10000
10	      2		        60                     10000
10			       150	               20000
			       150                     20000

Citation:

Envoyé par salim11

Salut,

Code:

1
2
3
4
5
6
7
8
9
10
11
 
ID_AFFAIRE  ID_MVT  ID_REG   SUM(MT_MVT)	SUM(REG.MT_REG)
 
10	      1	     1	        30	               1000
10	      1	     2	        30	               4000
10	      1	     3	        30	               5000
10	      1		        90	               10000
10	      2	     4	        60	               10000
10	      2		        60                     10000
10			       150	               20000
			       150                     20000

Merci beaucoup pour ton investissement mais celà ne répond pas encore à mon besoin :calim2: . Voilà ce que je souhaiterais en me basant sur ton cas

Code:

1
2
3
4
5
6
7
8
9
10
11
 
ID_AFFAIRE  ID_MVT  ID_REG   SUM(MT_MVT)	SUM(REG.MT_REG)
 
10	      1	     1	        30	               1000
10	      1	     2	          	               4000
10	      1	     3	          	               5000
10	      1		        30	               10000
10	      2	     4	        60	               10000
10	      2		        60                     10000
10			       90	               20000
			       90                     20000

Le montant au niveau du mvt 1 doit bien être égal à 30 au final pas 30*3 (réglements).

Sinon tu m'a appris un nouvel ordre Oracle :)

Merci encore pour ton aide.

Salut,
Essaie ce code

Code:

1
2
3
4
5
6
7
SELECT   mvt.id_affaire, mvt.id_mvt,reg.id_reg, avg (mt_mvt) mt_mvt,sum (mt_reg)mt_reg 
              FROM mvt left OUTER JOIN reg
                   ON mvt.id_mvt = reg.id_mvt
                 AND mvt.id_affaire = reg.id_affaire
where mvt.id_affaire = :pid          --     and mvt.id_mvt=:s
          GROUP BY ROLLUP (mvt.id_affaire,mvt.id_mvt,reg.id_reg)

Merci beaucoup pour l'astuce. Je regarde çà demain au boulot.

Retour boulot... Bon ben le AVG ne marche pas car il me renvoie un avg entre 30 et 60 => 45 au lien de 90 :s

Si vous avez d'autres pistes :roll:

Salut ,

Code amélioré:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT   mvt.id_affaire id_aff, mvt.id_mvt id_mvt , mvt.mt_mvt, reg.mt_reg
    FROM (SELECT   mvt.id_affaire, mvt.id_mvt, SUM (mt_mvt) mt_mvt
              FROM  mvt
          GROUP BY ROLLUP (mvt.id_affaire, mvt.id_mvt)) mvt,
         (SELECT   id_affaire, id_mvt, SUM (mt_reg) mt_reg
              FROM reg
          GROUP BY id_affaire, id_mvt) reg
   WHERE mvt.id_mvt = reg.id_mvt(+)
     AND mvt.id_affaire = reg.id_affaire(+)
    -- AND mvt.id_affaire = :pid                        --     and mvt.id_mvt=:s
ORDER BY mvt.id_affaire, mvt.id_mvt

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
ID_AFF	ID_MVT	MT_MVT	MT_REG
 
10	1	10	200
10	2	10	
10	3	10	
10		30	
20	4	10	
20	5	20	
20		30	
30	6	500	
30		500	
		560

Nickel çà marche.

Sauf niveau perf :(

Merci à tous pour votre aide.

Salut ,
J'ai trouvé un article qui par de la performance, ils disent que GROUP BY grouping sets plus performant que le group by rollup et group by cube

Voici l'article integrale page 19 et 20
http://www2.ift.ulaval.ca/~agamache/...Section1F2.pdf

Solution amélioré, sans le group by rollup

Salut,

J'ai trouvé un moyen plus simple et donne le meme le résultat ,pour éviter le group by rollup (qui diminuait la performance).
1- Solution

Code:

1
2
3
4
5
6
7
8
9
 
SELECT   T.id_affaire,  T.id_mvt,  T.mt_mvt, sum(T.mt_reg) mt_reg
 FROM 
    (SELECT   mvt.id_affaire,  mvt.id_mvt,  sum(mt_reg) mt_reg, 
                  sum(mt_mvt)  mt_mvt   
       FROM mvt LEFT OUTER JOIN   reg ON mvt.id_mvt = reg.id_mvt                          
           AND mvt.id_affaire = reg.id_affaire  
       GROUP BY mvt.id_affaire,  mvt.id_mvt,  reg.id_reg) T
 GROUP BY  T.id_affaire,  T.id_mvt,  T.mt_mvt

2-Solution

Code:

1
2
3
4
5
6
7
8
9
10
 
SELECT  
  mvt.id_affaire,
  mvt.id_mvt,sum(mt_mvt)  mt_mvt ,
  (SELECT SUM(mt_reg)
     FROM   REG
     WHERE   reg.id_affaire = mvt.id_affaire   			
         AND  reg.id_mvt 	= mvt.id_mvt) mt_reg  
 FROM mvt 
GROUP BY mvt.id_affaire,  mvt.id_mvt