MySQL Comptage par semaine sans sous-requête - Page 1

Hello,

Je cherche à produire un jeu de données pour générer des graphiques, en l'occurrence un histogramme qui me représente le nombre d'objets répondant à un critère, par semaine. Pour simplifier le post, je vais supposer qu'il s'agit de commandes d'articles sur Internet, et que je veux mesurer le nombre de commandes envoyées par semaine.

J'utilise pour ça deux tables :

- Une table "commandes" qui contient un enregistrement par commande effectuée,
- Une table "etats" qui m'indique les états successifs de chaque commande (passée, payée, expédiée, livrée, etc) avec les dates correspondantes.

Commandes : ID

Nom
1	Saucisse
2	Sabre laser
3	Planche à découper

Etats : ID

Commande	Etat	Date
1	1	passée	01/01/10
2	1	payée	04/01/10
3	1	expédiée	05/01/10
4	2	passée	04/01/10
5	2	payée	07/01/10
6	3	passée	05/01/10
7	3	payée	08/01/10
6	3	expédiée	10/01/10
6	3	livrée	15/01/10

Avec ces données, je voudrais compter le nombre de commandes expédiées par semaines sur un intervalle donné, c'est à dire obtenir un tableau de cette forme :
Semaine

Nbr commandes expédiées
S01	1
S02	1
S03	0
S04	0

Le problème (parcequ'il y a *toujours* un problème !) c'est que je n'ai quasiment aucune liberté sur la requête SQL. En raison de contraintes diverses, je suis obligé de suivre un modèle du style "SELECT [champs] FROM commandes, etats WHERE commandes.id = etats.commande AND [filtres] GROUP BY [groupes]", dans lequel je peux mettre ce que je veux à la place de "[champs]", "[filtres]" et "[groupes]" (à condition que ce soit des expressions SQL valides bien sûr). Du coup j'ai tenté un truc de ce style :
SELECT DATE_FORMAT(commandes.date, '%Y S%U') semaine, SUM(CASE WHEN etats.etat = 'expédiée' THEN 1 ELSE 0 END) nb_expediees FROM commandes, etats WHERE commandes.id = etats.commande AND etats.date >= '01/01/10' AND etats.date < '01/02/10' GROUP BY semaine ORDER BY semaine

Mais il y a deux soucis :

- Si une commande est expédiée plusieurs fois (c'est envisageable), elle sera comptabilisée plusieurs fois, alors que je ne voudrais jamais compter deux fois une même commande (donc prendre soit la première expédition, soit la dernière, mais pas les deux) ;
- Si aucune commande n'est expédiée pendant une semaine, la ligne correspondant à cette semaine ne sera pas présente dans le résultat (par exemple si on a aucune expédition en semaine 03, le tableau va passer directement de S02 à S04, d'où un trou dans le graphique).

S'il est impossible de s'en sortir, je vais devoir changer complètement de fusil d'épaule. Mais avant de faire ça, j'aurais voulu savoir si par hasard il n'y avait pas un moyen de s'en sortir plus facilement, parceque ça me ferait quand même gagner une bonne journée de boulot ^^

Merci

Et tu ne peux pas changer le format de la table Etat ? Je trouve ça bizarre d’avoir plusieurs lignes pour repésenter l’état d’une même commande.

Elle ne représente pas l'état actuel (en réalité la table "commande" contient directement l'état actuel), mais l'historique de tous les états. C'est indispensable pour pouvoir sortir après coup des informations sur les états qui ont été traversés entre telle et telle date.

Je pense que pour le 2e problème il n’y a pas de solution en SQL.
Pour le 1er, tu peux ajouter un attribut expediée à la table Commande, qui indique si la commande a déjà été expédiée au moins une fois (donc dans le where de la requête tu vérifies que cet attribut ne vaut pas 0, et tu peux virer le case du select).

et en faisant un group by sur "MONTH(etats.date)" ?
Genre : select tes colonnes from tes tables where ton refto broup by MONTH(etats.date) order by etats.date ?

Sasume (./4) :
Je pense que pour le 2e problème il n’y a pas de solution en SQL.

Pour le 1er, tu peux ajouter un attribut expediée à la table Commande, qui indique si la commande a déjà été expédiée au moins une fois (donc dans le where de la requête tu vérifies que cet attribut ne vaut pas 0, et tu peux virer le case du select).

Ah oui, mais je dois modifier la structure, sur laquelle je n'ai pas la main :/ (j'ai oublié de le préciser dans le post initial).

