[Résolu] Requete SQL challenge

Bonjour,
Je vous explique mon contexte :
j'ai une table identification et une table suivi qui est relié à la table identification par une jointure
donc :

TABLE : Visite identification
ID,asthme(BOOLEAN)
FIN TABLE

TABLE : Visite de suivi
ID,asthme(BOOLEAN),id_identification
FIN TABLE

Ce que je veux : le pourcentage de personne qui ont de l'asthme à l'instant ou on exécute la requête.
Donc je veux que la dernière valeur de l'asthme soit retourné pour chaque personne (une personne correspondant à une ligne dans la table Visite d'identification), sachant que une personne peut avoir plusieurs lignes de visite de suivi.

Exemple de données :

TABLE : Visite identification
1,0
2,1
3,1
ID,asthme(BOOLEAN)
FIN TABLE

TABLE : Visite de suivi
1,1,2
2,1,2
3,0,3
ID,asthme(BOOLEAN),id_identification
FIN TABLE

Dans ce cas je veux que ma requête me retourne
1,0
2,1
3,0
Visite identification.ID,asthme
Modifié par Su4p (17 Dec 2013 - 15:40)

A première vue, j'ai l'impression que le champ asthme dans la table d'identification est au choix redondant et donc inutile, soit pas à jour.

Parce que si tu maintenais le champ asthme de la table identification à jour après chaque insertion d'une nouvelle visite, ce serait facile :

select asthme, count(*) as count
from identification
group by asthme with rollup

Tu n'aurais pas exactement le pourcentage, mais le nombre de personnes dans chaque groupe et le total; à partir de là c'est un jeu d'enfant de calculer le pourcentage, en php ou ce que tu veux...

Après, je conçois qu'il n'est peut-être pas à jour, ou que tu n'aie pas envie de le mettre à jour à chaque nouvelle visite automatiquement. Dans ce cas on peut toujours le mettre à jour manuellement, dans un cron par exemple :

update identification i
set asthme = (
select asthme
from visites v
where v.id_identification = i.id
order by id desc
limit 1
)

Ou en version un peu plus proche du standard SQL (parce que limit est je crois une spécificité de MySQL) :

update identification i
set asthme = (
select asthme
from visites v
where v.id = (
select max(id)
from visites w
where w.id_identification = i.id
)
)

Code potentiellement lent, sans doute optimisable !

Après, tu peux aussi faire la méga requête en un seul coup, mais ça devient vraiment sportif :

select asthme, count(*) as count
from (
select i.id, v.asthme
from identification i, visites v
where i.id = v.id_identification
and v.id = (
select max(id)
from visites w
where w.id_identification = v.id_identification
) 
) unused
group by asthme with rollup

Je n'ai pas du tout testé ces codes, je les ai écrit en live dans le champ de réponse. Je décline toute responsabilité si ça fait ramer excessivement le serveur...
Modifié par QuentinC (12 Dec 2013 - 17:14)

Merci pour ton intervention. Mais l'idée est quand même de savoir si le patient lors de la visite d'identification était asthmatique ou pas. Et donc mettre à jour la table d'identification lors d'une visite de suivie est une hérésie. l'historique du patient serait perdu.
Modifié par Su4p (12 Dec 2013 - 17:24)

a écrit :
Mais l'idée est quand même de savoir si le patient lors de la visite d'identification était asthmatique ou pas. Et donc mettre à jour la table d'identification lors d'une visite de suivie est une hérésie. l'historique du patient serait perdu.

J'ai pas du tout pensé à ça, désolé.

Par contre tu peux voir clairement que maintenir un champ à jour sur l'état du patient à sa dernière visite dans la table identification va beaucoup te simplifier la tâche. Ca te fait une sorte de cache. L'aggrégation comme celle que je te propose en tout dernier et qui fait tout en une fois (si je ne me suis pas trop planté) est par définition longue à calculer puisqu'il faut tout parcourir à chaque fois (et plusieurs fois en plus).

