Une requête un peu subtile

Maljuna Kris · 23-04-2012 20:03:57

Saluton,
Voici la problématique :
Soit 3 tables (pour l'instant je travaille sous SQLite):

Bourses (An, Echelon, Montant) PK {An+Echelon}
Jeu d'essai :
2011,0,0
2011,1,1606
2011,2,2419
2011,3,3100
2011,4,3779
2011,5,4339
2011,6,4600
Bareme (An,PCG,Echelon,Plafond) PK {An+PCG+Echelon}
et son jeu d'essai
2011,0,0,33100
2011,1,0,36760
2011,2,0,40450
2011,3,0,44120
2011,4,0,47800
2011,5,0,51480
2011,6,0,55150
2011,7,0,58830
2011,8,0,62510
2011,9,0,66180
2011,10,0,69860
2011,11,0,73540
2011,12,0,77210
2011,13,0,80890
2011,14,0,84560
2011,15,0,88250
2011,16,0,91920
2011,17,0,95610
2011,0,1,22500
2011,1,1,25000
2011,2,1,27500
2011,3,1,30000
2011,4,1,32500
2011,5,1,35010
2011,6,1,37510
2011,7,1,40010
2011,8,1,42510
2011,9,1,45000
2011,10,1,47510
2011,11,1,50010
2011,12,1,52500
2011,13,1,55000
2011,14,1,57520
2011,15,1,60010
2011,16,1,62510
2011,17,1,65010
2011,0,2,18190
2011,1,2,20210
2011,2,2,22230
2011,3,2,24250
2011,4,2,26270
2011,5,2,28300
2011,6,2,30320
2011,7,2,32340
2011,8,2,34360
2011,9,2,36380
2011,10,2,38400
2011,11,2,40410
2011,12,2,42430
2011,13,2,44450
2011,14,2,46480
2011,15,2,48500
2011,16,2,50520
2011,17,2,52540
2011,0,3,16070
2011,1,3,17850
2011,2,3,19640
2011,3,3,21430
2011,4,3,23210
2011,5,3,25000
2011,6,3,26770
2011,7,3,28560
2011,8,3,30350
2011,9,3,32130
2011,10,3,33920
2011,11,3,35710
2011,12,3,37490
2011,13,3,39280
2011,14,3,41050
2011,15,3,42840
2011,16,3,44630
2011,17,3,46410
2011,0,4,13990
2011,1,4,15540
2011,2,4,17100
2011,3,4,18640
2011,4,4,20200
2011,5,4,21760
2011,6,4,23310
2011,7,4,24860
2011,8,4,26420
2011,9,4,27970
2011,10,4,29520
2011,11,4,31090
2011,12,4,32630
2011,13,4,34180
2011,14,4,35750
2011,15,4,37300
2011,16,4,38840
2011,0,5,11950
2011,17,4,40400
2011,1,5,13280
2011,2,5,14600
2011,3,5,15920
2011,4,5,17250
2011,5,5,18580
2011,6,5,19910
2011,7,5,21240
2011,8,5,22560
2011,9,5,23890
2011,10,5,25220
2011,11,5,26540
2011,12,5,27870
2011,13,5,29200
2011,14,5,30530
2011,15,5,31860
2011,16,5,33190
2011,17,5,34510
2011,0,6,7540
2011,1,6,8370
2011,2,6,9220
2011,3,6,10050
2011,4,6,10880
2011,5,6,11730
2011,6,6,12570
2011,7,6,13410
2011,8,6,14240
2011,9,6,15080
2011,10,6,15910
2011,11,6,16750
2011,12,6,17590
2011,13,6,18420
2011,14,6,19270
2011,15,6,20110
2011,16,6,20940
2011,17,6,21780
Dossiers(An,IdEtudiant,Distance,Fratrie,FratUniv,MBG) PK {An+IdEtudiant}
et son jeu d'essai :
1,2011,2,8,6,40479
2,2011,2,0,0,21653
3,2011,1,2,2,17198
4,2011,2,6,2,39428
5,2011,2,2,2,65443

Bien, l'équivalent de PCG dans la table Dossiers s'obtient par la somme des trois colonnes : Distance+Fratrie+FratUniv

Je cherche à faire une requête qui me retourne, pour tous les dossiers 2011, l'échelon de bourse et le montant de bourse correspondant aux points de charges (PCG) du dossier et au MBG du dossier, ou NULL, pour ces deux colonnes, si aucun échelon ne correspond dans le barème.

Dans le jeu d'essai, #1 qui a (2+8+6)=16 PCG et 40479 de MBG doit se voir attribuer l'échelon 3 soit 3100 puisqu'avec un PCG de 16 il présente un MBG <=44630, tandis que #5 avec un MBG de 65443 pour (2+2+2)=6 PCG dépasse le seuil 55150 et n'a donc pas de correspondance d'échelon dans le barème.

J'ai essayé de procéder comme ça :

SELECT D.An,D.Id,D.TPC,B.Echelon,B.Montant

FROM (SELECT E.An,E.Id,E.Distance+E.Fratrie+E.FratSup AS TPC,

            (SELECT P.Echelon FROM P PCS 

             WHERE P.An=E.An

               AND P.PCG=(E.Distance+E.Fratrie+E.FratSup)

               AND P.Plafond >= E.MBG

             ORDER BY P.Plafond DESC LIMIT 1) AS Echelon) 

      FROM Dossiers AS E) AS D

