[SQL] count() sur plusieurs tables [Résolu]

[philodido]

Bonjour,

Je cherche depuis un bon moment comment faire une requête.

Voici l'énoncé de mon problème :

J'ai 3 tables qui se présentent comme suit :

table des négociateur (t_negociateur) :

negociateur_id | neg_nom

1 | Dupont
2 | Durant
3 | Tartanpion
... ...


table des erreurs sur les offres (check_offres) :

offre_id | neg_id

1 | 1
2 | 2
3 |3
4 |1
5 | 1
... ...


table des erreurs sur les demandes (check_demandes) :

demande_id | neg_id

1 | 1
2 | 2
3 | 3
4 | 3
5 | 2
... ...


Et je voudrai resortir un resultat comme suit :


nom du négociateur | nombre erreurs offres | nombre erreurs demandes

Dupont | 64 | 26
Durant | 31 | 23
Tartanpion |28 | 37


Attention : Je voudrais sortir ce résultat en 1 seule requête pour pouvoir faciliter les tris de mon tableau par la suite.

Merci d'avance de votre aide.

[asmduty]

Bonjour,
tu joins les 3 tables et tu fais un count(offre) et un count(demande) en groupant par negociateur

[philodido]

Oui c'est ce que j'ai essayé mais le résultat il ne distingue pas le nombre d'erreurs offres du nombre d'erreurs demande.

[punkoff]

bonjour,

je vois en gros deux solutions :

Code :

1
2
3
4
5
 
SELECT a.*,
(SELECT count(*) FROM offres b WHERE a.negociateur_id = b.neg_id),
(SELECT count(*) FROM demandes c WHERE a.negociateur_id = b.neg_id)
FROM negociateur a

et sinon

Code :

1
2
3
4
5
6
7
8
 
SELECT a.negociateur_id, a.neg_nom,
sum(case when b.neg_id IS NOT NULL then 1 else 0),
sum(case when c.neg_id IS NOT NULL then 1 else 0),
FROM negociateur a
LEFT OUTER JOIN offres b ON a.negociateur_id = b.neg_id
LEFT OUTER JOIN demandes c ON a.negociateur_id = b.neg_id
GROUP BY a.negociateur_id, a.neg_nom

A tester les deux solutions pour prendre celle qui a le meilleur plan d'acces.

[Waldar]

Je n'aime aucune des deux solutions du dessus !

Code :

1
2
3
4
5
6
7
8
9
  SELECT a.neg_nom
       , coalesce(b.nb_off, 0) AS nb_off
       , coalesce(c.nb_dmd, 0) AS nb_dmd
    FROM negociateur a
         LEFT OUTER JOIN (SELECT neg_id, count(*) AS nb_off FROM offres   GROUP BY neg_id) b
           ON b.neg_id = a.negociateur_id
         LEFT OUTER JOIN (SELECT neg_id, count(*) AS nb_dmd FROM demandes GROUP BY neg_id) c
           ON c.neg_id = a.negociateur_id
ORDER BY a.negociateur_id ASC;

[philodido]

J'ai essayé la 2eme solution mais j'obtiens le message suivant :

Msg*102, Niveau*15, État*1, Ligne*2
Syntaxe incorrecte vers ')'.

avant cela j'ai déjà enlevé la virgule après la parenthèse fermante du 2eme SUM parce que j'avais une erreur dessus.

Autre précision que j'ai oublié dans l'énoncé :

Je dois aussi vérifier les champs : 'agence_id' et 'neg_actif' de la table t_negociateurs en faisant qqc comme : "WHERE N.agence_id=1 and N.neg_actif = 1" ... je ne pense pas que cela ait une quelconque importance mais je préfère le préciser.

[punkoff]

ah oui j'ai oublié le end désolé,

Code :

1
2
3
4
5
6
7
8
 
SELECT a.negociateur_id, a.neg_nom,
sum(case when b.neg_id IS NOT NULL then 1 else 0 end),
sum(case when c.neg_id IS NOT NULL then 1 else 0 end),
FROM negociateur a
LEFT OUTER JOIN offres b ON a.negociateur_id = b.neg_id
LEFT OUTER JOIN demandes c ON a.negociateur_id = b.neg_id
GROUP BY a.negociateur_id, a.neg_nom