vince (./5) :
et en faisant un group by sur "MONTH(etats.date)" ? Genre : select tes colonnes from tes tables where ton refto broup by MONTH(etats.date) order by etats.date ?

Bah c'est exactement ce que j'ai fait, non ? (sauf que je l'ai fait par semaine et non pas par mois)

erf ouais, j'chuis mal réveillé moi...

Aïe, si tu ne peux pas modifier le modèle et que tu ne peux pas non plus faire de sous-requêtes, ça complique les choses !

Je peux faire des sous-requêtes, mais en me passant totalement d'une partie de l'API (en les exécutant "à la main" sur la base, ce qui n'est pas terrible).

S'il n'y a pas d'autre solution, c'est vers cette direction que je vais devoir partir mais je voulais être sûr avant de ne pas avoir loupé quelque chose d'évident qui me permettrait de m'en sortir de façon plus propre

Pour ne pas compter les commandes en double, tu peux utiliser le mot clef UNIQUE, mais bon tu va perdre l'info si c'est envoyé en plusieurs semaines (cf http://sql.1keydata.com/fr/sql-constraint.php )

on peut utiliser UNIQUE dans une requête de sélection ? (parceque ta page décrit la contrainte UNIQUE sur une table, que je ne peux pas utiliser puisque je ne peux pas modifier le schéma)

bon sinon vous me confirmez qu'on ne peut pas faire en une requête simple, donc je pense faire un select "approchant" puis retraiter les résultats en code derrière, tant pis :/

merci pour votre aide

heu, pourquoi tu es contrain à cette forme de requête ? à cause d'un l'ORM particulier ?

car dans certains cas (du style pour des requetes de ce type) il vaut mieux parfois ne pas s'embêter et ne pas utiliser l'orm, mais juste le connecteur. car sinon on se fait chier pour rien

C'est à mi-chemin entre les deux, je peux effectuer une requête en ne spécifiant que les champs à sélectionner et les filtres. Pour une requête plus complexe, je vais devoir taper directement dans la table, mais j'aurais préféré éviter puisque ça traverse une couche.

le pb c'est que dans certains cas, il vaut mieux la traverser, cette couche, car sinon ca complique bcp trp inutilement. et surtout, vu le résultat que tu veux obtenir, un orm ne sera pas vraiment utile, car aucun objet ne pourra correspondre à ton résultat. Perso, dans des cas comme celui-lo, j'hésite pas à contourner. en plus tu gagnes généralement pas mal en perfs.

Zephyr (./11) :
on peut utiliser UNIQUE dans une requête de sélection ? (parceque ta page décrit la contrainte UNIQUE sur une table, que je ne peux pas utiliser puisque je ne peux pas modifier le schéma)

Oui *normalement*

http://sqlpro.developpez.com/cours/sqlaz/sousrequetes/#L1.5.2

Au pire, dinsctinct pourrais faire l'affaire :

http://www.webdevelopersnotes.com/tutorials/sql/online_mysql_guide_the_distinct_keyword.php3

select title from employee_data;

+----------------------------+
| title                      |
+----------------------------+
| CEO                        |
| Senior Programmer          |
| Senior Programmer          |
| Web Designer               |
| Web Designer               |
| Programmer                 |
| Programmer                 |
| Programmer                 |
| Programmer                 |
| Multimedia Programmer      |
| Multimedia Programmer      |
| Multimedia Programmer      |
| Senior Web Designer        |
| System Administrator       |
| System Administrator       |
| Senior Marketing Executive |
| Marketing Executive        |
| Marketing Executive        |
| Marketing Executive        |
| Customer Service Manager   |
| Finance Manager            |
+----------------------------+
21 rows in set (0.00 sec)

select DISTINCT title from employee_data;

+----------------------------+
| title                      |
+----------------------------+
| CEO                        |
| Customer Service Manager   |
| Finance Manager            |
| Marketing Executive        |
| Multimedia Programmer      |
| Programmer                 |
| Senior Marketing Executive |
| Senior Programmer          |
| Senior Web Designer        |
| System Administrator       |
| Web Designer               |
+----------------------------+
11 rows in set (0.00 sec)

Spipu (./14) :
le pb c'est que dans certains cas, il vaut mieux la traverser, cette couche, car sinon ca complique bcp trp inutilement. et surtout, vu le résultat que tu veux obtenir, un orm ne sera pas vraiment utile, car aucun objet ne pourra correspondre à ton résultat. Perso, dans des cas comme celui-lo, j'hésite pas à contourner. en plus tu gagnes généralement pas mal en perfs.

mais apres ca depend des specs. Et au pire les specs, c'est aussi fait pour etre modifiees.