Si tu veux garder l'état lors de la première visite, tu pourrais ajouter un champ supplémentaire. Dans la table identification, tu aurais donc l'état initial et l'état à la dernière visite. Si besoin, tu peux mettre à jour les tables existantes avec des insert-select ou des update-select.

En fait, l'utilité de ce « cache » dépend surtout de la fréquence à laquelle tu auras besoin de cette statistique, et de la taille des données à traiter.

QuentinC a écrit :

Par contre tu peux voir clairement que maintenir un champ à jour sur l'état du patient à sa dernière visite dans la table identification va beaucoup te simplifier la tâche. Ca te fait une sorte de cache.

Gérer un cache ne simplifie jamais la tâche, ça la complique. La sous-requête est en effet la solution. Le SQL a été créé et inventé pour cela. Après, si (et seulement si) on a des problèmes de performance, on peut commencer à implémenter des solutions de cache. Sans parler que le cache peut être géré ailleurs tout aussi bien.

Sinon, pour simplifier, il faudrait surtout corriger le modèle de données, qui n'est pas terrible. L'identification n'est juste qu'un cas particulier du suivi (le premier). L'information "asthme" n'a rien à y faire.

On aurait donc, pour ce qui nous concerne une table identification (ou plutôt patient, avec un id), et une table suivi avec id, asthme, et id_patient (et idéalement une date de suivi plutôt que de gérer cela sur l'id… )

ce qui donnerait:

SELECT id, asthme, id_identification FROM (SELECT * FROM suivi ORDER BY ID DESC) GROUP BY id_identification

Pas de gestion de cache, pas de boucles de sélection imbriquées, structure des données cohérentes, que du bonheur quoi…
et dans ce cas pour connaître la situation à l'identification:

SELECT id, asthme, id_identification FROM (SELECT * FROM suivi ORDER BY ID ASC) GROUP BY id_identification

(j'ai laissé la sous-requête dont on pourrait se passer ici, mais comme le tri devrait se faire sur une date, et que la table pourrait ne pas être naturellement ordonnée, c'est ainsi qu'il faudrait le faire)
Modifié par kustolovic (12 Dec 2013 - 23:37)

a écrit :
Gérer un cache ne simplifie jamais la tâche, ça la complique.

Je ne suis pas d'accord. Enfin, pas toujours. Ici j'ai franchement l'impression que ça simplifie les choses, un petit update à chaque visite n'est vraiment pas compliqué, beaucoup moins qu'une super requête imbriquée.
Et puis, au cas où, on a les triggers.

a écrit :
Sinon, pour simplifier, il faudrait surtout corriger le modèle de données, qui n'est pas terrible. L'identification n'est juste qu'un cas particulier du suivi (le premier). L'information "asthme" n'a rien à y faire.

Par contre en réalité sur ce point tu as totalement raison. C'est à ce à quoi j'ai pensé en premier quand je disais redondant/inutile; mais plutôt que de le supprimer, il m'a semblé plus logique d'en faire autre chose.

a écrit :
(et idéalement une date de suivi plutôt que de gérer cela sur l'id… )

+1, ça sera nettement plus propre. Mais si on suppose que l'ID est toujours croissant, le principe et les requêtes à exécuter restent malgré tout fondamentalement les mêmes.

Pour la réponse la plus simple, la voici :

SELECT v.ID, COALESCE(s.asthme, v.asthme) AS asthme
FROM Visitieidentification v
LEFT JOIN VisiteSuivi s
    ON s.id_identification = v.id_identification
    AND NOT EXISTS( 
        SELECT 1
        FROM VisiteSuivi s2
        WHERE S2.id_identification = s.id_identification
        AND S2.id > s.id
    )

Toutefois pour les critiques faites sur le modèle de donnée, vous devez savoir messieurs que pour établir un bon modèle de donnée il faut en connaitre le sujet. Ce qui ne semble pas être votre cas, on pourrait passer 1 heure à discuter en quoi une visite d'identification est différente d'une visite de suivi, et en quoi une personne qui a été diagnostiqué avec l'asthme pourrait, lors d'une différente visite voir son asthme se résorber; en quoi doubler le champ asthme dans la table identification serait une redondance d'information, qui n'a vraiment pas sa place en cet endroit.

Vous auriez pu vous douter que pour faciliter la compréhension, il vous a été servit une version très réduite de la table(En réalité la requête ne porte pas que sur le champ asthme mais sur une 15 champs, la table comprends plus de 200 champs et le modèle plus de 100 tables généré automatiquement par un framework) et donc bien-sur il existe une date(pour des visites...... évidement qu'il existe une date !!!! Toutefois personnellement je préfère faire un traitement de type (max) sur un entier plutôt que sur une date).

"On aurait donc, pour ce qui nous concerne une table identification (ou plutôt patient, avec un id), et une table suivi avec id, asthme, et id_patient (et idéalement une date de suivi plutôt que de gérer cela sur l'id… )" <-- C'est le cas

Cette requête :

SELECT id, asthme, id_identification FROM (SELECT * FROM suivi ORDER BY ID ASC) GROUP BY id_identification

Ne répond absolument pas au problème donné, pourquoi une sous requete sur toute la table de suivi ?
Cette requête fonctionnerait aussi bien :

SELECT id, asthme, id_identification FROM suivi  GROUP BY id_identification

Mais ne répondrait toujours pas au problème (que dire des patients qui n'ont pas de visite de suivi)

Ci-dessus vous trouverez la bonne (par bonne j'estime la plus simple, et rapide tout en étant juste). Réponse donnée sur un autre forum, je vous remercie d'avoir pris le temps de répondre. Je vous invite à prendre le temps de comprendre cette requête que je trouve très élégante.
Modifié par Su4p (16 Dec 2013 - 11:10)

Su4p a écrit :
Toutefois pour les critiques faites sur le modèle de donnée, vous devez savoir messieurs que pour établir un bon modèle de donnée il faut en connaitre le sujet. Ce qui ne semble pas être votre cas, on pourrait passer 1 heure à discuter en quoi une visite d'identification est différente d'une visite de suivi, et en quoi une personne qui a été diagnostiqué avec l'asthme pourrait, lors d'une différente visite voir son asthme se résorber; en quoi doubler le champ asthme dans la table identification serait une redondance d'information, qui n'a vraiment pas sa place en cet endroit.

En connaissant plus de détails, la solution de modèle n'est peut-être pas la bonne, mais le problème demeure: la structure reste mauvaise. L'information asthme, qui est la même, se retrouve séparée en deux tables, ce qui cause des problèmes et des besoins de requêtes autrement complexes pour récupérer une informations somme toutes simple.

Les solutions adaptées à ce problème existent pourtant:

Cas simple:
Une table suivi, contenant toutes les données communes à la visite d'identification et la visite d'identification (asthme notamment).
Une table identification, contenant toutes les données récoltées qui ne le sont que la première fois, et qui hérite de suivi.

Cas complexe:
Une table abstraite séance, contenant toutes les données communes à la visite d'identification et la visite d'identification (asthme notamment).
Une table identification, contenant toutes les données récoltées qui ne le sont que la première fois, et qui hérite de séance.
Une table suivi, contenant toutes les données récoltées qui ne le sont que lors des suivis, et qui hérite de séance.

Entre ces deux modèles, l'un des deux est le bon. C'est propre, maintenable, et facile à requêter.

Su4p a écrit :
et donc bien-sur il existe une date(pour des visites...... évidement qu'il existe une date !!!! Toutefois personnellement je préfère faire un traitement de type (max) sur un entier plutôt que sur une date).

C'est à ça que servent les timestamps. présuposer que la chronologie des ids correspond à la chronologie des séances est faux. Cela entraîne un risque d'erreur, par soi-même, et encore plus par un autre développeur. Que se passe-t-il si on insère plus tard une séance que l'on avait oublié (pas pris le temps de l'introduire dans le système, panne informatique, etc.) ? Un système qui se base sur des présomptions est toujours soumis à un risque d'erreurs élevé.

"SELECT id, asthme, id_identification FROM (SELECT * FROM suivi ORDER BY ID ASC) GROUP BY id_identification"
oui ça peut paraître absurde avec ID, mais plus avec date:
"SELECT id, asthme, id_identification FROM (SELECT * FROM suivi ORDER BY DATE ASC) GROUP BY id_identification"

Su4p a écrit :
(que dire des patients qui n'ont pas de visite de suivi)

Avec un modèle correct, ce cas là n'arriverait pas.

a écrit :
Je vous invite à prendre le temps de comprendre cette requête que je trouve très élégante.

Une requête sur des données mal structurée, non, je ne peux pas, je trouve toujours cela hyper moche (en plus d'être inutilement lourd)

kustolovic a écrit :

En connaissant plus de détails, la solution de modèle n'est peut-être pas la bonne, mais le problème demeure: la structure reste mauvaise. L'information asthme, qui est la même, se retrouve séparée en deux tables, ce qui cause des problèmes et des besoins de requêtes autrement complexes pour récupérer une informations somme toutes simple.

L'information asthme n'est pas la même sur la visite d'identification, et sur (la ou les) visite de suivi.

kustolovic a écrit :

"SELECT id, asthme, id_identification FROM (SELECT * FROM suivi ORDER BY DATE ASC) GROUP BY id_identification"

PS : avec un alias de table ça marche mieux ^^

"SELECT id, asthme, id_identification FROM (SELECT * FROM suivi ORDER BY DATE ASC) suivi GROUP BY id_identification"

Sur cette requête pour quoi faire une sous requête ?

SELECT id, asthme, id_identification FROM  suivi GROUP BY id_identification ORDER BY DATE ASC

Celle ci ne suffirait-elle pas ?
Modifié par Su4p (16 Dec 2013 - 14:57)

Bien joué pour le not exist, effectivement c'est bien plus simple et élégant que de jouer avec max.

Par contre, je me demande si c'est plus rapide avec MySQL. C'est censé l'être avec un bon SGBD, mais la dernière fois que j'ai utilisé not exist, je l'ai regretté amèrement, et j'ai eu l'impression que MySQL était passablement con et pas optimisé du tout pour les not exists. A tester et comparer avec des explain pour être sûr.

a écrit :
Vous auriez pu vous douter que pour faciliter la compréhension, il vous a été servit une version très réduite de la table(En réalité la requête ne porte pas que sur le champ asthme mais sur une 15 champs, la table comprends plus de 200 champs et le modèle plus de 100 tables généré automatiquement par un framework) et donc bien-sur il existe une date

Certes; mais compte tenu des informations dont on avait à disposition, on ne pouvait pas le deviner. C'est indispensable de simplifier, mais il ne faut pas non plus trop simplifier. Sinon, forcément, on perd de l'information et il nous en manque pour pouvoir répondre correctement au problème posé.

Pour le moment, j'ai des performances très satisfaisante, je n'ai pas encore suffisamment pousser les tests aux bornes, mais ça ne saurait tarder.
Pour le not exist rendons à César ce qui appartient à César : http://www.developpez.net/forums/d1400634/bases-donnees/langage-sql/requete-sql-mode-challenge/#post7610872

C'est "aieeeuuuuu" Expert Confirmé en SQL du forum http://www.developpez.net/ qui a trouvé la réponse.

Concernant MySQL, je suis entrain de me demander si je ne devrais pas opter pour un autre SGBD.