ceci étant dit, si Waldar dit qu'il préfère sa solution écoutez-le il a dû comparer les plans pour en déduire que sa proposition est meilleur.


Sinon, vous pouvez rajouter votre clause where sans aucun problème dans les 3 cas cités.

[boussafi]

voilà la requete qe tu cherche

Code :

1
2
3
4
5
6
7
8
9
SELECT  t_negociateur.nom_neg,nbre_demande,nbre_offre
FROM (SELECT count(neg_id) AS nbre_demande ,neg_id
FROM check_demandes
GROUP BY neg_id)demande  
RIGHT JOIN (
(SELECT count(neg_id) AS nbre_offre ,neg_id
FROM check_offres
GROUP BY neg_id)offre  
RIGHT JOIN t_negociateur ON offre.neg_id = t_negociateur.negotiateur_id) ON demande.neg_id = t_negociateur.negotiateur_id

Resulat d'apres l'exemple que tu as donné.

Code :

1
2
3
4
5
 
nom_neg	nbre_demande	nbre_offre
Dupont	             1	3
Durant	             2	1
Tartanpion	2	1

[philodido]

Envoyé par Waldar

Je n'aime aucune des deux solutions du dessus !

Code :

Je pense que c'est presque ça mais pas tout à fait car, par exemple pour Tatampion qui a 6 erreurs d'offre et 22 erreurs de demande, j'obtiens le résultat suivant :

neg_nom | nb_off | nb_dmd

Tartampion | 6 | 22
Tartampion | 0 | 0
Tartampion | 3 | 0

Je pense que c'est dut au fait que l'id de Tartampion apparait plusieurs fois dans la table t_negociateur. Que dois-je faire pour n'avoir qu'une ligne de résultat par négociateur ?

[StringBuilder]

Envoyé par Waldar

Je n'aime aucune des deux solutions du dessus !

Personnellement, je préfère la requête avec les sous-select dans la liste des colonnes du select, plutôt que ta solution (moins lisible je trouve).
De plus, niveau perf, je ne suis pas certain qu'il n'y trouve la moindre différence, puisque les deux font absolument la même chose (au bug près)

[skuatamad]

Envoyé par philodido

Je pense que c'est dut au fait que l'id de Tartampion apparait plusieurs fois dans la table t_negociateur.

Tu veux dire que negociateur_id n'est pas la clé primaire de la table negociateur ? Son nom est très mal choisi alors.

Envoyé par StringBuilder

De plus, niveau perf, je ne suis pas certain qu'il n'y trouve la moindre différence, puisque les deux font absolument la même chose

Ben non, les plans peuvent être différents.
Personnelement je me serais naturellement orienté vers les sum(case...), comme quoi les goûts et les couleurs ...
Il faut donc soit choisir la requête la plus performante, soit, si les perfs sont les mêmes, celle que l'on préfère.

[StringBuilder]

Envoyé par skuatamad

Tu veux dire que negociateur_id n'est pas la clé primaire de la table negociateur ? Son nom est très mal choisi alors.

Ben non, les plans peuvent être différents.
Personnelement je me serais naturellement orienté vers les sum(case...), comme quoi les goûts et les couleurs ...
Il faut donc soit choisir la requête la plus performante, soit, si les perfs sont les mêmes, celle que l'on préfère.

Autant le sum/case n'aura pas le même plan d'exécution, autant la requête proposée par Waldar, devrait certainement avoir le même plan que les sous-select en colonne, ou presque : on retrouve les mêmes sous-select, c'est juste la façon de ramener le résultat qui n'est pas écrite de la même façon.

Dans les deux cas, l'optimiseur va de toute façon convertir les sous-select en jointures simples (ou alors on est sur MySQL ou PostGreSQL et leur optimiseur pourrave).
Il est même possible que les sous-select en colonne auront un plan plus légèrement plus simple que la requête de waldar qui fait des outer join sur des sous-select. En tout cas, je doute fortement que le plan soit significativement différent.

