Comment trouver les extrêmes à partir d'intervalles?

**JGLord** · 30/01/2013, 17h55

Bonjour à tous,

Tout d'abord, je ne sais pas trop comment poser ma question pour être clair, alors je vais faire une première tentative, suivi d'un exemple.

J'aurais besoin de votre aide pour construire une requête SQL qui me permettrait de trouver les maximums/minimums de plusieurs intervalles.

À titre d'exemple, ma table se nomme ALL_DATA et son contenu est le suivant:
ID;DATA
01;60
02;60
03;61
04;62
05;61
06;60
07;60
08;61
09;63
10;60
11;59
12;58
13;60

Dans mon contexte, un intervalle se définit par une suite de chiffres qui est supérieure ou inférieure à 60.

Conséquemment, les valeurs associées aux intervalles de ma table ALL_DATA seraient:
1re intervalle:
03;61
04;62
05;61

2e intervalle:
08;61
09;63

3e intervalle
11;59
12;58

Donc, le résultat que j'aimerais obtenir sont les valeurs maximales des intervales >60 et les valeurs minimales des intervalles <60, bref:
04;62
09;63
12;58

Je ne sais pas si c'est possible, mais idéalement j'aimerais pouvoir y arriver sans utiliser des tables temporaires.

Merci d'avance!

**al1_24** · 30/01/2013, 18h57

Pourrais-tu expliquer la règle de sélection des intervalles...

**skuatamad** · 30/01/2013, 19h06

Quelle version d'oracle ?
A partir de 11GR2, voilà une approche (probablement pas la meilleure) à base de WITH récursif :

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
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
SQL>   with ALL_DATA as (
  2  select 1 as id, 60 as data from dual union all
  3  select 2      , 60         from dual union all
  4  select 3      , 61         from dual union all
  5  select 4      , 62         from dual union all
  6  select 5      , 61         from dual union all
  7  select 6      , 60         from dual union all
  8  select 7      , 60         from dual union all
  9  select 8      , 61         from dual union all
 10  select 9      , 63         from dual union all
 11  select 10     , 60         from dual union all
 12  select 11     , 59         from dual union all
 13  select 12     , 58         from dual union all
 14  select 13     , 60         from dual
 15  ),
 16         ALL_DATA_hors60 as (
 17  select id, data, row_number() over (order by id) as rn
 18    from ALL_DATA
 19   where data <> 60
 20  ),
 21         rec_ALL_DATA (id, data, rn, num_group, type_group) as (
 22  select id, data, rn, 1, case when data > 60 then 'MAX' else 'MIN' end
 23    from ALL_DATA_hors60
 24   where rn = 1
 25   union all
 26  select ad.id, ad.data, ad.rn,
 27         case when ad.id = rad.id + 1 then rad.num_group else rad.num_group + 1 end,
 28         case when ad.data > 60 then 'MAX' else 'MIN' end
 29    from ALL_DATA_hors60 ad
 30    join rec_all_data rad on ad.rn = rad.rn + 1
 31  ),
 32         min_max as (
 33  select num_group, type_group,
 34         max(id) keep (dense_rank first order by data desc) as id_max,
 35         max(data) keep (dense_rank first order by data DESC) as data_max,
 36         min(id) keep (dense_rank first order by data ASC) as id_min,
 37         min(data) keep (dense_rank first order by data ASC) as data_min
 38    from rec_all_data
 39   group by num_group, type_group
 40  )
 41  select case when type_group = 'MAX' then id_max else id_min end as id,
 42         case when type_group = 'MAX' then data_max else data_min end as data
 43    from min_max
 44  /
 
        ID       DATA
---------- ----------
         4         62
         9         63
        12         58
 
SQL>

**JGLord** · 30/01/2013, 19h32

@al1_24: J'avoue que ce n'était peut-être pas assez clair. Dans mon contexte, je considère comme un intervalle toute série consécutive d'ID dont la valeur du champ DATA est soit plus grande à 60 OU soit inférieure à 60. Bref, dès que DATA est <> 60, alors ça fait parti d'un évènement. Il faut voir les ID comme des identifiants chronologique (1 vient avant 2, etc).

Dans mon exemple initial, c'est pour cette raison que j'ai dit que ma 1re intervalle était:
03;61
04;62
05;61

