Je me tourne aujourd'hui vers vous pour un problème de requête SQL. Je m'explique, je souhaite récupérer un classement par points et j'y arrive, sauf que les points et le nombre d'étapes validés sont incorrects.
Le contexte :
Nous avons un joueur qui participe à un évènement. Cet évènement se fait via son téléphone mobile sur une application web. Il se connecte à l'évènement en question à l'aide d'un code. Il a alors accès à une carte avec sa position et des étapes cachées autours de lui sur la carte. Il active une étape lorsqu'il est à proximité de cette dernière, une fois activée, l'étape dévoile son contenu et peut potentiellement posséder un Quiz. Ce Quiz peut donner des points si la réponse donnée est correcte.
Ce que je souhaite faire, c'est obtenir un classement des joueurs par nombre de points obtenus sur les Quiz.
Voici ma requête :
SELECT SUM(PGQA.points) AS total_points,
PG.name AS GUEST_NAME,
PG.guest_code AS GUEST_CODE,
PT.track_id AS TRACK_ID,
COUNT(PMC.marker_checked_id) AS activated_markers
FROM p_tracks AS PT
INNER JOIN p_guests AS PG ON PG.p_tracks_track_id=PT.track_id AND PG.is_owner=FALSE
LEFT JOIN p_markers_checked AS PMC ON PMC.p_guests_guest_id=PG.guest_id
LEFT JOIN p_guests_quiz_answers AS PGQA ON PGQA.guest_id=PG.guest_id
WHERE PT.track_id=813 GROUP BY PG.guest_id ORDER BY total_points DESC
Explications des champs de la requête :
La table p_tracks est la table regroupant les évènements (jeux). La table p_guests est la table regroupant les joueurs et sont liés à un p_track. La table p_markers_checked est la table regroupant les étapes activées par les joueurs et sont liés à la table p_markers qui ne se trouvent pas dans ma requête SQL (inutile). La table p_guests_quiz_answers est la table regroupant les réponses des joueurs sur les quiz et est lié à la table p_quiz qui est la table regroupant les quiz et p_guests.
J'ai utilisé LEFT JOIN sur p_markers_checked et p_guests_quiz_answers, car une étape peut ne pas posséder de Quiz et un joueur peut ne pas avoir activé d'étape, mais ce n'est pas pour autant que je ne veux pas afficher les joueurs qui n'ont pas du tout répondu à un quiz ou activé une étape, je veux les récupérer avec un total de points de 0 et un activated_markers de 0.
Le total_points représente le nombre total de points qu'un joueur a obtenu. Le guest_name est le nom du joueur. Le guest_code est le code unique du joueur, ce code n'est valable que pour un évènement. Le track_id est l'id unique de l'évènement. Le activated_markers est le nombre d'étapes qu'un joueur a activé.
Le résultat souhaité :
Le résultat obtenu :
Informations sur l'évènement du résultat ci-dessus :
L'évènement possède 3 étapes et 2 quiz avec respectivement 10 et 5 points à obtenir. Les trois premiers joueurs ont activé 2/3 étapes et le dernier 0 étape. Le premier joueur a répondu correctement aux 2 quiz, ce qui devrait lui faire 15 points obtenus. Le second joueur a répondu correctement à 1 quiz, ce qui devrait lui faire 10 points obtenus. Le troisième joueur n'a répond à aucun quiz, ce qui devrait lui faire 0 point obtenu (à la limite le NULL n'est pas un problème). Le dernier joueur n'a découvert aucune étape et répondu par conséquent à un aucun quiz.
Ce qui se passe selon moi :
Le SUM(PGQA.points) calcule le nombre de points de cette manière : nombre_total_de_points * nombre_total_de_réponses (10*2)+(5*2) = 30
Alors qu'il devrait faire : 10+5 = 15
le *2 est le nombre total de réponses données.
Il se passe exactement la même chose avec le nombre de total d'étapes activées : (1*2) + (1*2) = 4
Au lieu de : 1+1 = 2
Le *2 étant le nombre d'étapes activées.
Voilà, j'espère avoir communiqué toutes les informations nécessaires à la résolution de mon problème, je remercie tout ce qui prendront la peine de me répondre et m'aider.
Déjà, simple détail et si j'ai bien compris le modèle, tu peux retirer la table p_tracks de la requête puisque tu n'en utilises que l'id qui est déjà présent dans la table p_guests ...
Cela dit, je me pose des questions sur l'organisation de tes tables. Dans ta description, je ne vois pas comment tu fais le lien entre p_markers_checked et p_guests_quiz_answers. Et je pense que ton problème de "doublon" vient de là.
La table p_quiz évoquée n'est-elle pas liée à la table p_markers_checked ou p_markers ?
Déjà, simple détail et si j'ai bien compris le modèle, tu peux retirer la table p_tracks de la requête puisque tu n'en utilises que l'id qui est déjà présent dans la table p_guests ...
Tu as raison.
Benzouye a écrit:
Cela dit, je me pose des questions sur l'organisation de tes tables. Dans ta description, je ne vois pas comment tu fais le lien entre p_markers_checked et p_guests_quiz_answers. Et je pense que ton problème de "doublon" vient de là.
La table p_quiz évoquée n'est-elle pas liée à la table p_markers_checked ou p_markers ?
Alors, la table p_markers_checked est liée à p_markers et p_guests. Un guest peut avoir 1 ou plusieurs markers_checked et un markers_checked peut être lié à un seul p_guest. Un p_markers peut être lié à un ou plusieurs p_markers_checked mais un p_markers_checked ne peut être lié qu'à un seul p_marker.
Il n'y pas de lien direct entre p_markers_checked et p_guests_quiz_answers, le lien se fait plutôt d'une manière hiérarchique (regarde ta messagerie, ça te permettra de mieux comprendre).
La table p_quiz est liée à p_tracks et p_quiz_questions, la table p_quiz_questions est liée à la table p_guests_quiz_answers.
Encore une fois, regarde ta messagerie, ce sera plus clair avec ce que je t'ai envoyé.
Edite :
Je crois avoir réussi... avec cette requête :
SELECT SUM(PGQA.points) AS total_points,
PG.name AS GUEST_NAME,
PG.guest_code AS GUEST_CODE,
PT.track_id AS TRACK_ID,
COUNT(PMC.marker_checked_id) AS activated_markers
FROM p_tracks AS PT
INNER JOIN p_guests AS PG ON PG.p_tracks_track_id=PT.track_id AND PG.is_owner=FALSE
INNER JOIN p_markers AS PM ON PM.p_tracks_track_id=PG.p_tracks_track_id
LEFT JOIN p_markers_checked AS PMC ON PMC.p_guests_guest_id=PG.guest_id AND PMC.p_markers_marker_id=PM.marker_id
INNER JOIN p_quiz_questions AS PQQ ON PQQ.marker_id=PM.marker_id
LEFT JOIN p_guests_quiz_answers AS PGQA ON PGQA.guest_id=PG.guest_id AND PGQA.question_id=PQQ.question_id
WHERE PT.track_id=813 GROUP BY PG.guest_id ORDER BY total_points DESC
Ne tiens pas compte du p_tracks, je vais modifier ça. Mais cette requête me donne le bon résultat, qu'en penses-tu ?
Au passage, le INNER JOIN sur p_quiz_questions n'est-il pas gênant ?
Sinon je trouve le modèle passé par MP un peu touffu, et je pense qu'il serait simplifiable mais c'est un autre sujet ...
Je ne comprends pas non plus LOL. En ce qui concerne le INNER JOIN sur p_quiz_questions, suite à ta remarque, j'ai essayé d'afficher un ladder sur un évènement sans quiz, et bien sûr, il ne l'affiche pas à cause de ce INNER ! J'ai mis par conséquent LEFT JOIN, ce qui permet d'afficher les joueurs même si aucun quiz n'est présent, mais ... je n'avais pas le bon nombre de activated_markers. J'ai essayé un RIGHT JOIN (je ne l'avais jamais utilisé avant) et j'ai le résultat qui s'affiche correctement, SAUF s'il n'y a aucun quiz dans une étape d'un évènement où dans ce cas, rien ne s'affiche non plus.
SELECT SUM(PGQA.points) AS total_points,
PG.name AS GUEST_NAME,
PG.guest_code AS GUEST_CODE,
PT.track_id AS TRACK_ID,
COUNT(PMC.marker_checked_id) AS activated_markers
FROM p_tracks AS PT
INNER JOIN p_guests AS PG ON PG.p_tracks_track_id=PT.track_id AND PG.is_owner=FALSE
INNER JOIN p_markers AS PM ON PM.p_tracks_track_id=PG.p_tracks_track_id
LEFT JOIN p_markers_checked AS PMC ON PMC.p_guests_guest_id=PG.guest_id AND PMC.p_markers_marker_id=PM.marker_id
LEFT JOIN p_quiz_questions AS PQQ ON PQQ.marker_id=PM.marker_id
LEFT JOIN p_guests_quiz_answers AS PGQA ON PGQA.guest_id=PG.guest_id AND PGQA.question_id=PQQ.question_id
WHERE PT.track_id=815 GROUP BY PG.guest_id ORDER BY total_points DESC
SELECT
SUM( A.points ) AS total_points,
G.name AS GUEST_NAME,
G.guest_code AS GUEST_CODE,
G.p_tracks_track_id AS TRACK_ID,
COUNT( GM.marker_checked_id ) AS activated_markers
FROM
p_guests G
LEFT JOIN p_markers_checked GM
ON G.guest_id = GM.p_guests_guest_id
LEFT JOIN p_markers M
ON GM.p_markers_marker_id = M.marker_id
LEFT JOIN p_quiz_questions Q
ON M.marker_id = Q.marker_id
LEFT JOIN p_guests_quiz_answers A
ON Q.question_id = A.question_id
AND G.guest_id = A.guest_id
WHERE
G.p_tracks_track_id = 813
AND G.is_owner = FALSE
GROUP BY G.guest_id
ORDER BY total_points DESC
On part de tous les joueurs (guests) d'un événement (tracks) donné (ici 813).
De là, tout en jointure externe :
on relie aux étapes validées de chacun
on relie à l'étape liée
on relie aux questions liées
on relie aux réponses liées
Les cardinalités aidant, on en devrait pas avoir de doublon, et le compte devrait passer.
SELECT
SUM( A.points ) AS total_points,
G.name AS GUEST_NAME,
G.guest_code AS GUEST_CODE,
G.p_tracks_track_id AS TRACK_ID,
COUNT( MC.marker_checked_id ) AS activated_markers
FROM
p_guests G
LEFT JOIN p_markers_checked GM
ON G.guest_id = GM.p_guests_guest_id
LEFT JOIN p_markers M
ON GM.p_markers_marker_id = M.marker_id
LEFT JOIN p_quiz_questions Q
ON M.marker_id = Q.marker_id
LEFT JOIN p_guests_quiz_answers A
ON Q.question_id = A.question_id
AND G.guest_id = A.guest_id
WHERE
G.p_tracks_track_id = 813
AND G.is_owner = FALSE
GROUP BY G.guest_id
ORDER BY total_points DESC
On part de tous les joueurs (guests) d'un événement (tracks) donné (ici 813).
De là, tout en jointure externe :
on relie aux étapes validées de chacun
on relie à l'étape liée
on relie aux questions liées
on relie aux réponses liées
Les cardinalités aidant, on en devrait pas avoir de doublon, et le compte devrait passer.
Dis-moi si ça le fait !
PS : J'ai critiqué un peu ton modèle en MP
- Edité par Benzouye il y a 12 minutes
Merci beaucoup de ton aide ! Il manque la jointure avec la table MC (p_markers_checked), elle se trouve dans le select mais pas dans les jointures, ce qui provoque une erreur. De quelle manière tu l'implémenterais dans cette requête ?
Édite :
Ah non, tu l'avais bien mise en jointure, c'est juste que le nom est GM est non CM. J'ai fait la modification et le résultat attendu n'est pas le bon.
Ici, le problème est la colonne "activated_markers" qui affiche 3 markers activés, alors qu'en réalité, les 3 joueurs ont seulement 2 étapes (markers) activées. Ce résultat est exactement celui que j'obtiens aussi avec la dernière requête en réponse à ce sujet.
My mistake J'ai édité mon post pour corriger cela ...
S@yf a écrit:
en réalité, les 3 joueurs ont seulement 2 étapes (markers) activées
Pour mieux comprendre ce qui se passe, il faudrait retirer les fonctions d'agrégat.
Peux-tu sortir le résultat sans :
SELECT
G.name AS GUEST_NAME,
G.guest_code AS GUEST_CODE,
G.p_tracks_track_id AS TRACK_ID,
GM.marker_checked_id,
Q.question_id,
A.guest_quiz_answer_id
FROM
p_guests G
LEFT JOIN p_markers_checked GM
ON G.guest_id = GM.p_guests_guest_id
LEFT JOIN p_markers M
ON GM.p_markers_marker_id = M.marker_id
LEFT JOIN p_quiz_questions Q
ON M.marker_id = Q.marker_id
LEFT JOIN p_guests_quiz_answers A
ON Q.question_id = A.question_id
AND G.guest_id = A.guest_id
WHERE
G.p_tracks_track_id = 813
AND G.is_owner = FALSE
My mistake J'ai édité mon post pour corriger cela ...
S@yf a écrit:
en réalité, les 3 joueurs ont seulement 2 étapes (markers) activées
Pour mieux comprendre ce qui se passe, il faudrait retirer les fonctions d'agrégat.
Peux-tu sortir le résultat sans :
Bien sûr :
Note : Une étape peut avoir plusieurs questions. Note 2 : Le joueur A3UQHG est celui qui n'a activé aucune étape (et forcément, répondu à aucune question)
Édite :
Cette requête fonctionne, c'est la première fois que j'utilise une sub-query et la fonction COALESCE:
SELECT
(SELECT COALESCE(SUM(GQA.points), 0) FROM p_guests_quiz_answers AS GQA WHERE GQA.guest_id=G.guest_id) AS total_points,
G.name AS GUEST_NAME,
G.guest_code AS GUEST_CODE,
G.p_tracks_track_id AS TRACK_ID,
COUNT( GM.marker_checked_id ) AS activated_markers
FROM
p_guests G
LEFT JOIN p_markers_checked GM
ON G.guest_id = GM.p_guests_guest_id
LEFT JOIN p_markers M
ON GM.p_markers_marker_id = M.marker_id
WHERE
G.p_tracks_track_id = 798
AND G.is_owner = FALSE
GROUP BY G.guest_id
ORDER BY total_points DESC
Qu'en penses-tu ? Je ne sais pas si c'est mauvais en terme de performance, sachant que nous pouvons avoir plus de 10.000 données sur un seul évènement au bout de seulement quelques semaines/mois. Selon mes tests, ça n'a pas l'air d'être gourmand.
Disons que les sous-requêtes imbriqués dans le SELECT ce n'est vraiment pas top en terme de performance ... elles sont réévaluées pour chaque enregistrement.
S@yf a écrit:
Une étape peut avoir plusieurs questions
Ok ... du coup je pense qu'un "simple" COUNT DISTINCT résoudra ton problème :
SELECT
SUM( A.points ) AS total_points,
G.name AS GUEST_NAME,
G.guest_code AS GUEST_CODE,
G.p_tracks_track_id AS TRACK_ID,
COUNT( DISTINCT GM.marker_checked_id ) AS activated_markers
FROM
p_guests G
LEFT JOIN p_markers_checked GM
ON G.guest_id = GM.p_guests_guest_id
LEFT JOIN p_markers M
ON GM.p_markers_marker_id = M.marker_id
LEFT JOIN p_quiz_questions Q
ON M.marker_id = Q.marker_id
LEFT JOIN p_guests_quiz_answers A
ON Q.question_id = A.question_id
AND G.guest_id = A.guest_id
WHERE
G.p_tracks_track_id = 813
AND G.is_owner = FALSE
GROUP BY G.guest_id
ORDER BY total_points DESC
Disons que les sous-requêtes imbriqués dans le SELECT ce n'est vraiment pas top en terme de performance ... elles sont réévaluées pour chaque enregistrement.
S@yf a écrit:
Une étape peut avoir plusieurs questions
Ok ... du coup je pense qu'un "simple" COUNT DISTINCT résoudra ton problème :
SELECT
SUM( A.points ) AS total_points,
G.name AS GUEST_NAME,
G.guest_code AS GUEST_CODE,
G.p_tracks_track_id AS TRACK_ID,
COUNT( DISTINCT GM.marker_checked_id ) AS activated_markers
FROM
p_guests G
LEFT JOIN p_markers_checked GM
ON G.guest_id = GM.p_guests_guest_id
LEFT JOIN p_markers M
ON GM.p_markers_marker_id = M.marker_id
LEFT JOIN p_quiz_questions Q
ON M.marker_id = Q.marker_id
LEFT JOIN p_guests_quiz_answers A
ON Q.question_id = A.question_id
AND G.guest_id = A.guest_id
WHERE
G.p_tracks_track_id = 813
AND G.is_owner = FALSE
GROUP BY G.guest_id
ORDER BY total_points DESC
Effectivement le COUNT DISTINCT résout ce dernier problème, et au passage je ne savais pas qu'on pouvait le placer de cette manière J'utiliserai par conséquent cette façon de faire pour éviter les sous requêtes. Je te remercie de ton aide et tes éclaircissements Benzouye !
Un cerveau c'est bien, plusieurs c'est mieux !
Requête SQL, résultat SUM et COUNT incorrect
× Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
× Attention, ce sujet est très ancien. Le déterrer n'est pas forcément approprié. Nous te conseillons de créer un nouveau sujet pour poser ta question.
Un cerveau c'est bien, plusieurs c'est mieux !
Un cerveau c'est bien, plusieurs c'est mieux !
Un cerveau c'est bien, plusieurs c'est mieux !
Un cerveau c'est bien, plusieurs c'est mieux !
Un cerveau c'est bien, plusieurs c'est mieux !
Un cerveau c'est bien, plusieurs c'est mieux !