Requête SQL et date

Bonjour,

Je sèche une peu sur une requete SQL (comme d'habitude quand il s'agit de trier des dates ...) :calim2:


J'ai deux tables avec, entre autre :

consommateur
- id_consommateur
- lieu_consommateur
...

ref_commande
-id_commande
-date_commande (au format AAAA/MM/JJ)
-id_consommateur
...

Les deux tables sont reliées par id_consommateur.

Je dois sélectionner tout les consommateurs n'ayant pas passé de commande depuis 8 mois.

J'ai essayé des requête imbriquée totalement indécente, j'ai abandonné.

Avez vous une idée ?

Merci d'avance !

Bonjour,
En gros tu veux tous les clients pour lesquelle il n'existe pas de commande postérieur à (la date du jour moins 8 mois).
Tu peux passer par une jointure externe, par exemple:

Code:

---

1 2 3 4 5

select client.* from client left outer join commande on client.id = commande.idclient and commande.datesaisie > '2012-06-01' where commande.id is null

---

J'ai mis la date en dur, car je pense qu'il est plus facile de la calculer dans le code qu'en SQL (enfin surtout je nesuis pas doué pour les date en SQL :mrgreen: ).

Tatayo.

Une date ne doit pas être enregistrée comme chaine de caractères.

Il existe un type DATE en SQL ; il est fait pour ça. Et il permet de faire facilement des calculs sur les dates tels que ajouter un certain nombre de mois.

Mon champs date est bien au format date dans ma table.

Tatayo, je vais tester, merci :)

Cela ne donne rien :(

Ça cherche sans succès, j'ai du stopper la requête au bout de 15 minutes.
J'ai testé avec une date très récente, au cas où le résultat était trop long, mais c'est pareil.

J'ai aussi testé de cette façon :

Code:

---

1 2 3 4 5

SELECT clients.* FROM clients WHERE `client_id` NOT IN ( SELECT `client_id` FROM cmd_ref WHERE cmd_ref.`cmd_date` > '2013-01-01')

---

Mais ce n'est pas mieux.

D'autres suggestions serait bienvenue :)

Bonjour,

Vous pouvez essayer avec un NOT EXISTS :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT C.* FROM clients C WHERE NOT EXISTS( SELECT 1 FROM cmd_ref R WHERE R.client_id = C.client_id AND cmd_date > '2013-01-01' )

---

Mais il vous faudra surement un index sur la date pour accélérer la requete

Si la requête est annulée avant la fin, normal qu'elle ne retourne rien :roll:
Est-ce qu'on peut avoir le plan d'execution ?

Tatayo.

Bonjour,

Cette requête à l'air de fonctionner :

Code:

---

1 2 3 4 5 6 7 8

SELECT C.* FROM clients C WHERE NOT EXISTS( SELECT 1 FROM cmd_ref R WHERE R.client_id = C.client_id AND cmd_date > '2013-01-01' )

---

Mais effectivement, elle est très longue à exécuter.
Et si j'ai le malheur de mettre une date plus ancienne, le script échoue car trop long. (j'ai mis max_execution_time à 300)

Pour les index, j'en ai crée un du coup, mais je ne sais pas si je m'y prend bien :

CREATE INDEX tri_date
ON cmd_ref
(cmd_date DESC])

?

Crée l'index sur le couple (client_id, cmd_date desc)

J'ai testé avec ça, ça à l'air de fonctionner, mais est ce que ça vous parait correct ?

Code:

---

1 2 3 4 5 6

SELECT DISTINCT C.`nom_societe`, R.`no_commande`, R.`commande_date`, C.`nom`, C.`prenom` FROM commande_ref R, clients C WHERE C.`client_id` = R.`client_id` AND R.`commande_date` < '2012-01-01' GROUP BY C.`nom_societe` ORDER BY R.`commande_date`

---

Citation:

---

Envoyé par Groom

mais est ce que ça vous parait correct ?

---

Absolument pas :aie:

Sur le fond d'abord :
vous recherchez maintenant les clients et leurs commandes datant d'avant le 1er janvier 2012... ça n'a plus rien à voir avec le besoin initial...

Sur la forme maintenant :
- Les jointures s’écrivent maintenant avec INNER JOIN ... ON
- Vous avez un GROUP BY, mais certaines colonnes dans votre select ne sont pas dans des fonctions d'agregat (ni en dépendance fonctionnelle de votre colonne de regroupement)

De plus je ne comprend ni le distinct, ni le group by, et encore moins les deux...