Ici, l'ID=01 et l'ID=02 ne sont pas considérés, car DATA n'est pas différent de 60. À l'ID=03, DATA=61 ce qui est le début de l'évènement et cet évènement se terminera à l'ID précédant un retour de DATA à la valeur 60. Bref, pour le 1er échantillon, le passage à DATA=60 se fait à l'ID=06, donc l'ID terminant l'échantillon est l'ID=05 et sa valeur est 61. Tous les ID qui seront entre l'ID débutant et terminant l'évènement devront être analysé pour trouver la valeur maximale du champ DATA correspondant, d'où la valeur DATA=62 qui correspond à l'ID "04".

**JGLord** · 30/01/2013, 19h35

@skuatamad: J'utilise la version suivante:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

Au fait, merci bcp pour ta réponse! Je vais essayer ça dès ce soir. Par contre, j'ai peur que ça soit un peu lourd pour ma "vrai" bd ayant plusieurs millions d'ID.

**skuatamad** · 30/01/2013, 22h55

Voilà une approche compatible avec plus de versions.
Si elle fonctionne bien, elle sera plus performante que l'approche récursive :

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SQL>   WITH ALL_DATA AS (
SELECT 1 AS id, 60 AS DATA FROM dual union ALL
SELECT 2      , 60         FROM dual union ALL
SELECT 3      , 61         FROM dual union ALL
SELECT 4      , 62         FROM dual union ALL
SELECT 5      , 61         FROM dual union ALL
SELECT 6      , 60         FROM dual union ALL
SELECT 7      , 60         FROM dual union ALL
SELECT 8      , 61         FROM dual union ALL
SELECT 9      , 63         FROM dual union ALL
SELECT 10     , 60         FROM dual union ALL
SELECT 11     , 59         FROM dual union ALL
SELECT 12     , 58         FROM dual union ALL
SELECT 13     , 60         FROM dual
),
       all_data_group as (
select id, data, rownum - id as grp, 
       case when data > 60 then 'MAX' else 'MIN' end as type_group
  from all_data
 where data <> 60
)
SELECT case when type_group = 'MAX'
            then max(id) keep (dense_rank first ORDER BY DATA DESC)
            else min(id) keep (dense_rank first ORDER BY DATA ASC)
        end as id,
       case when type_group = 'MAX'
            then max(DATA) keep (dense_rank first ORDER BY DATA DESC)
            else min(DATA) keep (dense_rank first ORDER BY DATA ASC)
        end as data
  FROM all_data_group
 GROUP BY grp, type_group
 order by id;
 
        ID       DATA
---------- ----------
         4         62
         9         63
        12         58
 
SQL>

Par contre je ne teste pas si les data dont les id qui se suivent sont bien tous du même côté de 60, mais je ne le faisais pas non plus dans la version récursive.
De toute façon comme type_group est utilisé dans le GROUP BY ça apparaîtra dans les résultats.

Après, une fois le groupe formé j'ai gardé la même approche pour le min_max (j'ai juste fusionné la requête min_max du recursif avec la requête d'affichage)

**JGLord** · 31/01/2013, 17h21

@skuatamad:
Wow! super, la 2e requête fonctionne très bien avec mon exemple et fait exactement ce que j'ai besoin (je n'ai pas essayé la 1re)!

Par contre, je m'apperçois que mon exemple était peut-être un peu trop "simple", car je ne peux pas l'appliquer directement à mon problème. En fait, dans ma situation réelle, je ne peux pas utiliser directement la colonne ID, car elle cycle de 1 à 60. Cette colonne est utilisée un peu comme des secondes où la valeur 1 = 1 sec et la valeur 60 = 0 sec. Lorsque la colonne ID=1, un champ date-heure "TEMPO_DEBUT" est incrémenté d'une minute.

Plus bas, un échantillon des 360 premières rows de mes "vraies" données sur lesquels j'applique mes "vrais" paramètres dans la clause WHERE. Théoriquement, il devrait y avoir 4 groupes, soit les rows 24 à 65, la row 118, les rows 162 à 171 et finalement la row 360. De ces groupes, les min/max devrait être respectivement "60,0664575", "60,02001922", "59,97520166" et "59,97966287".

J'apprécie sincèrement ton support!

