Discussion :

[merfolkfr]

Bonjour à tous,

Je fais un projet sur une base permettant de faire des statistiques sur les accidents routiers.
Pour toute la partie IHM en PHP et xhtml je n'ai pas de soucis. Mon problème vient de certaines requêtes SQL qui sont "moins basiques" que je ne le supposais.

Elles ne sont pas forcément fausses mais peuvent je pense être améliorées.

Mon modèle du domaine est simple : 2 tables : accident et victime et pour un accident j'ai de 1 à x victime(s).

Je vous soumets mes 2 requêtes les plus problématiques.

recherche du nombre de tués en 2 roues par an :

Code :

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

1
2
3
4
5
6
SELECT * 
FROM victime,accident 
WHERE accident.ID = victime.ID  
  AND (accident.ANNEE = '$lastYear' 
    AND (victime.VEHICULE = ('CYCLO' OR 'MOTO') 
    AND( victime.GRAVITE='TUE')))

et
nombre de tués par an :

Code :

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

1
2
3
SELECT SUM(NBR_TUE) AS nbr_tue 
FROM accident 
WHERE ANNEE = '$lastYear'

En remerciant d'avance ceux qui prendront quelques minutes pour se pencher sur mon problème en cette période de vacances.

[Scriuiw]

Mis à part une modélisation bancale et des parenthèses superflues, il n'y a pas grand chose à améliorer vue la simplicité des requêtes

Code :

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

1
2
3
4
5
6
SELECT  * 
FROM  victime,accident 
WHERE  accident.ID = victime.ID  
AND  ANNEE = '$lastYear' 
AND  VEHICULE IN ('CYCLO', 'MOTO')
AND  GRAVITE='TUE'

Le IN ne change rien aux performances, mais améliore très légèrement la lisibilité
Répéter le nom des tables si les champs sont uniques est inutiles

[CinePhil]

Quelques remarques pour commencer...

1) Un code indenté et aéré est plus agréable à lire et à débugguer.

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

3) Il vaut mieux éviter la guerre des étoiles !

4) Inutile d'entourer les valeurs numériques avec des apostrophes !

5) Les parenthèses dans les conditions de jointure ou de restriction sont le plus souvent inutiles, sauf en cas de mélange de AND et de OR.

Venons-en au besoin exprimé :

recherche du nombre de tué en 2 roues par an :

Vous demandez un nombre mais vous sélectionnez des lignes. Inutile !

Code :

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

1
2
3
 
FROM victime,accident 
WHERE accident.ID = victime.ID

Êtes-vous sûr de la condition de jointure ?
Je l'interprête comme "l'identifiant de l'accident est égal à l'identifiant de la victime" !

Fournissez la description de vos tables, ce sera plus facile de vous aider.

Pour rechercher un comptage par an, il faut faire un groupage.

Code :

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

1
2
3
4
5
6
SELECT a.ANNEE, COUNT(*) AS nb_tues
FROM victime v
INNER JOIN accident a ON a.ID = v.ID -- condition de jointure à vérifier !
WHERE v.VEHICULE IN ('CYCLO', 'MOTO')
	AND v.GRAVITE = 'TUE'
GROUP BY a.ANNEE

nombre de tué par an

Même principe que ci-dessus, sans la condition de restriction sur le véhicule :

Code :

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

1
2
3
4
5
SELECT a.ANNEE, COUNT(*) AS nb_tues
FROM victime v
INNER JOIN accident a ON a.ID = v.ID -- condition de jointure à vérifier !
WHERE v.GRAVITE = 'TUE'
GROUP BY a.ANNEE

Vous avez grand besoin de réviser vos connaissances en SQL !
Le site de SQLPro, dont j'ai donné plusieurs liens, vous sera très utile !

[CinePhil]

Mis à part une modélisation bancale et des parenthèses superflues, il n'y a pas grand chose à améliorer vue la simplicité des requêtes

Bien sûr que si ! Voir mon message ci-dessus.
Les requêtes proposées ne répondent pas au besoin exprimé et la première est fausse à cause du OR au lieu du IN !

Répéter le nom des tables si les champs sont uniques est inutiles

Au contraire ! quand il y a plus d'une table dans une requête, il vaut mieux utiliser des alias pour bien spécifier au lecteur d'où viennent les colonnes. Cela rend la requête plus facile à comprendre et à débugguer par la suite.

[merfolkfr]

Merci Cinephil de votre aide,
voici ma structure de la base :