[Waldar]

Non mais il n'y a pas besoin de plan d'exécution ici, il suffit de lire les requêtes.

La première requête est une requête scalaire.
Elles sera efficace sur un petit nombre d'id, mais pas sur des gros volumes.
Pourquoi ?
Parce qu'en gros, elle va compter le nombre d'occurrence id par id dans les tables filles.

La deuxième requête crée un produit cartésien entre les tables offres et demandes, et après on compte les valeurs renseignées dans ce résultats.
À exclure d'office.

La requête que j'ai proposée consolide d'abord les données avant de faire les jointures.
Ce sera efficace quel que soit les volumétries en jeu.

[StringBuilder]

Envoyé par Waldar

Parce qu'en gros, elle va compter le nombre d'occurrence id par id dans les tables filles.

Ca, c'est faux.
Avec PostGreSQL ou MySQL, je dis pas, mais Oracle, absolument sûr et certain, il réécrit la requête sous forme d'une jointure simple avant de faire le comptage des lignes.
Avec MS SQL Server, je n'en mettrai pas ma main à couper, mais je doute fortement qu'il ne fasse pas la même chose.

Certes, je suis d'accord sur le fait que la requête laisse penser ça, mais comme vous me l'avez si bien dit, les SGBD traitent les données de façon ensembliste : dont même si on écrit une requête d'une façon scalaire, il va tenter de la traiter de façon ensembliste.

Pour moi, à un détail près, la requête réellement exécutée sera celle que tu préconise. Mais d'un point de vue lecture, je trouve l'autre sémantiquement plus lisible.

[Waldar]

Sur Oracle 11gR1 :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE t1
(
    t_id   integer,
    t_name varchar(10),
    constraint pk_t1    PRIMARY KEY (t_id)
)
organization INDEX;
 
CREATE TABLE t2
(
    t_id  integer,
    constraint fk_t2_t1 FOREIGN KEY (t_id) REFERENCES t1(t_id)
);
 
CREATE TABLE t3
(
    t_id  integer,
    constraint fk_t3_t1 FOREIGN KEY (t_id) REFERENCES t1(t_id)
);

On ne va mettre que trois lignes dans la table de référence t1, 100.000 dans t2, 1.000.000 dans t3 :

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
INSERT INTO t1 (t_id, t_name) VALUES (1, 'Code1');
INSERT INTO t1 (t_id, t_name) VALUES (2, 'Code2');
INSERT INTO t1 (t_id, t_name) VALUES (3, 'Code3');
 
commit;
 
INSERT INTO t2 (t_id)
SELECT mod(level, 3)+1
  FROM dual
connect BY level <= 1e5+1;
 
commit;
 
INSERT INTO t3 (t_id)
SELECT mod(level, 3)+1
  FROM dual
connect BY level <= 1e6+2;
 
commit;
 
begin
  DBMS_STATS.GATHER_TABLE_STATS(ownname => user, tabname => 'T1', estimate_percent => 100, cascade => true, degree => 4);
  DBMS_STATS.GATHER_TABLE_STATS(ownname => user, tabname => 'T2', estimate_percent => 100, cascade => true, degree => 4);
  DBMS_STATS.GATHER_TABLE_STATS(ownname => user, tabname => 'T3', estimate_percent => 100, cascade => true, degree => 4);
end;
/

Comparaison des plans estimés :

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
EXPLAIN plan FOR
SELECT t1.t_name
     , (SELECT count(*) FROM t2 WHERE t2.t_id = t1.t_id) AS nb_t2
     , (SELECT count(*) FROM t3 WHERE t3.t_id = t1.t_id) AS nb_t3
  FROM t1;
 
SELECT * FROM TABLE(dbms_xplan.display);
 
EXPLAIN plan FOR
  SELECT t1.t_name
       , count(t2.t_id) AS nb_t2
       , count(t2.t_id) AS nb_t3
    FROM t1
         LEFT OUTER JOIN t2 ON t2.t_id = t1.t_id
         LEFT OUTER JOIN t3 ON t3.t_id = t1.t_id