LEFT JOIN Bourses ON B.An=D.An AND D.Echelon=B.Echelon

ORDER BY D.An, D.Id

Mais je me fait jeter par la syntaxe.
Avez-vous une proposition de requête à me soumettre ?
Merci.

Maljuna Kris · 23-04-2012 22:21:24

Bon, désolé pour le dérangement, j'ai trouvé:

SELECT D.An,D.Id,D.TPC,B.Echelon,B.Montant

    FROM (SELECT E.An,E.Id,E.Distance+E.Fratrie+E.FratSup AS TPC,

                (SELECT MAX(P.Echelon) FROM PCS P

                 WHERE P.An=E.An

                   AND P.PCG=(E.Distance+E.Fratrie+E.FratSup)

                   AND P.Plafond >= E.MBG) AS Echelon

          FROM Dossiers AS E) AS D

    LEFT JOIN Bourses B ON B.An=D.An AND D.Echelon=B.Echelon

    ORDER BY D.An, D.Id

qui retourne bien

SQLite a écrit :

2011,1,16,3,3100
2011,2,2,2,2419
2011,3,5,5,4339
2011,4,10,1,1606
2011,5,6,NULL,NULL

Jc · 23-04-2012 23:05:12

Bonsoir,

Juste une petite question, tu as beaucoup de lignes en prod? en fait j'ai tellement un doute sur la pertinence sur ton choix de vectorisation d'index, que je pense qu'on ne doit pas être loin du full scan sur P dans ton plan de requête
EDIT: Sinon je suis déçu d'être arrivé après que tu aies trouvé la solution toi même^^.

Dernière modification par Jc (23-04-2012 23:10:06)

Maljuna Kris · 24-04-2012 04:28:10

En fait c'est pour remplacer, dans une école d'ingénieurs, un petit utilitaire qui tourne sous Excel avec ce genre de formule :

=EQUIV(A2;INDIRECT(ADRESSE(E2+2;2;4;1;"grille échelon bourse")):INDIRECT(ADRESSE(E2+2;8;4;1;"grille échelon bourse"));-1)-1

pour trouver l'échelon de bourse dans une matrice de 18 lignes de 7 colonnes
puis :

=SI(ESTNA(F2);"Non boursier";RECHERCHE(F2;$'Montant bourse'.C$8:C$15;$'Montant bourse'.D$8:D$15))

pour trouver le montant de la bourse dans un barème.
Ensuite ça sert juste à faire de la notification par publipostage.
Il n'y a que quelques centaines d'étudiants et un barème par an. Et on ne garde que 7 ans d'historique dans la base, sachant qu'il n'y a que les deux dernières années en mode INSERT/DELETE/UPDATE les autres années sont juste en SELECT.
P ne contiendra donc, au pire, que 7*18*7 = 882 lignes.(Echelons 0 à 7, Total de points de charge de 0 à 17, pour les années N-6 à N).
Et ce sera en mono-utilisateur pour faire les notifications avec FPDF.
Il s'agit d'un minibus, pas d'un TGV.

PHP|Débutant :: Forums

Advertisement

#1 23-04-2012 20:03:57

Une requête un peu subtile

#2 23-04-2012 22:21:24

Re : Une requête un peu subtile

#3 23-04-2012 23:05:12

Re : Une requête un peu subtile

#4 24-04-2012 04:28:10

Re : Une requête un peu subtile

Pied de page des forums