Discussion :

[majajoue]

Bonjour à tous ,

J'ai une requête sql à faire et je sollicite votre aide.

Pour simplifier, j'ai une base principale de contrats un id_client et une certaine date DATE_T.
BASE1 :
...
Id_client
DATE_T

J'ai une une autre base de contrats dans laquelle j'ai également un id_client ainsi que deux dates DATE_DEB et DATE_FIN
BASE2 :
Id_client
Prix1
Prix2
DATE_DEB
DATE_FIN


EN partant de BASE1 j'aimerais compter le nombre de contrats de la BASE2, la moyenne de Prix1 associées ainsi que celle de Prix2 vérifiant les 2 conditions suivantes :

BASE1.id_client = BASE2.id_client
BASE2.DATE_DEB <= BASE1.DATE_T <= BASE2.DATE_FIN


Je vous présente ma solution actuelle qui prend énormément de temps notamment à cause de la conditions sur les dates :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE Tab AS
 
    SELECT 
 
    BASE1.*,
 
    (SELECT count(*) FROM BASE2 WHERE BASE1.id_client = BASE2.id_client 
                                     AND BASE2.DATE_DEB <= BASE1.DATE_T <= BASE2.DATE_FIN) as NB_CT,
 
    (SELECT mean(Prix1) FROM BASE2 WHERE BASE1.id_client = BASE2.id_client 
                                          AND BASE2.DATE_DEB <= BASE1.DATE_T <= BASE2.DATE_FIN) as Prix1Moy,
 
    (SELECT mean(Prix2) FROM BASE2 WHERE BASE1.id_client = BASE2.id_client 
                                          AND BASE2.DATE_DEB <= BASE1.DATE_T <= BASE2.DATE_FIN) as Prix2Moy
 
 
FROM BASE1;

J'aimerais savoir comment vous auriez codé cette requête car je dois vraiment abandonner cette méthode qui prend un temps fou .
J'ai pensé à utiliser la fonction OVER PARTITION BY mais sans succès.


Merci d'avance à ceux qui prendront le temps d'y jeter un œil.

[escartefigue]

Bonjour,

Vous voulez compter le nombre de contrats, très bien, mais à quoi reconnait on un contrat dans l'une et l'autre table ?
N'y a -t- il pas une colonne numéro de contrat, identifiant contrat ou autre ?
Ou alors faut il considérer que dans chacune des deux tables, un contrat est unique pour une personne identifiée par (insee_code + sexe + date de naissance)

Question subsidiaire : le code insee (s'il s'agit bien du n° de sécu ou équivalent) est normalement unique, du coup inutile d'y ajouter le sexe et la date de naissance pour reconnaitre une personne. Peut être avez vous ajouté ces critères si dans votre base de données, les enfants par exemple ont le n° insee du père ou de la mère ?

Et une remarque : pour obtenir le prix mini, il faut coder min(prix) et non pas mean(prix)

[majajoue]

Bonjour et merci pour votre réponse.

Non il peut y avoir plusieurs contrats de la BASE2 qui vérifient les 4 conditions.
Oui effectivement chaque ligne des deux tables correspond à un contrat unique avec un numéro de contrat associé.

Non le code insee est un code géographique (assez proche du code postale).

En fait, insee_code + sexe + date de naissance est un proxy d'un "Id_client" malheureusement absent de la base de données.

Je le remplace par id_client car ça complique inutilement le sujet :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE Tab AS
 
    SELECT 
 
    BASE1.*,
 
    (SELECT count(*) FROM BASE2 WHERE BASE1.Id_client = BASE2.Id_client
                                     AND BASE2.DATE_DEB <= BASE1.DATE_T <= BASE2.DATE_FIN) as NB_CT,
 
    (SELECT mean(Prix1) FROM BASE2 WHERE BASE1.Id_client = BASE2.Id_client
                                          AND BASE2.DATE_DEB <= BASE1.DATE_T <= BASE2.DATE_FIN) as Prix1Moy,
 
    (SELECT mean(Prix2) FROM BASE2 WHERE BASE1.Id_client = BASE2.Id_client
                                          AND BASE2.DATE_DEB <= BASE1.DATE_T <= BASE2.DATE_FIN) as Prix2Moy
 
 
FROM BASE1;

[escartefigue]

Utilisez une CTE ou une table dérivée
EDIT : oups pardon, j'ai oublié le problème de plage de dates

[tbc92]

J'aurais vu une requête comme celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
CREATE TABLE Tab AS
SELECT  
    BASE1.id_client ,   min(base1.nom) as nom, min(base1.prenom)  as prenom ... ... 
    count(*) as NB_CT , avg( prix1) as Prix1Moy, avg(Prix2) as Prix2Moy
from BASE1, BASE2
where BASE1.id_client = BASE2.id_client 
   and base1.date_T between base2.date_deb and base2.date_fin
group by base1.id_client

1. J'ai remplacé Mean() par avg() ; peut-être que Mean() fait le job, je ne connais pas.
2. J'imagine que dans base1, pour un id_client, on a une seule ligne. Du coup, je récupère min(Nom), mais je pourrais récupérer max(nom), ça donnerait exactement le même résultat. Si dans base1, il peut y avoir plusieurs lignes avec le même id_client, alors faut mieux expliquer l'organisation des données.
3. Si pour un id_client, il n'y a aucune ligne dans base2 avec les contraintes de date, cette requête va "perdre" ce id_client alors que ta requête d'origine ne les perdait pas. Si tu veux garder ces lignes, il faut adapter avec des outer_join.
4. J'ai utilisé la syntaxe where BASE1.id_client = BASE2.id_client ; certains prèfèrent qu'on écrive inner join base2 on ...
5. En terme de performance, ça devrait aller 100 fois ou 1000 fois plus vite que la requête d'origine. Accessoirement, ce serait bien de préciser si tu as un index sur la colonne id_client dans ces 2 tables. Et si tu n'en as pas, il faut peut-être en créer un.

[majajoue]

Ça marche nickel !

Merci bcp