GROUP BY t1.t_name;
 
SELECT * FROM TABLE(dbms_xplan.display);
 
EXPLAIN plan FOR
  SELECT t1.t_name
       , coalesce(t2.nb_t2, 0) AS nb_t2 
       , coalesce(t3.nb_t3, 0) AS nb_t3 
    FROM t1
         LEFT OUTER JOIN (SELECT t_id, count(*) AS nb_t2 FROM t2 GROUP BY t_id) t2 ON t2.t_id = t1.t_id
         LEFT OUTER JOIN (SELECT t_id, count(*) AS nb_t3 FROM t3 GROUP BY t_id) t3 ON t3.t_id = t1.t_id;
 
SELECT * FROM TABLE(dbms_xplan.display);

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
----------------------------------------------------------------------------         
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |         
----------------------------------------------------------------------------         
|   0 | SELECT STATEMENT   |       |     3 |    30 |     1   (0)| 00:00:01 |         
|   1 |  SORT AGGREGATE    |       |     1 |     3 |            |          |         
|*  2 |   TABLE ACCESS FULL| T2    | 33334 |    97K|    48   (3)| 00:00:01 |         
|   3 |  SORT AGGREGATE    |       |     1 |     3 |            |          |         
|*  4 |   TABLE ACCESS FULL| T3    |   333K|   976K|   315   (3)| 00:00:05 |         
|   5 |  INDEX FULL SCAN   | PK_T1 |     3 |    30 |     1   (0)| 00:00:01 |         
----------------------------------------------------------------------------         
 
Predicate Information (IDENTIFIED BY operation id):                                  
---------------------------------------------------                                  
 
   2 - filter("T2"."T_ID"=:B1)                                                       
   4 - filter("T3"."T_ID"=:B1)         
 
 
--------------------------------------------------------------------------------     
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |     
--------------------------------------------------------------------------------     
|   0 | SELECT STATEMENT       |       |     3 |    48 |  2232K(100)| 08:41:01 |     
|   1 |  HASH GROUP BY         |       |     3 |    48 |  2232K(100)| 08:41:01 |     
|*  2 |   HASH JOIN RIGHT OUTER|       |    33G|   496G|   133K(100)| 00:31:12 |     
|   3 |    TABLE ACCESS FULL   | T2    |   100K|   292K|    48   (3)| 00:00:01 |     
|*  4 |    HASH JOIN OUTER     |       |  1000K|    12M|   319   (4)| 00:00:05 |     
|   5 |     INDEX FULL SCAN    | PK_T1 |     3 |    30 |     1   (0)| 00:00:01 |     
|   6 |     TABLE ACCESS FULL  | T3    |  1000K|  2929K|   313   (2)| 00:00:05 |     
--------------------------------------------------------------------------------     
 
Predicate Information (IDENTIFIED BY operation id):                                  
---------------------------------------------------                                  
 
   2 - access("T2"."T_ID"(+)="T1"."T_ID")                                            
   4 - access("T3"."T_ID"(+)="T1"."T_ID")    
 
-------------------------------------------------------------------------------      
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |      
-------------------------------------------------------------------------------      
|   0 | SELECT STATEMENT      |       |     3 |   186 |   404  (13)| 00:00:06 |      
|*  1 |  HASH JOIN OUTER      |       |     3 |   186 |   404  (13)| 00:00:06 |      
|*  2 |   HASH JOIN OUTER     |       |     3 |   108 |   352  (13)| 00:00:05 |      
|   3 |    INDEX FULL SCAN    | PK_T1 |     3 |    30 |     1   (0)| 00:00:01 |      
|   4 |    VIEW               |       |     3 |    78 |   350  (13)| 00:00:05 |      
|   5 |     HASH GROUP BY     |       |     3 |     9 |   350  (13)| 00:00:05 |      
|   6 |      TABLE ACCESS FULL| T3    |  1000K|  2929K|   313   (2)| 00:00:05 |      
|   7 |   VIEW                |       |     3 |    78 |    52  (10)| 00:00:01 |      
|   8 |    HASH GROUP BY      |       |     3 |     9 |    52  (10)| 00:00:01 |      
|   9 |     TABLE ACCESS FULL | T2    |   100K|   292K|    48   (3)| 00:00:01 |      
-------------------------------------------------------------------------------      
 
