Conception de requête

Bonjour,

Je me trouve devant un cas tordu que j'arrive à écrire en java, mais pas dans une requête, en même temps je ne suis pas certains que cela soit faisable. je vous expose le truc.

J'ai une formation qui se découpe en session et chaque session en module.
une formation à donc n sessions ayant elle même n modules.

Un élève peut s'inscrite à 1 ou n sessions selon les modules qu'il aura besoin d'acquérir. Voici un extrait des données :

Formation :

Code:

---

1 2	formation_id | formation_name | formation_date_debut | formation_date_fin 1 | 'faire du vélo'| 01/01/2012 | 03/04/2012

---

Session :

Code:

---

1 2 3 4 5

session_id | session_name | session_date_debut | session_date_fin | formation_id 1 |'Pédaler' | 01/01/2012 |01/02/2012 | 1 2 |'Equilibre' | 02/02/2012 |02/03/2012 | 1 3 |'Position' | 03/03/2012 |03/04/2012 | 1

---

Module :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

module_id | module_name | module_date_debut | module_date_fin | session_id 1 | 'repérer les calles-pieds' | 01/01/2012 | 02/01/2012 | 1 2 | 'monter sur le vélo' | 03/01/2012 | 06/01/2012 | 1 3 | 'position du pied' | 07/01/2012 | 15/01/2012 | 1 4 | 'appuyez sur la pédale' | 16/01/2012 | 30/01/2012 | 1 5 | 'descendre du vélo' | 31/01/2012 | 01/02/2012 | 1 6 | 'monter sur le vélo rappel' | 02/02/2012 | 04/02/2012 | 2 7 | 'effet gyroscopique' | 05/02/2012 | 10/02/2012 | 2 8 | 'travaux pratique' | 11/02/2012 | 02/03/2012 | 2 9 | 'jambes tendu' | 03/03/2012 | 10/03/2012 | 3 10 | 'recherche de vitesse' | 11/03/2012 | 25/03/2012 | 3 11 | 'danseuse' | 26/03/2012 | 03/04/2012 | 3

---

Inscription :

Code:

---

1 2 3 4 5 6 7

inscription_id |formation_id | eleve_id 1 | 1 | 1 2 | 1 | 2 3 | 1 | 3 4 | 1 | 4 5 | 1 | 5

---

Inscription_session :

Code:

---

1 2 3

inscription_session_id | inscription_id | session_id 1 | 1 | 3

---

Une inscription fait le lien entre la formation à suivre et un élève.
L'inscription_session permet de lier une inscription à une session particulière.
Pour des raisons pratiques, nous avons décidés de ne renseigner la table inscription_session uniquement quand un élève ne participera pas à l'intégralité des formations. Ainsi dans l'exemple ci-dessus, 5 élèves sont inscrits à la formation, mais l'élève 1 ne participera que à partir de la session 3.

Maintenant la question :
Je souhaiterai compter le nombres d'inscrits par sessions.
je devrai donc obtenir :

Code:

---

1 2 3 4 5

session_id | nombre inscrit 1 | 4 2 | 4 3 | 5

---

D'avance merci de vos lumières.
et n'hésitez pas à me dire si ce n'est pas claire.

HadanMarv

Bonjour,


C'est possible, mais pour ça il va falloir relier deux calculs avec un union all :
- 1er calcul pour les sessions qui n'ont pas d'élève qui ne suivent pas globalement la formation
- 2 eme calcul pour les autres


Ca aurai été beaucoup plus simple avec un modèle de donnée plus proche de vos besoins.

Citation:

---

Envoyé par punkoff

C'est possible, mais pour ça il va falloir relier deux calculs avec un union all :
- 1er calcul pour les sessions qui n'ont pas d'élève qui ne suivent pas globalement la formation

---

Donc :

Code:

---

1 2 3 4

select count(ins.inscription_id) from inscription ins left join inscription_session on inscription_session.inscription_id = ins.inscription_id where inscription_session.inscription_session_id is null

---

Citation:

---

Envoyé par punkoff

- 2 eme calcul pour les autres

---

Code:

---

1 2 3 4

select count(ins.inscription_id) from inscription ins left join inscription_session on inscription_session.inscription_id = ins.inscription_id where inscription_session.inscription_session_id is not null

---

Je n'ai je pense pas compris.

Citation:

---

Envoyé par punkoff

Ca aurai été beaucoup plus simple avec un modèle de donnée plus proche de vos besoins.

---

Le modèle de données fourni est conforme et identique à celui utilisé aux données près.

HadanMarv

Citation:

---

