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.