Predicate Information (IDENTIFIED BY operation id):                                  
---------------------------------------------------                                  
 
   1 - access("T2"."T_ID"(+)="T1"."T_ID")                                            
   2 - access("T3"."T_ID"(+)="T1"."T_ID")

Les plans me paraissent plutôt différents et plutôt en accord avec ce que j'annonce :

• notez l'utilisation d'une bind variable en mode d'accès pour la première requête.
• notez l'estimation à 33 milliards de lignes dans une étape intermédiaire pour la deuxième requête.

[philodido]

@Waldar : Grand merci pour ta solution, après avoir testé et retesté ça marche impec

Et grand merci de votre aide à tous.


ps : ne vous battez pas !

[StringBuilder]

Envoyé par Waldar

Sur Oracle 11gR1 :

[...]

• notez l'utilisation d'une bind variable en mode d'accès pour la première requête.
• notez l'estimation à 33 milliards de lignes dans une étape intermédiaire pour la deuxième requête.

Alors là, je ne comprendrai jamais rien à Oracle.

Je viens de faire le test aussi sur une base Release 10.1.0.5.0, et j'obtiens les mêmes résultats que toi.

Pourtant, j'ai déjà débattu du sujet avec un DBA sur ce sujet (sur la 8i, 9i ?) il y a quelques temps, et je suis absolument certain que le sous-select en colonne étaient convertis en jointure simple dans le plan d'exécution

En tout cas, je viens de faire le test sur une base de données volumineuse :

Code :

1
2
3
4
5
6
7
8
9
10
 
SELECT e.numeve, 
      (SELECT count(*) FROM evp p WHERE p.codsoc = e.codsoc AND p.achvte = e.achvte AND p.typeve = e.typeve AND p.numeve = e.numeve) nb_p,
      (SELECT count(*) FROM evt t WHERE t.codsoc = e.codsoc AND t.achvte = e.achvte AND t.typeve = e.typeve AND t.numeve = e.numeve) nb_t
FROM eve e
WHERE e.codsoc = 100
AND e.achvte = 'V'
AND e.typeve = 'CDE'
AND e.numeve > 1000000
;

0,014 secondes pour ramener les 50 premières lignes dans SQL Developper

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT count(*) nb_e, sum(nb_p), sum(nb_t)
FROM (
SELECT e.numeve, 
      (SELECT count(*) FROM evp p WHERE p.codsoc = e.codsoc AND p.achvte = e.achvte AND p.typeve = e.typeve AND p.numeve = e.numeve) nb_p,
      (SELECT count(*) FROM evt t WHERE t.codsoc = e.codsoc AND t.achvte = e.achvte AND t.typeve = e.typeve AND t.numeve = e.numeve) nb_t
FROM eve e
WHERE e.codsoc = 100
AND e.achvte = 'V'
AND e.typeve = 'CDE'
AND e.numeve > 1000000
)
;
 
NB_E    NB_P    NB_T
------- ------- -------
436612	2087456	2513020

34,169 secondes pour ramener le résultat

Avec ta requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT e.numeve
       , coalesce(p.nb_p, 0) AS nb_p 
       , coalesce(t.nb_t, 0) AS nb_t
    FROM eve e
         LEFT OUTER JOIN (SELECT codsoc, achvte, typeve, numeve, count(*) AS nb_p FROM evp GROUP BY codsoc, achvte, typeve, numeve) p ON p.codsoc = e.codsoc AND p.achvte = e.achvte AND p.typeve = e.typeve AND p.numeve = e.numeve
         LEFT OUTER JOIN (SELECT codsoc, achvte, typeve, numeve, count(*) AS nb_t FROM evt GROUP BY codsoc, achvte, typeve, numeve) t ON t.codsoc = e.codsoc AND t.achvte = e.achvte AND t.typeve = e.typeve AND t.numeve = e.numeve