Pour des raisons pratiques, nous avons décidés de ne renseigner la table inscription_session uniquement quand un élève ne participera pas à l'intégralité des formations. Ainsi dans l'exemple ci-dessus, 5 élèves sont inscrits à la formation, mais l'élève 1 ne participera que à partir de la session 3.

---

Et c'est ben là que le problème se situe. Votre modélisation actuelle n'est justement pas "pratique" dans la mesure où la participation aux sessions est ambiguë. La table sensée exprimer cette notion n'expose finalement pas toutes les données (votre cas particulier "un participant à toutes les sessions d'une formations n'est pas présent dans la table inscription_session").

S'il y a bien quelque chose qui n'est pas pratique, ce sont les données qui n'ont pas une définition simple et systématique et vous vous en rendez compte avec la difficulté à exploiter les données de votre modèle. Vous devez gérer une finesse d'inscription au niveau session, faites le jusqu'au bout et éliminez la table inscription.

Bonjour,

On y est presque :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

select b.formation_id, b.session_id, count(*) from Inscription a inner join session b on a.formation_id = b.formation_id where not exists (select null from inscription_session c inner join inscription d on c.inscription_id = d.inscription_id where d.formation_id = b.formation_id and c.session_id = b.session_id) group by b.formation_id, b.session_id union all select b.formation_id, b.session_id, count(*) from Inscription a inner join session b on a.formation_id = b.formation_id where exists (select null from inscription_session c inner join inscription d on c.inscription_id = d.inscription_id where d.formation_id = b.formation_id and c.session_id = b.session_id) group by b.formation_id, b.session_id

---

:aie: y a peut-être moyen de ne pas faire un union all et de gérer le count avec une jointure externe + gérer un sum avec un case / when dans le select.

Citation:

---

Le modèle de données fourni est conforme et identique à celui utilisé aux données près.

---

Je ne dis pas le contraire, et c'est bien là le problème.

La requête devient bordélique à cause de vos choix de modélisation / fonctionnement.

Je vous laisserai aller sur le forum de modélisation si vous voullez une réponse correct, mais de point de vu :

Fonctionnellement, vos élèves s'inscrivent à une session et non à une formation.

En reprenant votre MCD :
Formation-0,n--------Contient---------(1,1)-Session-0,n------Compose---1,1-Module


Eleve-0,n-------Inscrit-------0,n-Session


=> niveau MPD
Eleve(eleve_id,...)
Formation(formation_id,...)
Session(session_id, #formation_id,....)
Inscription(#session_id, #formation_id, #eleve_id)



A partir de là les requêtes deviennent simple ...
Pour votre cas :

Code:

---

1 2 3 4

select formation_id, session_id, count(*) from Inscription group by formation_id, session_id

---

Pour cherchez si un élève est présent à une formation :

Code:

---

1 2 3 4

select * from formation a where exists (select null from Inscription b where a.formation_id = b.formation_id and b.eleve_id = 'X')

---

Etc.

Tout d'abord merci de votre analyse et expertise.

Comment ne pas allez dans votre sens. Quand je dis "pour des raisons pratiques, nous avons décidés...", je m'inclus dedans bien que débarquant sur le projet.
Plusieurs de mes postes sur ce forum sont dû à la reprise en main d'une application.
Je reprends la maintenance et l'évolution de cette application donc le développement à débuter en 2005. 99% de la logique métier est dans les requêtes Sql. L'ajout d'Ibatis dans l'architecture (2 ans auparavant) à uniquement servi à centraliser les requêtes dans des fichiers xml.
80% des requêtes ont plus 15 jointures.

Je suis tout à fait d'accord avec le fait que la conception est plus que cauchemardesque (j'ai d'ailleurs déjà employé ce terme, sur un précédent post). Cependant je ne puis mettre à la poubelle 6 années de développements.
Ce que je viens chercher ce sont des conseils et des pistes pour résoudre le soucis actuel. Il est certes intéressant de montrer du doigt les problèmes de conception, mais dans le cas présent, nous ne pouvons pour le moment le contourner.

En ce qui concerne la requête fourni par punkoff (que je remercie d'ailleurs), j'arrive à des résultats. mais je dois encore recouper les données pour être sûre d'avoir enfin le Graal.
Merci

HadanMarv

Oué bein l'expertise est pas terrible, car la requête posté ne marche pas.

Par contre l'approche avec le left outer join fonctionne avec le jeu de test donné, et est plus simple :

Code:

---

1 2 3 4 5 6 7 8 9

select b.formation_id, b.session_id, sum(case when b.session_id = c.session_id or c.session_id is null then 1 else 0 end) from Inscription a INNER JOIN session b ON a.formation_id = b.formation_id left outer join inscription_session c on c.inscription_id = a.inscription_id group by b.formation_id, b.session_id

---