PS. Si c'est beaucoup plus facile en PL/SQL ou même avec des tables temporaires, alors soit. Je suis preneur de toutes les solutions, mais j'aurais une préférence pour du simple SQL.

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
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
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
WITH ALL_DATA AS (
  SELECT 1 AS ID, to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') AS TEMPO_DEBUT , 60.0017053404495 AS DATA FROM dual union ALL
  SELECT 2 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0004931769065 FROM dual union ALL
  SELECT 3 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0007965352798 FROM dual union ALL
  SELECT 4 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0014669749301 FROM dual union ALL
  SELECT 5 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.001757909052 FROM dual union ALL
  SELECT 6 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0027139068303 FROM dual union ALL
  SELECT 7 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0041533679257 FROM dual union ALL
  SELECT 8 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0044164682788 FROM dual union ALL
  SELECT 9 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0034004761496 FROM dual union ALL
  SELECT 10 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0025332168435 FROM dual union ALL
  SELECT 11 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0025983555284 FROM dual union ALL
  SELECT 12 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0029302582338 FROM dual union ALL
  SELECT 13 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0019711159619 FROM dual union ALL
  SELECT 14 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0009258311803 FROM dual union ALL
  SELECT 15 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.00043340113 FROM dual union ALL
  SELECT 16 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0001016534169 FROM dual union ALL
  SELECT 17 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0005370316039 FROM dual union ALL
  SELECT 18 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0009948556177 FROM dual union ALL
  SELECT 19 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0016178584632 FROM dual union ALL
  SELECT 20 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0029495575167 FROM dual union ALL
  SELECT 21 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0042812390786 FROM dual union ALL
  SELECT 22 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0054606656167 FROM dual union ALL
  SELECT 23 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0100489979632 FROM dual union ALL
  SELECT 24 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0221551704098 FROM dual union ALL
  SELECT 25 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.034307709379 FROM dual union ALL
  SELECT 26 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.045305638599 FROM dual union ALL
  SELECT 27 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0544764097255 FROM dual union ALL
  SELECT 28 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.061108798667 FROM dual union ALL
  SELECT 29 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0651211076038 FROM dual union ALL
  SELECT 30 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0664575007132 FROM dual union ALL
  SELECT 31 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0651638854774 FROM dual union ALL
  SELECT 32 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0622898791274 FROM dual union ALL
  SELECT 33 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0581416526895 FROM dual union ALL
  SELECT 34 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0532408065527 FROM dual union ALL
  SELECT 35 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.048474595465 FROM dual union ALL
  SELECT 36 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0452630613456 FROM dual union ALL
  SELECT 37 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0434131166996 FROM dual union ALL
  SELECT 38 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0422595307457 FROM dual union ALL
  SELECT 39 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0407664088789 FROM dual union ALL
  SELECT 40 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.038994388955 FROM dual union ALL
  SELECT 41 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0369158807885 FROM dual union ALL
  SELECT 42 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0347214950281 FROM dual union ALL
  SELECT 43 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0330534637741 FROM dual union ALL
  SELECT 44 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0318420354332 FROM dual union ALL
  SELECT 45 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0312758922846 FROM dual union ALL
  SELECT 46 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0334118529596 FROM dual union ALL
  SELECT 47 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0370253938295 FROM dual union ALL
  SELECT 48 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0402606387971 FROM dual union ALL
  SELECT 49 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0431381737237 FROM dual union ALL
  SELECT 50 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0456282255704 FROM dual union ALL
  SELECT 51 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0478811547591 FROM dual union ALL
  SELECT 52 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0487953419359 FROM dual union ALL
  SELECT 53 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0485069346083 FROM dual union ALL
  SELECT 54 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0467894407085 FROM dual union ALL
  SELECT 55 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0442336251666 FROM dual union ALL
  SELECT 56 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.041946207768 FROM dual union ALL
  SELECT 57 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0397156082361 FROM dual union ALL
  SELECT 58 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0375439528119 FROM dual union ALL
  SELECT 59 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0357409002194 FROM dual union ALL
  SELECT 60 , to_date('2012-02-01  00:00:00','yyyy-mm-dd hh24:mi:ss') , 60.0337826925888 FROM dual union ALL
  SELECT 1 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0316112299934 FROM dual union ALL
  SELECT 2 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0290744293926 FROM dual union ALL
  SELECT 3 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0260532303478 FROM dual union ALL
  SELECT 4 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0235427774309 FROM dual union ALL
  SELECT 5 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0215344397473 FROM dual union ALL
  SELECT 6 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.018832591817 FROM dual union ALL
  SELECT 7 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.014789832893 FROM dual union ALL
  SELECT 8 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0106648849189 FROM dual union ALL
  SELECT 9 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0069510478692 FROM dual union ALL
  SELECT 10 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0036204567664 FROM dual union ALL
  SELECT 11 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0015840937931 FROM dual union ALL
  SELECT 12 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0010062602994 FROM dual union ALL
  SELECT 13 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0009022734481 FROM dual union ALL
  SELECT 14 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.001623181064 FROM dual union ALL
  SELECT 15 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0024659476557 FROM dual union ALL
  SELECT 16 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.002342151625 FROM dual union ALL
  SELECT 17 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0020511418497 FROM dual union ALL
  SELECT 18 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0017330287694 FROM dual union ALL
  SELECT 19 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.001113688013 FROM dual union ALL
  SELECT 20 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0016452478477 FROM dual union ALL
  SELECT 21 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0050795704844 FROM dual union ALL
  SELECT 22 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0085775141918 FROM dual union ALL
  SELECT 23 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0109603260472 FROM dual union ALL
  SELECT 24 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0119843575137 FROM dual union ALL
  SELECT 25 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.012445468421 FROM dual union ALL
  SELECT 26 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0123343190855 FROM dual union ALL
  SELECT 27 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0119240074355 FROM dual union ALL
  SELECT 28 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0115915134895 FROM dual union ALL
  SELECT 29 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0116145404288 FROM dual union ALL
  SELECT 30 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0112757447252 FROM dual union ALL
  SELECT 31 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0110262877733 FROM dual union ALL
  SELECT 32 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0095006881027 FROM dual union ALL
  SELECT 33 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0069507975345 FROM dual union ALL
  SELECT 34 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0040293828404 FROM dual union ALL
  SELECT 35 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0019847754225 FROM dual union ALL
  SELECT 36 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0015892924814 FROM dual union ALL
  SELECT 37 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0015617833706 FROM dual union ALL
  SELECT 38 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0021136505872 FROM dual union ALL
  SELECT 39 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0031175252862 FROM dual union ALL
  SELECT 40 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.004173332112 FROM dual union ALL
  SELECT 41 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0044002435423 FROM dual union ALL
  SELECT 42 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0050777001204 FROM dual union ALL
  SELECT 43 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0069486107593 FROM dual union ALL
  SELECT 44 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0091809291429 FROM dual union ALL
  SELECT 45 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0112774081937 FROM dual union ALL
  SELECT 46 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0130915791372 FROM dual union ALL
  SELECT 47 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0145398024768 FROM dual union ALL
  SELECT 48 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0153855358263 FROM dual union ALL
  SELECT 49 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0156691827971 FROM dual union ALL
  SELECT 50 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.015666651258 FROM dual union ALL
  SELECT 51 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0153195443598 FROM dual union ALL
  SELECT 52 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0155794126829 FROM dual union ALL
  SELECT 53 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0159561229324 FROM dual union ALL
  SELECT 54 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0165333274209 FROM dual union ALL
  SELECT 55 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0170316714744 FROM dual union ALL
  SELECT 56 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0174195027344 FROM dual union ALL
  SELECT 57 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0187186337579 FROM dual union ALL
  SELECT 58 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0200192189761 FROM dual union ALL
  SELECT 59 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0199695901108 FROM dual union ALL
  SELECT 60 , to_date('2012-02-01  00:01:00','yyyy-mm-dd hh24:mi:ss') , 60.0186910391656 FROM dual union ALL
  SELECT 1 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 60.0161286945013 FROM dual union ALL
  SELECT 2 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 60.0129560464742 FROM dual union ALL
  SELECT 3 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 60.0099533520988 FROM dual union ALL
  SELECT 4 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 60.0066738534765 FROM dual union ALL
  SELECT 5 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 60.0028250285331 FROM dual union ALL
  SELECT 6 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9999787792988 FROM dual union ALL
  SELECT 7 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.997193932525 FROM dual union ALL
  SELECT 8 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.995265109369 FROM dual union ALL
  SELECT 9 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9939094111132 FROM dual union ALL
  SELECT 10 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9938624432171 FROM dual union ALL
  SELECT 11 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9951677832032 FROM dual union ALL
  SELECT 12 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9967739070631 FROM dual union ALL
  SELECT 13 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9976572666142 FROM dual union ALL
  SELECT 14 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9971198069856 FROM dual union ALL
  SELECT 15 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9961913131632 FROM dual union ALL
  SELECT 16 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9953004204039 FROM dual union ALL
  SELECT 17 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9951023809709 FROM dual union ALL
  SELECT 18 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.995289410308 FROM dual union ALL
  SELECT 19 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9954068085698 FROM dual union ALL
  SELECT 20 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.995395509428 FROM dual union ALL
  SELECT 21 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9943028035747 FROM dual union ALL
  SELECT 22 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9929009040497 FROM dual union ALL
  SELECT 23 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.99246888488 FROM dual union ALL
  SELECT 24 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9917023681611 FROM dual union ALL
  SELECT 25 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9899485675248 FROM dual union ALL
  SELECT 26 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9876076370103 FROM dual union ALL
  SELECT 27 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9859955641449 FROM dual union ALL
  SELECT 28 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9853121815993 FROM dual union ALL
  SELECT 29 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9841580382742 FROM dual union ALL
  SELECT 30 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9826181499291 FROM dual union ALL
  SELECT 31 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9825525789428 FROM dual union ALL
  SELECT 32 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9832866106687 FROM dual union ALL
  SELECT 33 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9841316836313 FROM dual union ALL
  SELECT 34 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9852462960987 FROM dual union ALL
  SELECT 35 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9864021162396 FROM dual union ALL
  SELECT 36 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9876736196246 FROM dual union ALL
  SELECT 37 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9890155222258 FROM dual union ALL
  SELECT 38 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.989483896019 FROM dual union ALL
  SELECT 39 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9884616566541 FROM dual union ALL
  SELECT 40 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.985089173583 FROM dual union ALL
  SELECT 41 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9816454531594 FROM dual union ALL
  SELECT 42 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9793113026208 FROM dual union ALL
  SELECT 43 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9778966360457 FROM dual union ALL
  SELECT 44 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.976804860171 FROM dual union ALL
  SELECT 45 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9755836061817 FROM dual union ALL
  SELECT 46 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9752016611855 FROM dual union ALL
  SELECT 47 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9755156551812 FROM dual union ALL
  SELECT 48 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9759327540174 FROM dual union ALL
  SELECT 49 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9765132915517 FROM dual union ALL
  SELECT 50 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9775194120165 FROM dual union ALL
  SELECT 51 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9790627259032 FROM dual union ALL
  SELECT 52 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9805608806768 FROM dual union ALL
  SELECT 53 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9818339201576 FROM dual union ALL
  SELECT 54 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9829443315867 FROM dual union ALL
  SELECT 55 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9844657700509 FROM dual union ALL
  SELECT 56 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9861859044318 FROM dual union ALL
  SELECT 57 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9880200479631 FROM dual union ALL
  SELECT 58 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9888097051701 FROM dual union ALL
  SELECT 59 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9888616471723 FROM dual union ALL
  SELECT 60 , to_date('2012-02-01  00:02:00','yyyy-mm-dd hh24:mi:ss') , 59.9878984674267 FROM dual union ALL
  SELECT 1 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.986200864576 FROM dual union ALL
  SELECT 2 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9849938010097 FROM dual union ALL
  SELECT 3 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9842306793761 FROM dual union ALL
  SELECT 4 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9838660553486 FROM dual union ALL
  SELECT 5 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9842904465687 FROM dual union ALL
  SELECT 6 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9848902289405 FROM dual union ALL
  SELECT 7 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9850881970709 FROM dual union ALL
  SELECT 8 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9857469508283 FROM dual union ALL
  SELECT 9 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9866567086012 FROM dual union ALL
  SELECT 10 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9876319418533 FROM dual union ALL
  SELECT 11 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9885850569972 FROM dual union ALL
  SELECT 12 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.989628749093 FROM dual union ALL
  SELECT 13 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.991092670582 FROM dual union ALL
  SELECT 14 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9926907018868 FROM dual union ALL
  SELECT 15 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9932867333495 FROM dual union ALL
  SELECT 16 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9928426820097 FROM dual union ALL
  SELECT 17 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.99154801385 FROM dual union ALL
  SELECT 18 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9899777578402 FROM dual union ALL
  SELECT 19 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9880405688875 FROM dual union ALL
  SELECT 20 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9871767540539 FROM dual union ALL
  SELECT 21 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9879683218996 FROM dual union ALL
  SELECT 22 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9898346379432 FROM dual union ALL
  SELECT 23 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9913384681127 FROM dual union ALL
  SELECT 24 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9918720584424 FROM dual union ALL
  SELECT 25 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.991649686952 FROM dual union ALL
  SELECT 26 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9914567655839 FROM dual union ALL
  SELECT 27 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9913513227691 FROM dual union ALL
  SELECT 28 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9910812552946 FROM dual union ALL
  SELECT 29 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9899631608566 FROM dual union ALL
  SELECT 30 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9876995681555 FROM dual union ALL
  SELECT 31 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9856409405207 FROM dual union ALL
  SELECT 32 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9843269855471 FROM dual union ALL
  SELECT 33 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9837465421599 FROM dual union ALL
  SELECT 34 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9843807442293 FROM dual union ALL
  SELECT 35 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9856044006414 FROM dual union ALL
  SELECT 36 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9864520195023 FROM dual union ALL
  SELECT 37 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9878249102771 FROM dual union ALL
  SELECT 38 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.988827877803 FROM dual union ALL
  SELECT 39 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9898313233511 FROM dual union ALL
  SELECT 40 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9914530037124 FROM dual union ALL
  SELECT 41 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9932207689527 FROM dual union ALL
  SELECT 42 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9953914835209 FROM dual union ALL
  SELECT 43 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9970608115006 FROM dual union ALL
  SELECT 44 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.998389869786 FROM dual union ALL
  SELECT 45 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9991890631903 FROM dual union ALL
  SELECT 46 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9995929160937 FROM dual union ALL
  SELECT 47 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9994910620544 FROM dual union ALL
  SELECT 48 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9985950709808 FROM dual union ALL
  SELECT 49 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9979672259051 FROM dual union ALL
  SELECT 50 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9980089824408 FROM dual union ALL
  SELECT 51 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9976299118805 FROM dual union ALL
  SELECT 52 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9977303759793 FROM dual union ALL
  SELECT 53 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9977113389844 FROM dual union ALL
  SELECT 54 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9972638565914 FROM dual union ALL
  SELECT 55 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9959827164263 FROM dual union ALL
  SELECT 56 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9950676494879 FROM dual union ALL
  SELECT 57 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9954198516511 FROM dual union ALL
  SELECT 58 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9970464111999 FROM dual union ALL
  SELECT 59 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 59.9992791061536 FROM dual union ALL
  SELECT 60 , to_date('2012-02-01  00:03:00','yyyy-mm-dd hh24:mi:ss') , 60.0019702523638 FROM dual union ALL
  SELECT 1 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0047001882135 FROM dual union ALL
  SELECT 2 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0073143593296 FROM dual union ALL
  SELECT 3 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.008772470868 FROM dual union ALL
  SELECT 4 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0089713502021 FROM dual union ALL
  SELECT 5 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0086269062117 FROM dual union ALL
  SELECT 6 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.007832773569 FROM dual union ALL
  SELECT 7 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0065595703911 FROM dual union ALL
  SELECT 8 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0054331133934 FROM dual union ALL
  SELECT 9 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0040570349024 FROM dual union ALL
  SELECT 10 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0020517398824 FROM dual union ALL
  SELECT 11 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0006746343556 FROM dual union ALL
  SELECT 12 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0001432906911 FROM dual union ALL
  SELECT 13 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9998668998003 FROM dual union ALL
  SELECT 14 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9999633406146 FROM dual union ALL
  SELECT 15 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0001073546898 FROM dual union ALL
  SELECT 16 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.000831438836 FROM dual union ALL
  SELECT 17 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.001577661226 FROM dual union ALL
  SELECT 18 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0023398720858 FROM dual union ALL
  SELECT 19 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0032285269677 FROM dual union ALL
  SELECT 20 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0044209161368 FROM dual union ALL
  SELECT 21 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.005646542097 FROM dual union ALL
  SELECT 22 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0076684169475 FROM dual union ALL
  SELECT 23 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0111848264124 FROM dual union ALL
  SELECT 24 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0144261529885 FROM dual union ALL
  SELECT 25 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0160101981892 FROM dual union ALL
  SELECT 26 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0160114037054 FROM dual union ALL
  SELECT 27 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0147212162818 FROM dual union ALL
  SELECT 28 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0128849174338 FROM dual union ALL
  SELECT 29 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0109608430745 FROM dual union ALL
  SELECT 30 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0088316762167 FROM dual union ALL
  SELECT 31 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0066211683954 FROM dual union ALL
  SELECT 32 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0051509271592 FROM dual union ALL
  SELECT 33 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0041571682942 FROM dual union ALL
  SELECT 34 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0033303964958 FROM dual union ALL
  SELECT 35 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0014359095289 FROM dual union ALL
  SELECT 36 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9992736249658 FROM dual union ALL
  SELECT 37 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9976500146977 FROM dual union ALL
  SELECT 38 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9972336611497 FROM dual union ALL
  SELECT 39 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9985917476054 FROM dual union ALL
  SELECT 40 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0010523926399 FROM dual union ALL
  SELECT 41 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0035920140276 FROM dual union ALL
  SELECT 42 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0055471377977 FROM dual union ALL
  SELECT 43 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0055589225109 FROM dual union ALL
  SELECT 44 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0046086533634 FROM dual union ALL
  SELECT 45 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0032519774826 FROM dual union ALL
  SELECT 46 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0021322445919 FROM dual union ALL
  SELECT 47 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0015004982579 FROM dual union ALL
  SELECT 48 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0004454914584 FROM dual union ALL
  SELECT 49 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9996973965206 FROM dual union ALL
  SELECT 50 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9992967918174 FROM dual union ALL
  SELECT 51 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9995781088368 FROM dual union ALL
  SELECT 52 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0003409290539 FROM dual union ALL
  SELECT 53 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0008023955188 FROM dual union ALL
  SELECT 54 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 60.0004775932658 FROM dual union ALL
  SELECT 55 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9996886909062 FROM dual union ALL
  SELECT 56 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9983254518 FROM dual union ALL
  SELECT 57 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9967384553469 FROM dual union ALL
  SELECT 58 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9941909206606 FROM dual union ALL
  SELECT 59 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9915330302011 FROM dual union ALL
  SELECT 60 , to_date('2012-02-01  00:04:00','yyyy-mm-dd hh24:mi:ss') , 59.9896600448922 FROM dual union ALL
  SELECT 1 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9887669983318 FROM dual union ALL
  SELECT 2 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9887903028799 FROM dual union ALL
  SELECT 3 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9897084895051 FROM dual union ALL
  SELECT 4 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9919184846138 FROM dual union ALL
  SELECT 5 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9948777003197 FROM dual union ALL
  SELECT 6 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9977731366236 FROM dual union ALL
  SELECT 7 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0013391834752 FROM dual union ALL
  SELECT 8 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0042206308824 FROM dual union ALL
  SELECT 9 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0063120541323 FROM dual union ALL
  SELECT 10 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0073208527858 FROM dual union ALL
  SELECT 11 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0073771442877 FROM dual union ALL
  SELECT 12 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0070616714446 FROM dual union ALL
  SELECT 13 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0061990842673 FROM dual union ALL
  SELECT 14 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0042022809036 FROM dual union ALL
  SELECT 15 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0001678009711 FROM dual union ALL
  SELECT 16 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9960849110742 FROM dual union ALL
  SELECT 17 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9919002996867 FROM dual union ALL
  SELECT 18 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9880012828073 FROM dual union ALL
  SELECT 19 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9859923538651 FROM dual union ALL
  SELECT 20 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9854611978031 FROM dual union ALL
  SELECT 21 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9859723915394 FROM dual union ALL
  SELECT 22 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9867694127364 FROM dual union ALL
  SELECT 23 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9877657487745 FROM dual union ALL
  SELECT 24 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9897743474511 FROM dual union ALL
  SELECT 25 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9920776877881 FROM dual union ALL
  SELECT 26 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9949233383113 FROM dual union ALL
  SELECT 27 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9971868845598 FROM dual union ALL
  SELECT 28 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9991889890644 FROM dual union ALL
  SELECT 29 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0006723366377 FROM dual union ALL
  SELECT 30 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0010263124231 FROM dual union ALL
  SELECT 31 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0010565262429 FROM dual union ALL
  SELECT 32 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0030303627934 FROM dual union ALL
  SELECT 33 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0062248189511 FROM dual union ALL
  SELECT 34 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0089135012202 FROM dual union ALL
  SELECT 35 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0109927512626 FROM dual union ALL
  SELECT 36 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0116019123677 FROM dual union ALL
  SELECT 37 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0112186324064 FROM dual union ALL
  SELECT 38 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0098198462371 FROM dual union ALL
  SELECT 39 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.006998481111 FROM dual union ALL
  SELECT 40 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0029179411533 FROM dual union ALL
  SELECT 41 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9993171500733 FROM dual union ALL
  SELECT 42 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9984827704787 FROM dual union ALL
  SELECT 43 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9984980577249 FROM dual union ALL
  SELECT 44 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9992362662406 FROM dual union ALL
  SELECT 45 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0003447007304 FROM dual union ALL
  SELECT 46 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0019105365652 FROM dual union ALL
  SELECT 47 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0029729883812 FROM dual union ALL
  SELECT 48 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0039689570592 FROM dual union ALL
  SELECT 49 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0048674098294 FROM dual union ALL
  SELECT 50 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0048283013023 FROM dual union ALL
  SELECT 51 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0037398627955 FROM dual union ALL
  SELECT 52 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 60.0008075217987 FROM dual union ALL
  SELECT 53 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9966891907837 FROM dual union ALL
  SELECT 54 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9926906085076 FROM dual union ALL
  SELECT 55 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9900187741613 FROM dual union ALL
  SELECT 56 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9884702302921 FROM dual union ALL
  SELECT 57 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9869107329137 FROM dual union ALL
  SELECT 58 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9844382593624 FROM dual union ALL
  SELECT 59 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9819060950232 FROM dual union ALL
  SELECT 60 , to_date('2012-02-01  00:05:00','yyyy-mm-dd hh24:mi:ss') , 59.9796628724026 FROM dual
  ),
    all_data_group AS (
  SELECT ID, TEMPO_DEBUT, DATA, rownum - to_number(to_char(TEMPO_DEBUT,'yyyymmddhh24miss')) + ID AS grp, 
       case when DATA > 60.02 then 'MAX' 
       else 'MIN' end AS type_group
  FROM all_data
  WHERE ABS(DATA-60) >= 0.02
  )
