Jointure sur valeur la plus proche

Version imprimable

Voir 40 message(s) de cette discussion en une page

1 pièce(s) jointe(s)

Voici le jeu de données :

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
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
-- Creatable MEASURE
CREATE TABLE "MEASURE" (	
	"MEASURE_ID" NUMBER(19,0)
	,"EVENT_ID" NUMBER(19,0) 
	,"INDEX_MEASURE" NUMBER(19,0)
	,"POS_MDM" NUMBER(19,7) 
	,"LANE" NUMBER(19,0)
	,"TRACK" NUMBER(19,0)
	,"NUM_ELT" NUMBER(19,0)
	,"NUM_BPU" NUMBER(19,0) 
	,"MEASURE_CODE" VARCHAR2(100)
	,"VALUE" NUMBER(19,3)
	,"REPORT_ID" NUMBER(19,0) 
	,"USER_CREATION" VARCHAR2(100)  
	,"USER_MODIFICATION" VARCHAR2(100) 
	,"CREATION_DATE" TIMESTAMP (6) 
	,"MODIFICATION_DATE" TIMESTAMP (6) 
	);
 
-- Creatable SHEET_EVENT
CREATE TABLE "SHEET_EVENT" (	
	 "SHEET_EVENT_ID" NUMBER(19,0)
	,"SHEET_ID" NUMBER(19,0)
	,"EVENT_ID" NUMBER(19,0)
	,"REPORT_ID" NUMBER(19,0)
	,"USER_CREATION" VARCHAR2(100)
	,"USER_MODIFICATION" VARCHAR2(100)
	,"CREATION_DATE" TIMESTAMP (6)
	,"MODIFICATION_DATE" TIMESTAMP (6)
	)
 
