Requête avec 3 tables et dédoublonnage et exclusion

Version imprimable

15/09/2018, 13h39
Zeldhaking

Requête avec 3 tables et dédoublonnage et exclusion

Bonjour à tous, je suis novice en MySQL et là je bloque sur la création d'une requête.
Je ne sais pas si ce que je cherche à faire est faisable ni comment ce serait faisable.

J'ai 3 tables sur lesquels je n'ai pas la main et qui sont très mal pensées sans clé unique, le seul lien est le numéro de client mais qui peut apparaître plusieurs fois dans la même table:
- une table "intervention" (num_client, saison, date_inter, agence, statut_inter, resultat_echec)
- une table "contact_client" (num_client, date contact, resultat_contact)
- une base "coordonnees_clients" qui ne contient pas une ligne pour tous les clients (num_client, tel_client)

Dans la table "intervention" les champs peuvent prendre les valeurs suivantes:
- saison ("Eté", "Hiver")
- agence ("Agence1", "Agence2", "Agence3")
- statut_inter ("A planifier", "Planifié", "Réalisé")
- resultat_echec ("Opposition", "Technique", VIDE)

Dans la table "contact_client" les champs peuvent prendre les valeurs suivantes:
- resultat_contact ("RDV pris", "Opposition", "Mauvaises coordonnées")

Ce que je cherche à faire, regrouper par saison et par agence :
- le nombre de clients uniques en opposition (certains peuvent y être dans la table "intervention" et/ou dans la table "contact_client" et à plusieurs reprises)
- le nombre de clients uniques sans coordonnées et non en opposition

| Saison | Agence | Clients en opposition | Clients sans coordonnées et non en opposition |
| 1 | 1 | 23 | 4 |
| 1 | 2 | 7 | 8 |
| 1 | 3 | 13 | 24 |
| 2 | 1 | 53 | 4 |
| 2 | 2 | 27 | 7 |
...

Je suis preneur de vos suggestions...
18/09/2018, 11h44
CinePhil

1) Les champs sont à la campagne ou dans les formulaires, pas dans les tables SQL qui ne sont composées que de colonnes et de lignes.

2) Qu'avez-vous essayé comme requête ?
18/09/2018, 13h23
escartefigue

Bonjour,

En l'état, on ne peut pas répondre à l'expression de besoin sans plus de précision sur les règles de gestion.
Comme vous précisez qu'un même client peut être présent plusieurs fois dans la table "interventions" on peut donc avoir un même client dont le statut (colonne "resultat_echec") prend des valeurs différentes. En ce cas que faut il faire si pour ce client on trouve à la fois "opposition" et une autre valeur, comment faut il le considérer ?
Comme apparemment il n'y a aucune colonne d'horodatage, on ne peut même pas savoir que est le dernier statut pour un client (sauf s'il existe des colonnes que vous n'avez pas citées)

Citation:

Envoyé par CinePhil

1) Les champs sont à la campagne ou dans les formulaires, pas dans les tables SQL qui ne sont composées que de colonnes et de lignes.

2) Qu'avez-vous essayé comme requête ?

Merci CinePhil de l’intérêt porté à ma demande, désolé pour l'utilisation à tord du "champ".

Voici le code que j'utilise et qui répond au premier besoin, c'est pour le second que je sèche:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 SELECT INTERVENTION.saison, INTERVENTION.agence, SUM(IF( (SELECT COUNT(DISTINCT(CASE WHEN i.resultat_echec = 'Opposition' THEN i.num_client END)) FROM INTERVENTION i WHERE INTERVENTION.num_client = i.num_client ) + (SELECT COUNT(DISTINCT(CASE WHEN (cc.resultat_contact = 'Opposition' OR cc.resultat_contact = 'Opposition2') THEN cc.num_client END)) FROM contact_client cc WHERE INTERVENTION.num_client = cc.num_client ) >=1,1,0)) as "clients uniques en opposition", "?" as "clients uniques sans coordonnées et non en opposition" FROM INTERVENTION WHERE INTERVENTION.statut_inter = "A planifier" GROUP BY 1,2
J'aurais bien l'idée de lister les num_client sans coordonnées et d'exclure tous les num_client en opposition puis de compter les num_client uniques mais ç me semble compliqué et lourd?

Précision, les tables sont lourdes et la requête met entre 400 et 600 secondes à s'exécuter, j'aimerais optimiser...

18/09/2018, 22h33
Zeldhaking

Citation:

Envoyé par escartefigue

Bonjour,

En l'état, on ne peut pas répondre à l'expression de besoin sans plus de précision sur les règles de gestion.
Comme vous précisez qu'un même client peut être présent plusieurs fois dans la table "interventions" on peut donc avoir un même client dont le statut (colonne "resultat_echec") prend des valeurs différentes. En ce cas que faut il faire si pour ce client on trouve à la fois "opposition" et une autre valeur, comment faut il le considérer ?
Comme apparemment il n'y a aucune colonne d'horodatage, on ne peut même pas savoir que est le dernier statut pour un client (sauf s'il existe des colonnes que vous n'avez pas citées)

Les interrogations sont pertinentes ;)
En effet un même client peut apparaitre avec plusieurs "resultat_echec" dans la table "intervention" et/ou plusieurs "resultat_contact" dans la table "contact_client".
Même si il y a comme horodatage respectivement "date_inter" et "date_contact", le statut "Opposition" prévaut.