WHERE e.codsoc = 100
AND e.achvte = 'V'
AND e.typeve = 'CDE'
AND e.numeve > 1000000
;

22,904 secondes pour ramener les 50 premières lignes

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT count(*) nb_e, sum(nb_p), sum(nb_t)
(
SELECT e.numeve
       , coalesce(p.nb_p, 0) AS nb_p 
       , coalesce(t.nb_t, 0) AS nb_t
    FROM eve e
         LEFT OUTER JOIN (SELECT codsoc, achvte, typeve, numeve, count(*) AS nb_p FROM evp GROUP BY codsoc, achvte, typeve, numeve) p ON p.codsoc = e.codsoc AND p.achvte = e.achvte AND p.typeve = e.typeve AND p.numeve = e.numeve
         LEFT OUTER JOIN (SELECT codsoc, achvte, typeve, numeve, count(*) AS nb_t FROM evt GROUP BY codsoc, achvte, typeve, numeve) t ON t.codsoc = e.codsoc AND t.achvte = e.achvte AND t.typeve = e.typeve AND t.numeve = e.numeve
WHERE e.codsoc = 100
AND e.achvte = 'V'
AND e.typeve = 'CDE'
AND e.numeve > 1000000
)
;
 
NB_E    NB_P    NB_T
------- ------- -------
436612	2087456	2513020

=> 15,436 secondes pour ramener le résultat

Le résultat est assez étonnant pour ta requête : récupérer 50 lignes est plus long que pour faire des calculs sur les 436000 lignes.

En revanche, sur un grand volume, ta requête est effectivement plus rapide sur un traitement synchrone.

Mais en traitement assynchronme, ma requête retourne des lignes immédiatement, ce qui peut être appréciable, ça dépend de ce qu'on veut en faire.

Si je refais tourner les requête pour ne rammener qu'une ligne (numeve = 1000000) alors ma requête est un peu plus rapide (pour autant que de passer de 0,036 secondes à 0,012 puisse être significatif).

En tout cas, je retiens, en effet, les sous-select en colonne ne sont pas (plus ?) traduits en jointures.

[asmduty]

Et un simple :

Code :

1
2
3
4
5
6
SELECT negociateur_id, neg_nom, coalesce(count(offre_id, 0)) AS nb_off, coalesce(count(demande_id, 0)) AS nb_dmd
FROM t_negociateur 
LEFT OUTER JOIN check_offre AS O ON negociateur_id = O.neg_id
LEFT OUTER JOIN chech_demande AS D ON negociateur_id = D.neg_id
GROUP BY negociateur_id, neg_nom
ORDER BY negociateur_id, neg_nom

ne suffit pas et/ou est moins rapide ?

[StringBuilder]

Ca ne marche pas, ça fait faire un produit cartésien entre les lignes de check_offre et chech_demande, du coup les counts sont faussés.

Il faudrait trouver un ID unique par neg_id dans chacune de ces deux tables (avec la PK par exemple) et faire des count distinct dessus, en serrant très fort les fesses.

En fait, il faudrait tenter ça :

Code :

1
2
3
4
5
6
7
 
SELECT negociateur_id, neg_nom, coalesce(count(DISTINCT offre_id, 0)) AS nb_off, coalesce(count(DISTINCT demande_id, 0)) AS nb_dmd
FROM t_negociateur 
LEFT OUTER JOIN check_offre AS O ON negociateur_id = O.neg_id
LEFT OUTER JOIN chech_demande AS D ON negociateur_id = D.neg_id
GROUP BY negociateur_id, neg_nom
ORDER BY negociateur_id, neg_nom

-- Edit : Ta démarche était bonne, t'avais juste oublié les distinct. Comme ça, ça devrait effectivement marcher, à condition que offre_id et demande_id existent et soient bien uniques.

[asmduty]

Etrange, j'ai pas spécialement de quoi tester la mais il me semblait que le fait de faire la jointure sur le negociateur_ID avec les NEG_ID et de grouper ensuite par négociateur_ID, le fait de faire les count après suffirait
Je ne vois pas vraiment l'interet du distinct