Chainage SQL sur des informations implicites

Version imprimable

02/03/2012, 18h03
Delioneras

1 pièce(s) jointe(s)

Chainage SQL sur des informations implicites

Bonjour à tous et à toutes,

Je fais face à un problème de chainage de données en sql, et je n'arrive pas à comprendre comment lier mes données.

CONTEXTE
Voici un descriptif simplifié et très rapide d'une partie d'un système d'informations:

- AGENT (REF_AGENT, NOM, PRENOM, ...)
- TRANSPORT (REF_TRSP, DATE_TRSP, ..., #REF_AGENT, #REF_SRV_DEP, #REF_SRV_ARR)
- SERVICE (REF_SRV, Etage_SRV, ...)

Pour faire simple, un agent peut faire plusieurs transports, d'un service à un autre.
De même, un service peut être le point de départ ou d'arrivée de plusieurs transports.

Rien de très compliqué dans ce schéma.
Et pour faire des stats, idem. Le fait de compter ne pose aucun problème, du moins pour ce qui est apparent.

Le problème apparait dès lors que j'essaye de compter une information implicite (et donc non référencée dans la base), à la demande d'un client.

PROBLEME
En gros, un agent a un transport à faire (Transport 1) d'un Service (A) vers un Service (B).
Puis, il doit faire un deuxième transport (Transport 2) d'un Service (C) vers un Service (D).

Dans cet exemple, il y a un troisième transport, le transport intermédiaire (de B vers C) qui correspond juste à la transition d'une affectation à une autre.

C'est ce troisième transport que je souhaite compter. Et là, je bloque.
Ce troisième transport n'est jamais renseigné dans la base, je dois chainer les transports 1 et 2 alors qu'il n'y a pas de lien entre eux.
Ces transports 1 et 2 doivent également être consécutifs (entre une embauche et une débauche par exemple), mais les dates ne sont pas égales.
Et pour le challenge, pour que ce transport intermédiaire soit compté, il faut que l'étage du Service B soit différent du Service C.

EDIT
Si ça peut aider, je mets en pièce-jointe une capture d'écran d'un exemple des transports en question.
Le listing fait état de 14 transports réels.
En les comptant de visu, il y a 13 transports implicites.
FIN EDIT

Je suis sur un Oracle 10gR2, sur Windows. Ceci étant, ma question relève de SQL pur et dur, ça ne relève pas d'un SGBDR (je ne saurais pas le faire non plus sur MS SQL, Sybase, ou MySQL).

Si quelqu'un peut m'aider à y voir plus clair, j'apprécierais beaucoup le coup de main.

Merci d'avance à tous et à toutes,
Cordialement,
Délio
02/03/2012, 22h02
Jean.Cri1

Bonsoir,

Doit on prendre en compte une "tournée" a cheval sur deux dates ? (service de nuit )

Il faut ordonner par datetime croissant par journée et par agent puis compter les cas ou la ligne suivante satisfait les conditions par rapport a la ligne courante.

Je crois avoir vu par ici que cela peut se faire avec overlap ou rollup ( je ne sais plus tres bien je ne fais pas d'oracle )
ou alors avec une table tempo et un identity
ou -si les trajets sont toujours saisis avec une clé incrementée- avec le min de la cle superieur a la cle courante pour le meme agent le meme jour.
04/03/2012, 10h37
Delioneras

Bonjour,

La tournée à cheval est la question que j'avais posé. Ca paraitrait logique, et ce serait surtout plus exact à chiffrer.

Mais on m'a répondu qu'il fallait se concentrer d'abord sur un décompte par jour.

Donc non, pas besoin de calculer sur les entre deux jours :)

Les trajets ne sont pas saisis avec des clés incrémentés, ce sont des clés alphanumériques générées par l'application métier principale (et je ne sais pas quelle est la logique de création de ces clés).

Overlap ou Rollup ? Je connais pas. Merci pour la piste, je vais fouiller ça.

Cordialement,
Délio
05/03/2012, 14h43
Jean.Cri1

over partition

en fait c'est over partition,

voila un exemple
05/03/2012, 15h43
Waldar

Les données sous forme d'image c'est joli mais ce n'est pas pratique pour les manipuler.

Préparez un jeu d'essai avec ces données-là, en séparant bien les trois tables que vous présentez au départ.

Bonjour,

Oui, pardon pour le script. Le voilà.

Script de création des trois tables, et d'insertions des données d'exemple.

/!\ SCRIPT FAIT POUR ORACLE

Par rapport à la question d'origine, la seule chose que je ne sais pas faire, c'est comparer une ligne à sa suivante concernant l'étage du service:
- Si Etage suivant différent => On compte
- Si Etage suivant égal => On ignore

Après, quand il s'agit de compter, regrouper, ou autre, ça ne me pose aucun problème.

=========================================================

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
 
/*
 * CREATION DE LA TABLE AGENT
 */
CREATE TABLE AGENT
(
	CODE_AGENT   VARCHAR2(3)  NOT NULL,
	NOM_AGENT    VARCHAR2(15)         ,
	PRENOM_AGENT VARCHAR2(15)         ,
	CONSTRAINT PK_CODE_AGENT PRIMARY KEY (CODE_AGENT)
);
 
/*
 * JEU DE DONNEES, TABLE AGENT
 */
INSERT INTO AGENT (CODE_AGENT, NOM_AGENT, PRENOM_AGENT) VALUES ('CBA', 'BAURIN', 'Christian');
 
/*
 * CREATION DE LA TABLE SERVICE
 */
CREATE TABLE SERVICE
(
	CODE_SRV     VARCHAR2(10) NOT NULL,
	ETAGE_SRV    VARCHAR2(5)          ,
	CONSTRAINT PK_CODE_SRV PRIMARY KEY (CODE_SRV)
);
 
/*
 * JEU DE DONNEES, TABLE SERVICE
 */
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('CARDHDS2N' ,'2Nord');
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('CARD2EHGRL','2E'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('CARD2SHGRL','2E'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('CHIVASCBO' ,'RB'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('CSORLGRL'  ,'RH'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('CTCVBO'    ,'RH'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('CTCV1EHGRL','1E'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('CTCV1SHGRL','1E'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('ECHCARHGRL','RH'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('ECHOGRAPHL','RB'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('ENDOHDJRCB','RB'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('NEUROCHC4E','4E'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('PNEUHDJ3E' ,'3E'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('PNEU3EHGRL','3E'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('PNEU3SHGRL','3E'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('RADISDHGRL','RB'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('REACTCV'   ,'RH'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('SCANNEHGRL','RB'   );
INSERT INTO SERVICE (CODE_SRV, ETAGE_SRV) VALUES ('USICARDIO' ,'RH'   );
 
/*
 * CREATION DE LA TABLE TRANSPORT
 */
CREATE TABLE TRANSPORT
(
	REF_TRSP       VARCHAR2(12) NOT NULL,
	FK_CODE_AGENT  VARCHAR2(3)          ,
	DATE_DEPART    DATE                 ,
	DATE_ARRIVEE   DATE                 ,
	FK_SRV_DEPART  VARCHAR2(10)         ,
	FK_SRV_ARRIVEE VARCHAR2(10)         ,
	CONSTRAINT PK_REF_TRSP PRIMARY KEY (REF_TRSP),
	CONSTRAINT FK_SRV_DEP  FOREIGN KEY (FK_SRV_DEPART) REFERENCES SERVICE (CODE_SRV),
	CONSTRAINT FK_SRV_ARR  FOREIGN KEY (FK_SRV_ARRIVEE) REFERENCES SERVICE (CODE_SRV)
);
 
/*
 * JEU DE DONNEES, TABLE TRANSPORT
 */
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES 
('157/133', 'CBA', 
TO_DATE('01/02/2011 08:01:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 08:07:00', 'dd/mm/yyyy hh24:mi:ss'), 
'NEUROCHC4E', 'CHIVASCBO')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES 
('1261/149', 'CBA', 
TO_DATE('01/02/2011 08:26:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 08:30:00', 'dd/mm/yyyy hh24:mi:ss'), 
'CARDHDS2N', 'HEMODYN')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('1056/163', 'CBA', 
TO_DATE('01/02/2011 09:11:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 09:21:00', 'dd/mm/yyyy hh24:mi:ss'), 
'CTCV1EHGRL', 'CTCVBO')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('1488/59', 'CBA', 
TO_DATE('01/02/2011 09:28:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 09:06:00', 'dd/mm/yyyy hh24:mi:ss'), 
'CTCVBO', 'CTCV1EHGRL')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('270/143', 'CBA', 
TO_DATE('01/02/2011 09:38:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 09:38:00', 'dd/mm/yyyy hh24:mi:ss'), 
'ENDOHDJRCB', 'ENDOCHC5N')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('1000/113', 'CBA', 
TO_DATE('01/02/2011 09:56:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 09:56:00', 'dd/mm/yyyy hh24:mi:ss'), 
'PNEU3SHGRL', 'RMNHGRL')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('947/213', 'CBA', 
TO_DATE('01/02/2011 10:09:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 10:10:00', 'dd/mm/yyyy hh24:mi:ss'), 
'ECHOGRAPHL', 'UTTHC1O')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('354/223', 'CBA', 
TO_DATE('01/02/2011 10:26:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 10:26:00', 'dd/mm/yyyy hh24:mi:ss'), 
'PNEUHDJ3E', 'CHIVASCBO')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('1378/163', 'CBA', 
TO_DATE('01/02/2011 10:42:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 10:42:00', 'dd/mm/yyyy hh24:mi:ss'), 
'CARDHDS2N', 'CSORLGRL')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('678/183', 'CBA', 
TO_DATE('01/02/2011 10:49:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 10:50:00', 'dd/mm/yyyy hh24:mi:ss'), 
'ECHCARHGRL', 'CARDHDS2N')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('1526/49', 'CBA', 
TO_DATE('01/02/2011 11:06:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 11:06:00', 'dd/mm/yyyy hh24:mi:ss'), 
'PNEU3EHGRL', 'SCANNEHGRL')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('94/235', 'CBA', 
TO_DATE('01/02/2011 11:14:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 11:14:00', 'dd/mm/yyyy hh24:mi:ss'), 
'CSORLGRL', 'CARDHDS2N')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('938/169', 'CBA', 
TO_DATE('01/02/2011 11:14:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 11:14:00', 'dd/mm/yyyy hh24:mi:ss'), 
'CTCV1SHGRL', 'REACTCV')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('1499/155', 'CBA', 
TO_DATE('01/02/2011 11:50:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 11:50:00', 'dd/mm/yyyy hh24:mi:ss'), 'REACTCV', 'CTCV1SHGRL')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('591/195', 'CBA', 
TO_DATE('01/02/2011 12:10:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 12:10:00', 'dd/mm/yyyy hh24:mi:ss'), 
'SCANNEHGRL', 'PNEU3EHGRL')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('1654/189', 'CBA', 
TO_DATE('01/02/2011 13:49:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 13:49:00', 'dd/mm/yyyy hh24:mi:ss'), 
'CHIVASCBO', 'CHIVASHS1N')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('1468/165', 'CBA', 
TO_DATE('01/02/2011 14:02:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 14:02:00', 'dd/mm/yyyy hh24:mi:ss'), 
'CTCV1EHGRL', 'RADISDHGRL')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('631/161', 'CBA', 
TO_DATE('01/02/2011 14:24:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 14:24:00', 'dd/mm/yyyy hh24:mi:ss'), 
'USICARDIO', 'CARD2EHGRL')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('706/169', 'CBA', 
TO_DATE('01/02/2011 14:39:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 14:39:00', 'dd/mm/yyyy hh24:mi:ss'), 
'RADISDHGRL', 'CTCV1EHGRL')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('1413/173', 'CBA', 
TO_DATE('01/02/2011 14:40:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 14:08:00', 'dd/mm/yyyy hh24:mi:ss'), 
'CARD2EHGRL', 'USICARDIO')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('428/197', 'CBA', 
TO_DATE('01/02/2011 14:55:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 14:55:00', 'dd/mm/yyyy hh24:mi:ss'), 
'CARD2SHGRL', 'ECHCARHGRL')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('758/189', 'CBA', 
TO_DATE('01/02/2011 15:07:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 15:18:00', 'dd/mm/yyyy hh24:mi:ss'), 
'ECHCARHGRL', 'CARD2EHGRL')
;
INSERT INTO TRANSPORT (REF_TRSP, FK_CODE_AGENT, DATE_DEPART, DATE_ARRIVEE, FK_SRV_DEPART, FK_SRV_ARRIVEE) 
VALUES ('1011/129', 'CBA', 
TO_DATE('01/02/2011 15:18:00', 'dd/mm/yyyy hh24:mi:ss'), 
TO_DATE('01/02/2011 15:18:00', 'dd/mm/yyyy hh24:mi:ss'), 
'CARD2EHGRL', 'CSSTOMAGRL')
;
 
/*
 * VALIDATION DU SCRIPT
 */
 COMMIT;

06/03/2012, 12h54
Delioneras

Solution trouvée.

Il faut utiliser les fonctions Analytic d'Oracle, notamment LEAD ou LAG.

En l'occurrence, LEAD me permet de rajouter dans le résultat de ma ligne courante le résultat de la ligne suivante.

Après quoi, il n'y a plus qu'à compter et mettre quelques restrictions.

Merci pour vos conseils.

En fouillant ce que m'a indiqué Jean.Cri1, j'ai fini par trouver la faq d'Oracle qui traite spécifiquement de ces Analytic Functions:
http://www.orafaq.com/node/55
Si ça peut servir à quelqu'un, tant mieux.

Delioneras
06/03/2012, 13h46
Waldar

Il y a plusieurs erreurs de contraintes dans votre script (des services sont manquants).

Vous devriez vous en sortir en utilisant les fonctions de fenêtrages LEAD et LAG pour comparer des lignes différentes d'un jeu de résultat.

Edit : je n'avais pas vu votre réponse, bravo pour avoir trouvé la solution.
Vous avez un cours ici aussi :
http://lalystar.developpez.com/fonctionsAnalytiques/
06/03/2012, 14h47
Delioneras

Désolé pour les erreurs de script.

J'avais bidouillé un erzatz de résultat en adaptant mes requêtes SQL pour recréer ces tables de manière simplifiée via le tableur excel.

J'ai du me planter, tant pis, je revérifierai à l'occasion.

Merci pour le lien concernant les fonctions analytiques, je garde ça précieusement.