Discussion :

[HadanMarv]

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 :

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

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 :

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

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 :

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

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 :

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

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 :

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

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 :

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

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

[punkoff]

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.

[HadanMarv]

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 :

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

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

- 2 eme calcul pour les autres

Code :

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

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.

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

[vmolines]

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.

[punkoff]

Bonjour,

On y est presque :

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
 
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

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.

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 :

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

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 :

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

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.

[HadanMarv]

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

[punkoff]

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 :

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

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