select *
FROM all_data_group;

**skuatamad** · 31/01/2013, 17h55

Utilise ROW_NUMBER pour générer une vrai séquence :

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SQL>   WITH rn_all_data as (
  2  select id, TEMPO_DEBUT, data, row_number() over (order by TEMPO_DEBUT, id) as rn
  3    from all_data
  4    ),
  5         all_data_group AS (
  6  SELECT rn, ID, TEMPO_DEBUT, DATA, rownum - rn AS grp,
  7         case when DATA > 60.02 then 'MAX' else 'MIN' end AS type_group
  8    FROM rn_all_data
  9   WHERE ABS(DATA-60) >= 0.02
 10    )
 11  SELECT case when type_group = 'MAX'
 12              then max(rn) keep (dense_rank first ORDER BY DATA DESC)
 13              else min(rn) keep (dense_rank first ORDER BY DATA ASC)
 14          end AS rn,
 15         case when type_group = 'MAX'
 16              then max(id) keep (dense_rank first ORDER BY DATA DESC)
 17              else min(id) keep (dense_rank first ORDER BY DATA ASC)
 18          end AS id,
 19         case when type_group = 'MAX'
 20              then max(TEMPO_DEBUT) keep (dense_rank first ORDER BY DATA DESC)
 21              else min(TEMPO_DEBUT) keep (dense_rank first ORDER BY DATA ASC)
 22          end AS TEMPO_DEBUT,
 23         case when type_group = 'MAX'
 24              then max(DATA) keep (dense_rank first ORDER BY DATA DESC)
 25              else min(DATA) keep (dense_rank first ORDER BY DATA ASC)
 26          end AS DATA
 27    FROM all_data_group
 28   GROUP BY grp, type_group
 29   ORDER BY rn;
 
        RN         ID TEMPO_DE       DATA