-- Insert danss la table SHEET_EVENT
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(24830796, 5079, 395314, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:36:18.788053', TIMESTAMP '2023-04-28 04:36:18.788053');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(24830797, 5082, 395315, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:36:18.788523', TIMESTAMP '2023-04-28 04:36:18.788523');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(24830798, 5085, 395316, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:36:18.788991', TIMESTAMP '2023-04-28 04:36:18.788991');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(24830799, 5088, 395317, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:36:18.789471', TIMESTAMP '2023-04-28 04:36:18.789471');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(24830800, 5091, 395318, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:36:18.789884', TIMESTAMP '2023-04-28 04:36:18.789884');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(24830801, 5094, 395319, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:36:18.790392', TIMESTAMP '2023-04-28 04:36:18.790392');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(24830802, 5097, 395320, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:36:18.791197', TIMESTAMP '2023-04-28 04:36:18.791197');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(24830803, 5100, 395321, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:36:18.791996', TIMESTAMP '2023-04-28 04:36:18.791996');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138920, 5077, 703451, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.366493', TIMESTAMP '2023-04-28 04:39:36.366493');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138921, 5078, 703452, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.366871', TIMESTAMP '2023-04-28 04:39:36.366871');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138922, 5079, 703453, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.367604', TIMESTAMP '2023-04-28 04:39:36.367604');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138923, 5080, 703454, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.368102', TIMESTAMP '2023-04-28 04:39:36.368102');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138924, 5081, 703455, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.368571', TIMESTAMP '2023-04-28 04:39:36.368571');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138925, 5082, 703456, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.369002', TIMESTAMP '2023-04-28 04:39:36.369002');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138926, 5083, 703457, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.369460', TIMESTAMP '2023-04-28 04:39:36.369460');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138927, 5084, 703458, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.369863', TIMESTAMP '2023-04-28 04:39:36.369863');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138928, 5085, 703459, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.370289', TIMESTAMP '2023-04-28 04:39:36.370289');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138929, 5086, 703460, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.370732', TIMESTAMP '2023-04-28 04:39:36.370732');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138930, 5087, 703461, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.371191', TIMESTAMP '2023-04-28 04:39:36.371191');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138931, 5088, 703462, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.371602', TIMESTAMP '2023-04-28 04:39:36.371602');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138932, 5089, 703463, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.372181', TIMESTAMP '2023-04-28 04:39:36.372181');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138933, 5090, 703464, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.372658', TIMESTAMP '2023-04-28 04:39:36.372658');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138934, 5091, 703465, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.373075', TIMESTAMP '2023-04-28 04:39:36.373075');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138935, 5092, 703466, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.373539', TIMESTAMP '2023-04-28 04:39:36.373539');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138936, 5093, 703467, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.374209', TIMESTAMP '2023-04-28 04:39:36.374209');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138937, 5094, 703468, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.374629', TIMESTAMP '2023-04-28 04:39:36.374629');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138938, 5095, 703469, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.375254', TIMESTAMP '2023-04-28 04:39:36.375254');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138939, 5096, 703470, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.375990', TIMESTAMP '2023-04-28 04:39:36.375990');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138940, 5097, 703471, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.376416', TIMESTAMP '2023-04-28 04:39:36.376416');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138941, 5098, 703472, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.376887', TIMESTAMP '2023-04-28 04:39:36.376887');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138942, 5099, 703473, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.378021', TIMESTAMP '2023-04-28 04:39:36.378021');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138943, 5100, 703474, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.378696', TIMESTAMP '2023-04-28 04:39:36.378696');
INSERT INTO SHEET_EVENT
(SHEET_EVENT_ID, SHEET_ID, EVENT_ID, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(25138944, 5101, 703475, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-28 04:39:36.379543', TIMESTAMP '2023-04-28 04:39:36.379543');
 
 
-- Insert dans la table MEASURE 
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464856, 703451, 3747955, 3487.583287, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.673, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.256413', TIMESTAMP '2023-04-27 18:08:50.256413');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464860, 703452, 3747959, 3488.270082, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.258225', TIMESTAMP '2023-04-27 18:08:50.258225');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464864, 703453, 3747963, 3488.956877, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.260935', TIMESTAMP '2023-04-27 18:08:50.260935');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(165590443, 395314, 1873542, 3489.459453, 1, 3, 1, 0, 'IridesPosition_DevPosCDmm', 0.366, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 17:41:09.667544', TIMESTAMP '2023-04-27 17:41:09.667544');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464868, 703454, 3747967, 3489.643551, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.673, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.265150', TIMESTAMP '2023-04-27 18:08:50.265150');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464872, 703455, 3747971, 3490.330586, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.673, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.266943', TIMESTAMP '2023-04-27 18:08:50.266943');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464876, 703456, 3747975, 3491.017501, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.268970', TIMESTAMP '2023-04-27 18:08:50.268970');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(165590450, 395315, 1873549, 3491.462675, 1, 3, 1, 0, 'IridesPosition_DevPosCDmm', 0.366, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 17:41:09.675278', TIMESTAMP '2023-04-27 17:41:09.675278');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464880, 703457, 3747979, 3491.704536, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.673, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.272276', TIMESTAMP '2023-04-27 18:08:50.272276');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464884, 703458, 3747983, 3492.39121, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.274863', TIMESTAMP '2023-04-27 18:08:50.274863');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464888, 703459, 3747987, 3493.077885, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.673, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.277148', TIMESTAMP '2023-04-27 18:08:50.277148');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(165590457, 395316, 1873556, 3493.465776, 1, 3, 1, 0, 'IridesPosition_DevPosCDmm', 0.366, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 17:41:09.680578', TIMESTAMP '2023-04-27 17:41:09.680578');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464892, 703460, 3747991, 3493.76444, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.673, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.281042', TIMESTAMP '2023-04-27 18:08:50.281042');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464896, 703461, 3747995, 3494.451234, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.673, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.282996', TIMESTAMP '2023-04-27 18:08:50.282996');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464900, 703462, 3747999, 3495.138149, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.284954', TIMESTAMP '2023-04-27 18:08:50.284954');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(165590464, 395317, 1873563, 3495.468757, 1, 3, 1, 0, 'IridesPosition_DevPosCDmm', 0.366, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 17:41:09.686509', TIMESTAMP '2023-04-27 17:41:09.686509');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464904, 703463, 3748003, 3495.825184, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.673, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.291526', TIMESTAMP '2023-04-27 18:08:50.291526');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464908, 703464, 3748007, 3496.512339, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.297495', TIMESTAMP '2023-04-27 18:08:50.297495');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464912, 703465, 3748011, 3497.199134, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.300082', TIMESTAMP '2023-04-27 18:08:50.300082');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(165590471, 395318, 1873570, 3497.472098, 1, 3, 1, 0, 'IridesPosition_DevPosCDmm', 0.236, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 17:41:09.690621', TIMESTAMP '2023-04-27 17:41:09.690621');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464916, 703466, 3748015, 3497.885808, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.303867', TIMESTAMP '2023-04-27 18:08:50.303867');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464920, 703467, 3748019, 3498.572483, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.307197', TIMESTAMP '2023-04-27 18:08:50.307197');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464924, 703468, 3748023, 3499.259158, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.309528', TIMESTAMP '2023-04-27 18:08:50.309528');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(165590478, 395319, 1873577, 3499.47532, 1, 3, 1, 0, 'IridesPosition_DevPosCDmm', 0.236, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 17:41:09.697514', TIMESTAMP '2023-04-27 17:41:09.697514');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464928, 703469, 3748027, 3499.946072, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.312700', TIMESTAMP '2023-04-27 18:08:50.312700');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464932, 703470, 3748031, 3500.632987, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.315593', TIMESTAMP '2023-04-27 18:08:50.315593');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464936, 703471, 3748035, 3501.319902, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.319064', TIMESTAMP '2023-04-27 18:08:50.319064');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(165590485, 395320, 1873584, 3501.478421, 1, 3, 1, 0, 'IridesPosition_DevPosCDmm', 0.366, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 17:41:09.705431', TIMESTAMP '2023-04-27 17:41:09.705431');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464940, 703472, 3748039, 3502.006817, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.321421', TIMESTAMP '2023-04-27 18:08:50.321421');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464944, 703473, 3748043, 3502.693732, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.543, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.323889', TIMESTAMP '2023-04-27 18:08:50.323889');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464948, 703474, 3748047, 3503.380526, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.673, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.328996', TIMESTAMP '2023-04-27 18:08:50.328996');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(165590492, 395321, 1873591, 3503.481522, 1, 3, 1, 0, 'IridesPosition_DevPosCDmm', 0.236, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 17:41:09.710236', TIMESTAMP '2023-04-27 17:41:09.710236');
INSERT INTO MEASURE
(MEASURE_ID, EVENT_ID, INDEX_MEASURE, POS_MDM, LANE, TRACK, NUM_ELT, NUM_BPU, MEASURE_CODE, VALUE, REPORT_ID, USER_CREATION, USER_MODIFICATION, CREATION_DATE, MODIFICATION_DATE)
VALUES(167464952, 703475, 3748051, 3504.067321, 1, 3, 1, 0, 'PunchPosition_DevPosCDmm', 0.673, 481, 'linea_appli', 'linea_appli', TIMESTAMP '2023-04-27 18:08:50.331389', TIMESTAMP '2023-04-27 18:08:50.331389');