Table accident :

ID varchar(20)
DATE varchar(20)
TIMESTAMP int(25)
JOURSEMAINE varchar(10)
JOUR int(2)
MOIS int(2)
ANNEE int(4)
HEURE int(2)
MINUTE int(2)
SECONDE int(2)
SEMAINE int(2)
GRAVITE varchar(10)
NBR_TUE varchar(3)
NBR_BHO varchar(3)
NBR_BNO varchar(3)
NBR_BTO varchar(3)
COMMUNE varchar(25)
AXE varchar(20)
AXE_PRINCIPAL varchar(20)
NOM_AXE varchar(20)
PR varchar(10)
AGGLOMERATION varchar(20)
CAUSE varchar(30)
IMPL_2R int(3)
IMPL_VL int(3)
IMPL_PL int(3)
IMPL_TC int(3)
IMPL_PIETON int(3)
IMPL_DIV int(3)
GPS_LON varchar(10)
GPS_LAT varchar(10)

Table victime :

INDEX int(10)
ID_VICTIME varchar(15)
GRAVITE varchar(15)
RESPONSABILITE varchar(10)
VEHICULE varchar(10)
ANNEENAISS varchar(4)
AGE varchar(3)
POSITION
SEXE varchar(10)
DOMICILE varchar(15)

la table accident comporte les informations sur la date, le lieu, le nombre de blessé hospitalisé, non hospitalisé, tué et blessé total.

la table victime comporte les informations sur la victime age, sexe... son type de véhicule voiture, camion, moto, cyclo... sa gravité : bléssé...

les requêtes que je nécessite sont :

- nombre de mort en 2 roues (MOTO ou CYCLO dans VEHICULE de la table victime) pour une année a

- nombre de tués pour une année a

- nombre de tués de sexe féminin entre 25 et 40 ans en deux roues pour une année a

avec ces 3 requêtes je peux en déduire toutes les requêtes dont j'ai besoin encore merci

[CinePhil]

S'il n'est pas trop tard, tu peux revoir ton modèle de données !

Il y a des redondances d'informations qui peuvent entraîner des incohérences. Par exemple, au lieu de stocker séparément la date puis jour, mois, année puis semaine et joursemaine, stocke directement un DATETIME puisque tu as aussi l'heure.

La commune devrait être externalisée dans une table des communes pour éviter la saisie multiple de la même commune avec des orthographes différentes.

La gravité devrait aussi être externalisée dans une table de référence des gravités.

Je ne sais pas ce qu'est cette notion d'axe mais il faudrait probablement l'externaliser aussi.

L'agglomération peut être définie via la commune à condition de créer une table des agglomérations et l'associer à la table commune.

Quant à la victime, quelle colonne détermine de quel accident elle a été la victime ? INDEX ?