---------- ---------- -------- ----------
        30         30 01/02/12 60,0664575
       118         58 01/02/12 60,0200192
       166         46 01/02/12 59,9752017
       360         60 01/02/12 59,9796629
 
SQL>

**JGLord** · 31/01/2013, 19h08

@skuatamad:
Fonctionne Nickel!

Gros merci!

**Waldar** · 05/02/2013, 11h32

J'ai une autre solution un peu plus courte :

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
20
WITH grp_all_data AS
(
  SELECT id, tempo_debut, data
       , row_number() over (                                     ORDER BY tempo_debut asc, id asc)
       - row_number() over (PARTITION BY sign(0.02-abs(data-60)) ORDER BY tempo_debut asc, id asc) as grp
    FROM all_data
)
  select max(id)          keep(dense_rank first order by abs(data-60) desc) as id
       , max(tempo_debut) keep(dense_rank first order by abs(data-60) desc) as tempo_debut
       , max(data)        keep(dense_rank first order by abs(data-60) desc) as data
    from grp_all_data
   where abs(data-60) >= 0.02
group by grp;
 
ID TEMPO_DEBUT         DATA
-- ------------------- ----------------
30 2012-02-01 00:00:00 60.0664575007132
58 2012-02-01 00:01:00 60.0200192189761
46 2012-02-01 00:02:00 59.9752016611855
60 2012-02-01 00:05:00 59.9796628724026

**JGLord** · 07/02/2013, 14h25

@Waldar:
Merci, ça fonctionne aussi très bien, même que le Cost est inférieur!

Comment trouver les extrêmes à partir d'intervalles?

SQL Oracle

Discussions similaires

Partager

Partager