Pour la date c'est normal, elle sera amené à changer, du coup je test avec des dates différentes.

Pour les jointures, heuuu :/ d'accord, je ne savais pas. La requête que vous avez proposé à le même type de jointure

Code:

---

1 2 3 4 5 6 7 8

SELECT C.* FROM clients C WHERE NOT EXISTS( SELECT 1 FROM cmd_ref R WHERE R.client_id = C.client_id AND cmd_date > '2013-01-01' )

---

Ce n'est pas le même cas ?

Pour le disctinct, effectivement, je l'avais mis pour tester ... je tatonne.

Enfin, pour le GROUPE BY je voulais les rassembler par nom de société
Et les trier par date de commende.

Du coup, je sèche à nouveau :(

Reprenons...

Citation:

---

Je dois sélectionner tout les consommateurs n'ayant pas passé de commande depuis 8 mois.

---

Voici la requête qui répond au besoin, vu que vous utilisez MySQL :

Code:

---

1 2 3 4 5 6 7 8

SELECT -- les colonnes nécessaires et pas étoile ! FROM clients c WHERE NOT EXISTS( SELECT 1 FROM cmd_ref r WHERE R.client_id = C.client_id AND cmd_date > DATE_SUB(CURRENT_DATE, INTERVAL 8 MONTH) )

---

Citation:

---

Pour les jointures, heuuu :/ d'accord, je ne savais pas. La requête que vous avez proposé à le même type de jointure.
Ce n'est pas le même cas ?

---

Ici il ne s'agit pas d'une jointure mais d'une requête correlée.
En français, cette requête signifie : "Sélectionner les clients pour lesquels il n'existe pas de commande dont la date est supérieure à aujourd'hui moins 8 mois."

Citation:

---

Mais effectivement, elle est très longue à exécuter.
Et si j'ai le malheur de mettre une date plus ancienne, le script échoue car trop long. (j'ai mis max_execution_time à 300)

---

Combien de millions de lignes dans les tables pour que ce soit aussi long ?

Donnez-nous le résultat de EXPLAIN -- la requête donnée plus haut.

Et tant que vous y êtes,, donnez-nous aussi le résultat complet de SHOW CREATE TABLE -- la table pour les deux tables.

Merci pour ta réponse !

Voilà ma table client

Code:

---

1 2 3 4 5 6 7 8

CREATE TABLE `clients` ( `client_id` int(11) NOT NULL AUTO_INCREMENT, `lieu_id` int(2) NOT NULL DEFAULT '1', `nom_client` varchar(255) DEFAULT '', `prenom_client` varchar(255) DEFAULT '', PRIMARY KEY (`client_id`), KEY `lieu_id` (`lieu_id`), ENGINE=MyISAM AUTO_INCREMENT=11123 DEFAULT CHARSET=utf8

---

Ma table commande

Code:

---

1 2 3 4 5 6 7 8 9 10

CREATE TABLE `commande_ref` ( `cmd_no` int(11) NOT NULL AUTO_INCREMENT, `lieu_id` int(2) NOT NULL DEFAULT '1', `cmd_date` date NOT NULL, `cmd_heure` time NOT NULL, `client_id` int(11) NOT NULL, PRIMARY KEY (`cmd_no`), KEY `lieu_id` (`lieu_id`), KEY `cmd_date` (`cmd_date`), KEY `tri_date` (`cmd_date`)) ENGINE=MyISAM AUTO_INCREMENT=25822 DEFAULT CHARSET=utf8

---

Le explain :

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY C ALL NULL NULL NULL NULL 11083 Using where
2 DEPENDENT SUBQUERY R range cmd_date,tri_date cmd_date 3 NULL 5817 Using where

Il y a des trucs bizarres dans tes tables !
KEY `site_id` (`site_id`), dans les deux tables alors que tu n'as pas de colonne site_id ! 8O

Code:

---

1 2	KEY `cmd_date` (`cmd_date`), KEY `tri_date` (`cmd_date`)

---

Inutile de créer deux index identiques !

Par contre, comme c'est fonctionnellement une clé étrangère, il faudrait un index sur client_id dans la table commande_ref

Ensuite, dans le résultat de l'EXPLAIN, je trouve bizarre qu'il n'utilise pas d'index sur la table des clients.

Tu peux nous donner la requête exacte que tu as soumise à l'explain ?

Ce n'est pas "site_id" mais "lieu_id", j'ai renommé tout à l'heure. :)

Voilà la requête explain :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

EXPLAIN SELECT , C.`nom_client` , C.`prenom_client` FROM clients C WHERE NOT EXISTS (   SELECT 1 FROM commande_ref R WHERE R.client_id = C.client_id AND cmd_date > DATE_SUB( CURRENT_DATE, INTERVAL 8 MONTH ) )

---

Citation:

---

Code:

---

SELECT ,

---

---

Euh... m'étonnerait que ça fonctionne ! :aie:

Comme dit tout à l'heure :

Citation:

---

Envoyé par CinéPhil

Par contre, comme c'est fonctionnellement une clé étrangère, il faudrait un index sur client_id dans la table commande_ref

---

Et comme tes tables sont petites, la réponse devrait arriver en moins d'une seconde.

La requête du explain fonctionne et ne rame pas 3 jours (je me suis juste trompée en recopiant, pour la virgule) lorsque l'on crée l'index. :) merci !