Je ne suis pas sur d'avoir tout saisi donc faudra bien vérifier ;)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
	b.saison,
	b.agence,
	COUNT(DISTINCT CASE WHEN
		b.resultat_echec = 'Opposition' OR
		a.resultat_contact = 'Opposition' OR
		a.resultat_contact = 'Opposition2' THEN a.num_client ELSE NULL END) 'clients_uniques_opposition',
	COUNT(DISTINCT CASE WHEN c.num_client IS NULL  AND
		(b.resultat_echec <> 'Opposition' OR b.resultat_echec IS NULL) AND
		a.resultat_contact <> 'Opposition' AND
		a.resultat_contact <> 'Opposition2'THEN a.num_client ELSE NULL END) 'clients_uniques_sans_coordonnées_non_opposition'
FROM contact_client a
	INNER JOIN intervention b ON a.num_client = b.num_client
	LEFT JOIN coordonnees_clients c ON a.num_client = c.num_client
WHERE
	intervention.statut_inter = "A planifier"
GROUP BY 
	b.saison,
	b.agence

Le mieux, que ça soit pour sortir le bon résultat ou pour l'optimisation, ça serait que tu nous donnes les

Code:

SHOW CREATE TABLE ta_table

de chacune des tables.

19/09/2018, 10h00
escartefigue

Citation:

Envoyé par Zeldhaking

Même si il y a comme horodatage respectivement "date_inter" et "date_contact", le statut "Opposition" prévaut.

Si la présence suffit, alors le prédicat (NOT) EXISTS est la pour ça ;)

EDIT

Ca aussi c'est gênant :

Citation:

Envoyé par Zeldhaking

- le nombre de clients uniques en opposition (certains peuvent y être dans la table "intervention" et/ou dans la table "contact_client" et à plusieurs reprises)

Comment déterminer l'agence et la saison, si le client n'est que dans la table "contact" qui ne possède pas ces attributs ?

Vous avez raison : votre modèle de données est vraiment pourri :aie:
Si vous avez moyen de le corriger, ça serait nettement plus simple

Voici une solution partielle pour identifier le nombre de clients distinct en opposition, solution qui tient compte du fait que
- C'est la présence d'une occurrence d'opposition qui fait foi, indépendamment de la date de survenance et de la présence d'autres valeurs
- Les clients présents dans la table "contact_client" mais pas dans la table "intervention" ne peuvent pas être dispatchés par agence ni par saison faute d'information en ce sens

Je suis passé par des CTE pour créer des jeux d'essai simplifiés, vous n'en aurez pas besoin puisque vous avez déjà les tables
Les noms des tables et attributs sont simplifiés pour gagner du temps, mais le principe reste le même

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
 
-- table des interventions
with intv (IN_Clie, IN_Sais, IN_Dat1, IN_Agce, IN_Stat) as
 (select 001, 'été', '2018-07-01', 'AG1', 'Oppo' 
  union all                                      
  select 001, 'été', '2018-07-02', 'AG1', 'Tech' 
  union all                                      
  select 001, 'été', '2018-07-04', 'AG1', 'Oppo' 
  union all                                      
  select 002, 'hiv', '2018-01-05', 'AG2', '    ' 
  union all                                      
  select 001, 'hiv', '2018-01-06', 'AG1', '    ' 
  union all                                      
  select 003, 'hiv', '2018-02-06', 'AG3', '    ' 
  union all                                      
  select 003, 'hiv', '2018-02-08', 'AG3', 'Tech' 
  union all                                      
  select 004, 'été', '2018-08-02', 'AG1', 'Oppo' 
  union all                                      
  select 004, 'hiv', '2018-03-12', 'AG2', 'Tech' 
  union all                                      
  select 007, 'hiv', '2018-03-12', 'AG2', 'Oppo' 
 )                                               
-- table des contacts
   , cont (CN_Clie, CN_Dat1, CN_Stat) as  
 (select 002, '2018-05-12', 'Oppo'        
  union all                               
  select 005, '2018-06-20', '    '        
  union all                               
  select 003, '2018-09-02', '    '        
 )                                        
-- table des coordonnées
   , coor (CO_Clie, CO_Tel1) as                 
 (select 002, '01.02.03.04.05'                  
  union all                                     
  select 004, '06.06.06.06.06'                  
  union all                                     
  select 005, '02.03.04.05.06'                  
 )

La requête suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
  select coalesce(IN_Sais, '???')                  
       , coalesce(IN_Agce, 'XXX')                  
       , count(distinct coalesce(IN_Clie, CN_Clie))
  from intv                                        
  full outer join cont                             
    on cont.CN_Clie = intv.IN_Clie                 
   and cont.CN_Stat = intv.IN_Stat                 
  where coalesce(IN_Stat, CN_Stat)='Oppo'          
  group by coalesce(IN_Sais, '???')                
         , coalesce(IN_Agce, 'XXX')                
  ;

Donne :

Code:

1
2
3
??? XXX           1
hiv AG2           1
été AG1           2

Si vous utilisez une version MySQL antérieure à la V8, il faut en plus adapter la requête car les jointures "FULL OUTER" ne sont pas supportées :aie:
La première ligne correspond aux éléments issus de la table contact seule, les codes saison et agence sont inconnus par la force des choses !