[OPTIMISATION SQL] Comment enlever une sous-requête

[SuperCed]

J'ai 1 table de ce type commandes:
id_commande (primary key) | id_client | date | total

600 | 111 | 2007-01-04 | 50
700 | 111 | 2008-01-03 | 60
888 | 111 | 2009-01-02 | 40
601 | 112 | 2007-01-04 | 30
801 | 112 | 2009-01-04 | 10
802 | 113 | 2009-04-04 | 100

Je souhaite avoir le total des commandes de 2009 pour les clients ayant déjà commandé avant 2009. Je désire aussi le nombre de commande du même type, c'est à dire de 2009 mais avec une personne qui a déjà commandé avant 2009. Je veux aussi le nombre de client de la même façon donc qui a commandé en 2009 ET avant.

Je fais donc une requête de ce type : (requête 1)
SELECT SUM(total) AS total, COUNT(DISTINCT id_commande) AS nb_cmd, COUNT(DISTINCT id_commande) AS nb_cmd
FROM commandes
WHERE date >= '2009-01-01'
AND id_client IN (
SELECT DISTINCT id_client
FROM commandes
WHERE date < '2009-01-01')



Ceci fonctionne, je me demandais s'il existait un moyen de faire la même chose avec une seule requête. Résultat : 40+10 = 50

J'ai essayé ça : (requête 2)
SELECT SUM(total) AS total, COUNT(DISTINCT id_commande) AS nb_cmd, COUNT(DISTINCT id_commande) AS nb_cmd
FROM commandes c1
JOIN commandes c2
ON c1.id_client=c2.id_client AND
c1.date >= '2009-01-01'
AND c2.date < '2009-01-01'

Mais le total est FAUX car la commande 888 est comptée 2 fois! 40+40+10
Je pense qu'avec un GROUP BY, ce sera le même problème.

Est-ce possible de réduire la requête 1 qui comprend une sous requête en UNE SEULE requête (sans sous requête)?

Merci

 

[grumff]

Le IN ça mets les résultats en cache en général, avec un table trop grosse c'est un coup à avoir des problèmes... Privilégie un "exist" plutôt.
Ensuite imbriquer ou pas les requêtes, c'est un peu du détail, en général ça change pas grand chose au plan d'exécution, c'est lui qu'il faut vérifier plus que l'allure de ta requête, tant qu'elle est lisible.

 

[SuperCed]

Le IN ça mets les résultats en cache en général, avec un table trop grosse c'est un coup à avoir des problèmes... Privilégie un "exist" plutôt.
Ensuite imbriquer ou pas les requêtes, c'est un peu du détail, en général ça change pas grand chose au plan d'exécution, c'est lui qu'il faut vérifier plus que l'allure de ta requête, tant qu'elle est lisible.

Comment tu ferais ça avec un "exist"?

 

[macboy]

SuperCed : c'est quoi le SGDB que tu utilises car en fonction des SGDB les fonctions ne sont pas les mêmes .

en sql server je ferai ça comme ça

SELECT SUM(C.total) AS total, COUNT(DISTINCT C.id_commande) AS nb_cmd, COUNT(DISTINCT C.id_commande) AS nb_cmd
 FROM commandes C
 WHERE 
C.date >= '2009-01-01'
 AND 
EXISTS  (SELECT null 
           FROM commandes CC 
           WHERE date < '2009-01-01' and C.id_commande =CC.id_commande )

 

[SuperCed]

SuperCed : c'est quoi le SGDB que tu utilises car en fonction des SGDB les fonctions ne sont pas les mêmes .

en sql server je ferai ça comme ça

SELECT SUM(C.total) AS total, COUNT(DISTINCT C.id_commande) AS nb_cmd, COUNT(DISTINCT C.id_commande) AS nb_cmd
 FROM commandes C
 WHERE 
C.date >= '2009-01-01'
 AND 
EXISTS  (SELECT null 
           FROM commandes CC 
           WHERE date < '2009-01-01' and C.id_commande =CC.id_commande )

MySQL

 

[macboy]

Peux tu préciser quelle version de MySql : je ne connais pas mais d'après ce que j'ai pu lire les dernières versions ont fait un bon en avant concernant les fonctions !!

 

[SuperCed]

Peux tu préciser quelle version de MySql : je ne connais pas mais d'après ce que j'ai pu lire les dernières versions ont fait un bon en avant concernant les fonctions !!

version
5.0.32

 

[grumff]

SuperCed : c'est quoi le SGDB que tu utilises car en fonction des SGDB les fonctions ne sont pas les mêmes .

en sql server je ferai ça comme ça

SELECT SUM(C.total) AS total, COUNT(DISTINCT C.id_commande) AS nb_cmd, COUNT(DISTINCT C.id_commande) AS nb_cmd
 FROM commandes C
 WHERE 
C.date >= '2009-01-01'
 AND 
EXISTS  (SELECT null 
           FROM commandes CC 
           WHERE date < '2009-01-01' and C.id_commande =CC.id_commande )

Vu la requête ça devrait passer dans tous les sgbd de toutes façons, y'a rien d'exotique, en particulier pour mysql et oracle y'a pas lieu de s'inquiéter.