Dernière petite demande cependant !

Je voudrais afficher dans mon résultat l'id et la date de la commande correspondante.

Mais évidemment, lorsque j'ajoute :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

SELECT C.`nom_client` , C.`prenom_client`, R.`cmd_date` FROM clients C, commande_ref R WHERE NOT EXISTS (   SELECT 1 FROM commande_ref R WHERE R.client_id = C.client_id AND cmd_date > DATE_SUB( CURRENT_DATE, INTERVAL 18 MONTH ) )

---

cela ne marche plus.


Et aussi, pouvez vous m'expliquer pourquoi le requête suivante ne fonctionne pas ? Que je ne fasse pas la même erreur la prochaine fois :

Code:

---

1 2 3 4 5 6

SELECT C.`nom_societe`, R.`no_commande`, R.`commande_date`, C.`nom`, C.`prenom` FROM commande_ref R, clients C WHERE C.`client_id` = R.`client_id` AND R.`commande_date` < '2012-01-01' GROUP BY C.`nom_societe` ORDER BY R.`commande_date`

---

Merci !

Citation:

---

Envoyé par Groom

Je voudrais afficher dans mon résultat l'id et la date de la commande correspondante.

---

Quelle commande ?
Tu demandais les clients n'ayant pas passé de commande depuis 8 mois. Tu veux la dernière commande qu'il ont passée ?

Citation:

---

Mais évidemment, lorsque j'ajoute :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

SELECT C.`nom_client` , C.`prenom_client`, R.`cmd_date` FROM clients C, commande_ref R WHERE NOT EXISTS (   SELECT 1 FROM commande_ref R WHERE R.client_id = C.client_id AND cmd_date > DATE_SUB( CURRENT_DATE, INTERVAL 18 MONTH ) )

---

cela ne marche plus.

---

Les jointures s'écrivent depuis 20 ans avec l'opérateur JOIN ; il serait temps de s'y mettre !

Ceci : FROM clients C, commande_ref R est un produit cartésien entre les deux tables puisqu'il n'y a pas de condition de jointure dans le WHERE (ancienne syntaxe d'il y a 20 ans) !

En plus, comme tu utilises le même alias dans le produit cartésien et dans la sous-requête NOT EXISTS, le SGBD doit te renvoyer une belle erreur d'ambiguïté !

Bref, ta requête est totalement fausse ! :aie:

Exprime clairement ce que tu veux obtenir.

Citation:

---

Et aussi, pouvez vous m'expliquer pourquoi le requête suivante ne fonctionne pas ? Que je ne fasse pas la même erreur la prochaine fois :

Code:

---

1 2 3 4 5 6

SELECT C.`nom_societe`, R.`no_commande`, R.`commande_date`, C.`nom`, C.`prenom` FROM commande_ref R, clients C WHERE C.`client_id` = R.`client_id` AND R.`commande_date` < '2012-01-01' GROUP BY C.`nom_societe` ORDER BY R.`commande_date`

---

---

Dans cette requête, il y a cette fois une jointure d'il y a 20 ans ( FROM des_tables WHERE condition_de_jointure ).

Par contre, c'est le GROUP BY qui est faux !
Toutes les colonnes du SELECT ne faisant pas l'objet d'une fonction de groupage doivent figurer dans le GROUP BY sous peine de voir des valeurs aléatoires pour les colonnes manquantes.

Les jointures, je note, pas de problème !
Mais c'est malheureusement nos professeurs qui ne se mettent pas à jour. C'est dommage.

Pour ma requête je voulais bien les clients les clients n'ayant pas passé de commande depuis 8 mois. J'essayais juste d'afficher à côté, en plus, le numéro de la dernière commande correspondant au client. Pour me simplifier les choses.