Problème d'efficacité sur une requête

Bonjour à tous 8O,

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:

---

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.

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) ;)

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:

---

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;

---

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

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

Code:

---

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.

Ça marche nickel !

Merci bcp :)