Je rajoute un tableau montrant quelles lignes iridesPosition sont à additionner aux lignes PunchPosition :

Pièce jointe 641553

Et voici la requête qui permet d'obtenir ce tableau de données :

Code:

1
2
3
4
SELECT se.SHEET_EVENT_ID, se.SHEET_ID , m.*
FROM MEASURE m 
LEFT JOIN SHEET_EVENT se ON m.REPORT_ID = se.REPORT_ID AND m.EVENT_ID = se.EVENT_ID
ORDER BY POS_MDM

Avec un LEFT JOIN, un CASE permet de distinguer la présence de lignes correspondant au critère et si vous mettez le row_number() dans un sous-CASE pour calculer la nouvelle valeur,
cela peut être plus simple, les lignes utiles seront celles où la nouvelle valeur est non nulle.

En attendant un jeu de tests et en utilisant que les colonnes utiles :

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
with measures(report_id, nf, posmdm, code, value ) as (
    select 481, 5079, 3498.459453, 'IridesPosition_DevPosCDmm', 0.366 from dual union all
    select 481, 5093, 3498.572483, 'PunchPosition_DevPosCDmm', 0.543 from dual union all
    select 481, 5093, 3499.47532, 'IridesPosition_DevPosCDmm', 0.236 from dual union all
    select 481, 5093, 3501.478421, 'IridesPosition_DevPosCDmm', 0.366 from dual union all
    select 481, 5093, 3503.481522, 'IridesPosition_DevPosCDmm', 0.236 from dual union all
    select 481, 5092, 3497.885808, 'PunchPosition_DevPosCDmm', 0.543 from dual union all
    select 481, 5092, 3497.472098, 'IridesPosition_DevPosCDmm', 0.236 from dual -- union all
)
select report_id, nf, posmdm, code, value, newvalue 
from (
    select mp.report_id, mp.nf, mp.code, mp.posmdm, abs(mp.posmdm - mi.posmdm) as diff, mp.value, mi.value as toadd,
        case when 
            mi.report_id is null then mp.value
        else 
            case when row_number() over(partition by mp.report_id, mp.nf order by abs(mp.posmdm - mi.posmdm)) = 1
            then mp.value + mi.value
            end
        end as newvalue
    from measures mp
        left join measures mi on mp.report_id = mi.report_id
            and mp.nf = mi.nf and mp.code = 'PunchPosition_DevPosCDmm' and mi.code = 'IridesPosition_DevPosCDmm'
)
where newvalue is not null
;

