Discussion :

[Patrick_38]

Bonjour à tous,

J'ai une BDD Mysql contenant 3 tables : Joueurs, Trajets et Jeux.
La description des tables est en pièce jointe.

Je souhaiterai obtenir un comptage du nombre de jeux pour chaque joueur et pour chaque type de trajet, par jour, même si un joueur n'a pas effectué tous les types de trajets pour cette journée.
Ma table Joueurs contient 6 joueurs; ma table Trajets contient 6 types de trajets. Ma requête doit donc retourner 6 x 6 = 36 lignes.
La requête suivante sort un comptage uniquement pour les types de trajets qui ont été effectués dans la journée.

Code :

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

select A.Joueur, A.NbTrajet, count(*) from Jeux A left join Trajets B on A.NbTrajet = B.NBTrajet where Date='2022-05-10' group by A.Joueur, A.NbTrajet order by A.Joueur;

Pourriez-vous m'aider svp ?

Merci par avance.

Cdt
Patrick

[escartefigue]

bonjour,

Si on veut toutes les dates d'une période (par exemple d'une année), même celles où certains joueurs (voir aucun joueur) n'a joué, alors il faut créer une table calendrier contenant toutes les dates correspondantes et faire une requête sur cette table en interne (table INNER) et les autres en externe (OUTER JOIN)

Pour le reste, le modèle de données est très mal fait : dans la table "jeux", on trouve une colonne foreign key qui n'est pas primary key d'une autre table
C'est la colonne "idtrajet" qu'on devrait trouver dans jeux et non pas "nbtrajet".

De plus, il semble que cette table "jeux" soit une table associative entre "joueurs" et "trajets". Si c'est bien le cas, cette table ne devrait pas avoir d'identifiant propre (idjeu), sa PK devrait être composé des PK des tables "joueurs" et "trajets"

Egalement, la présence de 10 colonnes "durée1" à "durée10" est suspecte, le plus souvent, ce type de colonnes répétées est une erreur de modélisation
Seul cas où cette modélisation est correcte : il y a toujours obligatoirement 10 et seulement 10 durées, et ça ne changera pas.
Et si la colonne "durée totale" représente la somme des 10 autres, alors c'est une redondance à supprimer

Point supplémentaire "date" est un mot réservé SQL, à éviter comme nom d'objet (table, colonne...), car ça complique inutilement les requêtes (délimiteurs obligatoires) et complique les analyses.

Enfin, l'usage est de nommer les tables au singulier, par exemple "joueur" plutôt que "joueurs", il est évident que la table contiendra plusieurs occurrences de joueurs

Tout ceci étant dit, la requête suivante fait le comptage du nombre de trajets par date de chaque joueur :

Code SQL :

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

1
2
3
4
5
6
7
8
9
select JE.dttrajet
     , JO.prenom
     , count(distinct JE.idtrajet)
from jeux as JE     
left join joueurs as JO
   on JO.idjoueur=JE.idjoueur
group by JE.dttrajet
       , JO.prenom   
;

EDIT : j'oubliais, il est demandé de compter le nombre de "types de trajets", mais cette notion n'apparait pas dans les tables, c'est pourquoi j'ai compté les identifiants de trajets dans la requête ci-dessus. Merci d'indiquer comment on identifie un "type de trajet"

[Patrick_38]

Bonjour,

Merci pour votre réponse et vos remarques pertinentes.
Je n'ai peut-être pas été suffisamment clair dans mes explications.

Pour le "type de trajets", il s'agit de NbTrajet (les valeurs possibles sont 1, 2, 3, 4, 5 et 10).
Un "jeu" est la réalisation d'un type de trajets (donc 1, 2, ... ou 10 trajets). La durée de chaque trajet étant chronométrée.

En fait, la requête ne répond pas complètement à mon besoin. Je voudrais pour l'ensemble des joueurs et des types de trajets, le nombre de jeux effectués (qui peut valoir 0 ou tout autre valeur), pour une date donnée.
Est-ce possible ?

PS : je reconstruirai le modèle de données en suivant vos conseils.

Cdt
Patrick

[escartefigue]

Il est peu utile (sauf à titre didactique) de mettre au point la requête puisque le modèle de données doit être revu.