Ne jamais utiliser de VARCHAR pour des identifiants, ni pour des valeurs purement numériques (que l'on peut utiliser pour des calculs)

le nombre de blessé hospitalisé, non hospitalisé, tué et blessé total.

Tout ceci peut se calculer par requête. Inutile de stocker des valeurs calculables. Ça peut là aussi entraîner des incohérences.

Pour tes requêtes, je t'ai donné le principe avec les miennes. Je ne vais quand même pas faire tout le boulot à ta place !

Si c'est pour un travail professionnel, repasse par la case conception de la BDD. commence par écrire des règles de gestion claires puis dessine un modèle conceptuel de données que tu pourras proposer dans le forum Schéma.

Bref, faut tout refaire !

[merfolkfr]

j'ai d'autres tables dans ma base que j'utilise comme référentiel pour ne pas avoir d'erreur de saisie ou de fautes d'orthographe et afin de pouvoir faire des recherche sur ces critères.

* accident : les accidents
* ref_age : référentiel des tranches d'age
* ref_agglo : référentiel des types d'agglomération
* ref_axe : liste des axes départementaux
* ref_cause : liste des causes d'accidents
* ref_commune : liste des communes du département
* ref_domicile : proximité du domicile de la victime
* ref_nom_axe : nom de l'axe de l'accident
* ref_sexe : sexe de la victime
* ref_unite : unité en charge de l'accident
* user : login paswd d'accès
* victime victime de l'accident

Index c'est une indexation de la table que j'utilise comme clé primaire

ID_VICTIME de la table victime est l'id de l'accident que j'utilise pour relier les victimes à l'accident

ID de la table accident est un numéro unique par accident utilisé en clé primaire.

le nombre de blessé... peut être calculé en sql mes ce sont justement mes lacunes en sql qui me font surchargé les tables quitte à avoir des incohérences.

j'ai fait un grand nombre de référentiel pour pallier à ces différents problèmes. Par manque de connaissances et d'expériences je pense que je ne les utilise pas à bon escient (surout en saisie dans des formulaires).

Ce travail je l'ai en plus de mon travail "ordinaire" et j'y passe un temps "libre" considérable.

Je ne demande pas du tout que l'on fasse mon travail loin de moi cette idée, le plus gros de mon travail est fini et marche parfaitement, et pour un développeur JavaJ2e faire une interface en PHP c'était pas gagné d'avance : )

Mon dernier soucis ce sont ces dernières requêtes qui ne me remonte pas le résultat escompté. Là encore ce n'est pas par manque de travail mais plus par manque de connaissances.
Ayant peu de connaissances en sql j'ai parcouru les forums ou je me suis "instruit" et je fait toutes les requêtes "simples".

Je parcours très souvent ces forums à la recherche de réponse que d'ailleurs je trouve le plus souvent. Ce qui me permet et de m'instruire et de comprendre donc d'avancer.

Mais là pour une fois, la pression hiérarchique faisant je n'ai pas le temps de faire de recherches approfondies pour mon problème particulier j'ai donc posté pour que l'on m'aide a faire une requête qui me servira à faire toute les autres.

Aux vues de mes tables, cette requête me renverrais bien le nombre le tué en deux roues pas année?

Code :

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

1
2
3
4
5
6
 
SELECT a.ANNEE, COUNT(*) AS nb_tues
FROM victime v
INNER JOIN accident a ON a.ID = v.ID_VICTIME -- condition de jointure à vérifier !
WHERE v.GRAVITE = 'TUE'
GROUP BY a.ANNEE

Je ne demande qu'à apprendre
En vous remerciant pour le temps que vous m'aurez accordé.

[CinePhil]

Tu n'as pas répondu à mon interrogation :

Quant à la victime, quelle colonne détermine de quel accident elle a été la victime ? INDEX ?

Du coup, je ne peux guère répondre avec assurance à la tienne mais j'ai encore un gros doute sur ta condition de jointure puisque tu sembles faire une égalité entre l'identifiant de l'accident (a.ID) et l'identifiant de la victime (v.ID_VICTIME) !

En dehors de ce gros doute, la requête, comme dit précédemment, répondra au premier besoin exprimé :

recherche du nombre de tué en 2 roues par an

[lola06]

@CinePhil : il te répond bien à la question :

ID_VICTIME de la table victime est l'id de l'accident que j'utilise pour relier les victimes à l'accident

Cette requête renvoi le nombre de tué par années.

Code :

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

1
2
3
4
5
6
 
SELECT a.ANNEE, COUNT(*) AS nb_tues
FROM victime v
INNER JOIN accident a ON a.ID = v.ID_VICTIME
WHERE v.GRAVITE = 'TUE'
GROUP BY a.ANNEE

Si vous voulez pour les deux roues il vous faut rajouter une condition de jointure :

Code :

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

v.VEHICULE IN ('CYCLO', 'MOTO')

[hanamachi]

oui avec la dernière requete tu as toutes les années avec a coté le nombre de tué mais cette fois il n'y pas de condition sur le vehicule .

[CinePhil]

@CinePhil : il te répond bien à la question :

Au temps pour moi ! J'étais passé à côté de cette bizarrerie consistant à identifier un accident en l'appelant ID_VICTIME !

[lola06]

Au temps pour moi ! J'étais passé à côté de cette bizarrerie consistant à identifier un accident en l'appelant ID_VICTIME !

J'avoue il a fallut que je le lise plusieurs fois.

@merfolkfr : ne pourrais-tu pas renommer cette colonne ID_ACCIDENT pour plus de clarté ?

[CinePhil]

@merfolkfr : ne pourrais-tu pas renommer cette colonne ID_ACCIDENT pour plus de clarté ?

Comme j'ai dit plus haut, : "Faut tout refaire !"

Mais apparemment, c'est un peu trop tard.
J'espère qu'il tiendra compte de mes conseils dans un prochain développement.

[merfolkfr]

Je vous remercie tous pour votre temps, votre gentillesse et surtout votre patience.
Si j'ai une autre base a faire je viendrais vous soumettre pas à pas les différentes phases pour apprendre à travailler autrement que comme un cochon.