Code:

1
2
3
4
5
6
7
8
481    5079    3498,459453    IridesPosition_DevPosCDmm    0,366    0,366
481    5092    3497,885808    PunchPosition_DevPosCDmm    0,543    0,779
481    5092    3497,472098    IridesPosition_DevPosCDmm    0,236    0,236
481    5093    3498,572483    PunchPosition_DevPosCDmm    0,543    0,779
481    5093    3499,47532    IridesPosition_DevPosCDmm    0,236    0,236
481    5093    3501,478421    IridesPosition_DevPosCDmm    0,366    0,366
481    5093    3503,481522    IridesPosition_DevPosCDmm    0,236    0,236

19/07/2023, 11h00
jordan63000

J'ai posté un message à 10h34 avec le jeu de test :)

Merci pour cette solution je testerai cette solution également :)

Essayez ainsi :

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
    SELECT s.SHEET_EVENT_ID
         , s.SHEET_ID
         , case
             when m.measure_code = 'IridesPosition_DevPosCDmm'
             then 0
             when coalesce(abs(m.POS_MDM - lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
                < coalesce(abs(m.POS_MDM - lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
             then lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.MEASURE_ID end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
             else lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.MEASURE_ID end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
           end as MEASURE_ID_selected
         , m.VALUE
         + case
             when m.measure_code = 'IridesPosition_DevPosCDmm'
             then 0
             when coalesce(abs(m.POS_MDM - lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
                < coalesce(abs(m.POS_MDM - lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
             then lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.VALUE end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
             else lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.VALUE end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
           end as VALUE_CALC
         , m.*
      FROM MEASURE     m 
 LEFT JOIN SHEET_EVENT s  ON s.REPORT_ID = m.REPORT_ID
                         AND s.EVENT_ID  = m.EVENT_ID
  ORDER BY m.POS_MDM;

La solution de JeitEmgie est assez rapide mais ne renvoie pas les bon résultats.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
with MESURE as (  
	SELECT 	se.SHEET_ID, m.*	
	FROM MEASURE m 	
	LEFT JOIN SHEET_EVENT se ON se.REPORT_ID = m.REPORT_ID AND se.EVENT_ID = m.EVENT_ID 
	WHERE m.REPORT_ID = 481
	AND MEASURE_CODE IN ('PunchPosition_DevPosCDmm','IridesPosition_DevPosCDmm')
	AND lane = 1
	AND TRACK = 3
	)
SELECT report_id, SHEET_ID, POS_MDM, MEASURE_CODE, value, newvalue 
FROM (
    SELECT mp.report_id, mp.SHEET_ID, mp.MEASURE_CODE, mp.POS_MDM, abs(mp.POS_MDM - mi.POS_MDM) as diff, mp.value, mi.value as toadd,
        CASE WHEN mi.report_id IS NULL THEN mp.value
			 WHEN row_number() over(PARTITION BY  mp.report_id, mp.SHEET_ID ORDER BY abs(mp.POS_MDM - mi.POS_MDM)) = 1 THEN mp.value + mi.value
      		 END as newvalue
    FROM MESURE mp
        LEFT JOIN MESURE mi on mp.report_id = mi.report_id AND mp.SHEET_ID = mi.SHEET_ID AND mp.MEASURE_CODE  = 'PunchPosition_DevPosCDmm' AND mi.MEASURE_CODE = 'IridesPosition_DevPosCDmm'
)
where newvalue is not NULL
AND MEASURE_CODE = 'PunchPosition_DevPosCDmm'
;

La solution de Waldar renvoie des résultats corrects :) Toutefois l'"exécution" est trop longue. C'est assez étrange puisque c'est le fetched qui est très long, plus de 5minute pour ramener les 22000 lignes

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
    SELECT s.SHEET_EVENT_ID
         , s.SHEET_ID
         , case
             when m.measure_code = 'IridesPosition_DevPosCDmm'
             then 0
             when coalesce(abs(m.POS_MDM - lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
                < coalesce(abs(m.POS_MDM - lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
             then lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.MEASURE_ID end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
             else lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.MEASURE_ID end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
           end as MEASURE_ID_selected
         , m.VALUE
         + case
             when m.measure_code = 'IridesPosition_DevPosCDmm'
             then 0
             when coalesce(abs(m.POS_MDM - lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
                < coalesce(abs(m.POS_MDM - lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
             then lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.VALUE end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
             else lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.VALUE end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
           end as VALUE_CALC
      FROM MEASURE     m 
 LEFT JOIN SHEET_EVENT s  ON s.REPORT_ID = m.REPORT_ID
                         AND s.EVENT_ID  = m.EVENT_ID
 	WHERE m.REPORT_ID = 481
	AND m.MEASURE_CODE IN ('PunchPosition_DevPosCDmm','IridesPosition_DevPosCDmm')
	AND m.lane = 1
	AND m.TRACK = 3
  ORDER BY m.POS_MDM;

21/07/2023, 11h09
Waldar

Si c'est le fetched qui est long, c'est "simplement" du trafic réseau.

Pour tester la performance de la requête, vous pouvez faire un create table test as select ... pctfree 0;
21/07/2023, 11h24
Waldar

Une autre question en passant, est-ce que la jointure externe est nécessaire ?

Citation:

Envoyé par jordan63000

La solution de JeitEmgie est assez rapide mais ne renvoie pas les bon résultats.

parce qu'elle ne considère que les sheet_id égaux, essayez en enlevant le

Code:

AND mp.sheet_id = mi.sheet_id

du LEFT JOIN.

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
WITH mesure as (  
    SELECT     se.sheet_id, m.*    
    FROM measure m     
    LEFT JOIN SHEET_EVENT se ON se.REPORT_ID = m.REPORT_ID AND se.EVENT_ID = m.EVENT_ID 
    WHERE m.REPORT_ID = 481
    AND MEASURE_CODE IN ('PunchPosition_DevPosCDmm','IridesPosition_DevPosCDmm')
    AND lane = 1
    AND TRACK = 3
    )
SELECT m.report_id, s.sheet_event_id, m.sheet_id, newvalue 
FROM (
    SELECT mp.report_id, mp.sheet_id, mp.measure_code, mp.event_id,
        mp.pos_mdm, abs(mp.POS_MDM - mi.POS_MDM) as diff, mp.value, mi.value as toadd,
        row_number() OVER(PARTITION BY mp.report_id, mp.measure_id ORDER BY abs(mp.pos_mdm - mi.pos_mdm)) AS rn,
        CASE 
            WHEN row_number() OVER(PARTITION BY mp.report_id, mp.sheet_id ORDER BY abs(mp.pos_mdm - mi.pos_mdm)) = 1 THEN
            mi.measure_id END measure_id_selected,
        CASE WHEN mi.report_id IS NULL 
                THEN mp.value
             WHEN row_number() OVER(PARTITION BY mp.report_id, mp.sheet_id ORDER BY ABS(mp.pos_mdm - mi.pos_mdm)) = 1 
                THEN mp.value + mi.value
               END As newvalue
    FROM mesure mp
        LEFT JOIN mesure mi on mp.report_id = mi.report_id 
            /*AND mp.sheet_id = mi.sheet_id */ AND mp.measure_code  = 'PunchPosition_DevPosCDmm' AND mi.measure_code = 'IridesPosition_DevPosCDmm'
) m
LEFT JOIN sheet_event s  ON s.report_id = m.report_id
                         AND s.event_id  = m.event_id
WHERE newvalue IS NOT NULL
ORDER BY sheet_id
;

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
report_id  sheet_event_id sheet_id newvalue
481    25138920    5077    1,039
481    25138921    5078    0,909
481    25138922    5079    0,909
481    24830796    5079    0,366
481    25138923    5080    1,039
481    25138924    5081    1,039
481    25138925    5082    0,909
481    24830797    5082    0,366
481    25138926    5083    1,039
481    25138927    5084    0,909
481    25138928    5085    1,039
481    24830798    5085    0,366
481    25138929    5086    1,039
481    25138930    5087    1,039
481    25138931    5088    0,909
481    24830799    5088    0,366
481    25138932    5089    1,039
481    25138933    5090    0,779
481    25138934    5091    0,779
481    24830800    5091    0,236
481    25138935    5092    0,779
481    25138936    5093    0,779
481    25138937    5094    0,779
481    24830801    5094    0,236
481    25138938    5095    0,779
481    25138939    5096    0,909
481    25138940    5097    0,909
481    24830802    5097    0,366
481    25138941    5098    0,909
481    25138942    5099    0,779
481    25138943    5100    0,909
481    24830803    5100    0,236
481    25138944    5101    0,909

Waldar :
- Je comprends mais les autres requêtes fetched beaucoup plus vite, en moyenne en 40sec j'ai mes données.
- Pour le left join oui il est obligatoire pour ramener le numéro de feuille qui n'est pas présent dans la table mesure
- j'ai lancé votre commande je ne sais pas trop ce que c'est censé faire mais pas au bout de plusieurs minutes pas de résultat :

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
 
create table test2 pctfree 0 as  SELECT *
FROM (
    SELECT s.SHEET_EVENT_ID
         , s.SHEET_ID
         , case
             when m.measure_code = 'IridesPosition_DevPosCDmm'
             then 0
             when coalesce(abs(m.POS_MDM - lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
                < coalesce(abs(m.POS_MDM - lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
             then lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.MEASURE_ID end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
             else lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.MEASURE_ID end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
           end as MEASURE_ID_selected
         , m.VALUE
         + case
             when m.measure_code = 'IridesPosition_DevPosCDmm'
             then 0
             when coalesce(abs(m.POS_MDM - lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
                < coalesce(abs(m.POS_MDM - lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.POS_MDM end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)), 9999999)
             then lag (case m.measure_code when 'IridesPosition_DevPosCDmm' then m.VALUE end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
             else lead(case m.measure_code when 'IridesPosition_DevPosCDmm' then m.VALUE end ignore nulls) over(partition by m.REPORT_ID order by m.POS_MDM asc)
           end as VALUE_CALC
      FROM MEASURE     m 
 LEFT JOIN SHEET_EVENT s  ON s.REPORT_ID = m.REPORT_ID
                         AND s.EVENT_ID  = m.EVENT_ID
 	WHERE m.REPORT_ID = 481
	AND m.MEASURE_CODE IN ('PunchPosition_DevPosCDmm','IridesPosition_DevPosCDmm')
	AND m.lane = 1
	AND m.TRACK = 3
)
WHERE MEASURE_ID_selected != 0;

JeitEmgie : Yes c'était bien ça, j'ai toutefois rajouté un Between au niveau de la jointure sinon le produit cartésien est monstrueux. Le temps d'exécution est en dessous de la minute sensiblement égal aux premières propositions du post, merci :)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
with MESURE as (  
	SELECT 	se.SHEET_ID, m.*	
	FROM MEASURE m 	
	LEFT JOIN SHEET_EVENT se ON se.REPORT_ID = m.REPORT_ID AND se.EVENT_ID = m.EVENT_ID 
	WHERE m.REPORT_ID = 481
	AND MEASURE_CODE IN ('PunchPosition_DevPosCDmm','IridesPosition_DevPosCDmm')
	AND lane = 1
	AND TRACK = 3
	)
SELECT report_id, SHEET_ID, POS_MDM, MEASURE_CODE, value, newvalue 
FROM (
    SELECT mp.report_id, mp.SHEET_ID, mp.MEASURE_CODE, mp.POS_MDM, abs(mp.POS_MDM - mi.POS_MDM) as diff, mp.value, mi.value as toadd,
        CASE WHEN mi.report_id IS NULL THEN mp.value
			 WHEN row_number() over(PARTITION BY  mp.report_id, mp.SHEET_ID ORDER BY abs(mp.POS_MDM - mi.POS_MDM)) = 1 THEN mp.value + mi.value
      		 END as newvalue
    FROM MESURE mp
        LEFT JOIN MESURE mi 
        	on mp.report_id = mi.report_id 
        	AND mp.MEASURE_CODE  = 'PunchPosition_DevPosCDmm' AND mi.MEASURE_CODE = 'IridesPosition_DevPosCDmm'
        	AND mi.POS_MDM BETWEEN mp.POS_MDM-10 AND mp.POS_MDM+10
)
where newvalue is not NULL
AND MEASURE_CODE = 'PunchPosition_DevPosCDmm'

21/07/2023, 17h35
JeitEmgie

Citation:

Envoyé par jordan63000

JeitEmgie : Yes c'était bien ça, j'ai toutefois rajouté un Between au niveau de la jointure sinon le produit cartésien est monstrueux. Le temps d'exécution est en dessous de la minute sensiblement égal aux premières propositions du post, merci :)

Vous pouvez encore optimiser en diminuant ce delta, apparement 1 devrait suffire
et le LEFT JOIN ne sert à rien : s'il n'y a pas de row le mi.pos_mdm est NULL donc ne participe pas au calcul du row_number() sur abs(delta qui est donc null)...
21/07/2023, 21h39
jordan63000

Yes je n'ai pas encore assez de données pour être suffisamment sur que 1 suffisse mais une fois que j'aurais accès à l'entièreté de la DB je pourrais vérifier et réduire le delta du BETWEEN.

Et ok ca marche pour le Left Join.

Merci en tout cas :)

Voir 40 message(s) de cette discussion en une page