Pour votre besoin, il faut donc comme je le suggérai plus haut créer une table calendrier avec toutes les dates de la période qui vous intéresse.
Un moyen simple de créer ce type de tables est d'utiliser une requête récursive si votre version MariaDB vous le permet.

Voici la création d'un jeu d'essai (avec les tables créées à minima, je n'ai pas créé la table "trajets" inutile ici) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
create table Calend
      (  CAdate   date not null)
;
insert into Calend (CAdate)
values ('2021-01-01'), ('2021-01-02'), ('2021-01-03')
     , ('2021-01-04'), ('2021-01-05'), ('2021-01-06')
     , ('2021-01-07'), ('2021-01-08'), ('2021-01-09')
     , ('2021-01-10'), ('2021-01-11'), ('2021-01-12')     
;
create table Joueurs
      (  IDJoueur      integer auto_increment  primary key
       , PrenomJoueur  varchar(50)
      )
;
insert into Joueurs(PrenomJoueur)
values ('Benoit'), ('Esmeralda'), ('Mouloud'), ('Sergeï')
;
create table Jeux
      (  IDjeu        integer auto_increment  primary key
       , IDjoueur     integer      not null
       , NbTrajet     smallint     not null
       , Jdate        date         not null
      )
;
insert into Jeux (IDjoueur, NbTrajet, Jdate)
values (01, 01, '2021-01-02')
     , (01, 10, '2021-01-02')
     , (01, 03, '2021-01-10')
     , (01, 04, '2021-01-12')
     , (02, 02, '2021-01-03')
     , (02, 03, '2021-01-04')
     , (02, 05, '2021-01-04')
     , (02, 06, '2021-01-06')
     , (03, 01, '2021-01-06')
     , (03, 03, '2021-01-07')
     , (03, 03, '2021-01-09')
     , (04, 04, '2021-01-02')
     , (04, 10, '2021-01-06')
;

La requête :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
   select CA.CAdate
        , JO.Prenom
        , count(JE.NbTrajet)
   from Calend as CA
   left join Jeux as JE
      on JE.Jdate=CA.CAdate
   left join Joueurs as JO
      on JO.IDjoueur=JE.IDjoueur
   group by CA.CAdate
          , JO.prenom
   order by CA.CAdate
          , JO.prenom
   ;

Et le résultat :

CAdate	IDjoueur	PrenomJoueur	Nbr
2021-01-01	null		0	null
2021-01-02	1	Benoit	2
2021-01-02	4	Sergeï	1
2021-01-03	2	Esmeralda	1
2021-01-04	2	Esmeralda	2
2021-01-05	null		0	null
2021-01-06	2	Esmeralda	1
2021-01-06	3	Mouloud	1
2021-01-06	4	Sergeï	1
2021-01-07	3	Mouloud	1
2021-01-08	null		0	null
2021-01-09	3	Mouloud	1
2021-01-10	1	Benoit	1
2021-01-11	null		0	null
2021-01-12	1	Benoit	1

Grâce à la table "calendrier", on a bien toutes les dates, même celles qui n'ont été jouées par aucun joueur.

[Patrick_38]

Bonjour,

Merci pour vos tests.
Je vais reconstruire le modèle de données. Je vous tiendrai au courant.

Cdt
Patrick

[escartefigue]

Pour bien concevoir le modèle de données, il est recommandé de collecter les règles de gestion, puis de commencer par le modèle conceptuel (MCD).
Il y a un forum consacré à la modélisation, il se trouve ICI

[Patrick_38]

J'ai recréé mes tables comme suit :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE TABLE `Joueur` (
  `IDJoueur` smallint(2) NOT NULL,
  `Prenom` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Liste des joueurs';
ALTER TABLE `Joueur`
  ADD PRIMARY KEY (`IDJoueur`);
CREATE TABLE `Trajet` (
  `IDTrajet` smallint(2) NOT NULL,
  `NBTrajet` smallint(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Liste du nombre de trajets possibles pour un jeu';
ALTER TABLE `Trajet`
  ADD PRIMARY KEY (`IDTrajet`);
CREATE TABLE `Jeu` (
  `IDJoueur` smallint(2) NOT NULL,
  `IDTrajet` smallint(2) NOT NULL,
  `Jdate` date NOT NULL COMMENT 'Date du jeu',
  `Jheure` time NOT NULL COMMENT 'Heure du jeu',
  `Duree1` decimal(5,2) NOT NULL,
  `Duree2` decimal(5,2) DEFAULT NULL,
  `Duree3` decimal(5,2) DEFAULT NULL,
  `Duree4` decimal(5,2) DEFAULT NULL,
  `Duree5` decimal(5,2) DEFAULT NULL,
  `Duree6` decimal(5,2) DEFAULT NULL,
  `Duree7` decimal(5,2) DEFAULT NULL,
  `Duree8` decimal(5,2) DEFAULT NULL,
  `Duree9` decimal(5,2) DEFAULT NULL,
  `Duree10` decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Liste des jeux effectués par les joueurs';
ALTER TABLE `Jeu`
  ADD KEY `fk_Joueur_IDJoueur` (`IDJoueur`),
  ADD KEY `fk_Trajet_IDTrajet` (`IDTrajet`);
ALTER TABLE `Jeu`
  ADD CONSTRAINT `fk_Joueur_IDJoueur` FOREIGN KEY (`IDJoueur`) REFERENCES `Joueur` (`IDJoueur`),
  ADD CONSTRAINT `fk_Trajet_IDTrajet` FOREIGN KEY (`IDTrajet`) REFERENCES `Trajet` (`IDTrajet`);

Avec les données suivantes (pour l'instant) :



Je pense que je me suis mal exprimé.

En fait, je souhaiterai obtenir en sortie les résultats ci-dessous, en effectuant une sélection sur la date 2022-05-12 (pour l'exemple), donc avec un where Jdate='2022-05-12' :



Si l'on prend la ligne 6 du tableau : cela signifie que Patrick a effectué 2 jeux de 2 trajets pour la journée du 2022-05-12.
Pour la ligne 13 : cela signifie qu' Estelle a effectué 0 jeu de 5 trajets pour la journée du 2022-05-12.
Pour la journée du 2022-05-12 on a bien au total 6 jeux qui ont été effectués, avec des nombres de trajets différents selon les jeux.

Est-ce que l'on peut faire cela avec des "join" ?

NB : les durées ne sont pas utilisées dans cette requête; elles le seront dans une autre requête ultérieure.

[Paprick]

Bonsoir,

Je vois deux problèmes à la structure de votre base de données :

• La table "Jeu" ne possède pas de clé primaire comme le montrent les 2 "date-heure" équivalentes sur 2 enregistrements ; déjà, je ne comprends pas que le SGBD tolère ça, mais bon...
• Les 10 rubriques "Durée" dans la table "Jeu" génèrent des tas de valeurs NULL, ce qui ferait bondir tout concepteur de BD respectueux des formes normales !

Bref, pour moi, la clé primaire de "Jeu" doit être composée de l'identifiant du joueur, de la date et de l'heure du jeu, ainsi que d'un numéro de durée : il y aura alors autant d'enregistrements qu'il y a de durées, ce qui ouvrira de nombreuses possibilités en termes de requêtes.

Et surtout, comme le recommande Escartefigue, il est toujours préférable de commencer par la modélisation conceptuelle avant de s'attaquer directement à SQL.

Voici le MCD, le MLD et DDL qui découleraient de la prise en compte ces remarques :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE Joueur(
   Id_Joueur SMALLINT,
   Prénom VARCHAR(50),
   PRIMARY KEY(Id_Joueur)
);
 
CREATE TABLE Trajet(
   Id_Trajet SMALLINT,
   NbTrajet INT,
   PRIMARY KEY(Id_Trajet)
);
 
CREATE TABLE Jeu(
   Id_Joueur SMALLINT,
   Jdate DATE,
   Jheure TIME,
   NumDurée TINYINT,
   Durée DECIMAL(5,2),
   Id_Trajet SMALLINT NOT NULL,
   PRIMARY KEY(Id_Joueur, Jdate, Jheure, NumDurée),
   FOREIGN KEY(Id_Joueur) REFERENCES Joueur(Id_Joueur),
   FOREIGN KEY(Id_Trajet) REFERENCES Trajet(Id_Trajet)
);

Bonne continuation !

[Patrick_38]

Bonjour,

OK, merci pour les conseils et les modèles fournis. Je vais recréer la BDD.
Par contre, pour l'instant, je ne vois pas trop comment je vais récupérer les infos dont j'ai besoin mais je vais creuser et reviendrai vers vous 2 si besoin. Merci encore messieurs.

Cdt
Patrick