Re: a difficult SQL request

From: Buck Turgidson <jc_va_at_hotmail.com>
Date: Sun, 23 Feb 2003 18:55:03 GMT
Message-ID: <bO86a.58921$P1.3752150@news1.east.cox.net>

Thought about your question a little more. There is probably a slicker way to do this, but this will answer the question "which row has 7 previous consecutive rows with the same volume.

I.e. at 12:14, there existed 7 previous records of volume 3. The same for minute 27, 28, and 29.

SQL> select to_char(c_time,'hh:mi'),volume from data_collector 2 order by c_time;

TO_CH VOLUME
----- ----------

12:01          1
12:02          2
12:03          3
12:04          3
12:05          3
12:06          4
12:07          3
12:08          3
12:09          3
12:10          3
12:11          3
12:12          3
12:13          3
12:14          3
12:15          6
12:16          8
12:17          2
12:18          2
12:19          2
12:20          9
12:21          9
12:22          9
12:23          9
12:24          9
12:25          9
12:26          9
12:27          9
12:28          9
12:29          9

29 rows selected.

SQL>
SQL> select c_time from
  2 (
  3 select
  4 to_char(c_time,'hh:mi') c_time,
  5 volume,

  6    lag (volume,1) over (order by c_time) previous_1,
  7    lag (volume,2) over (order by c_time) previous_2,
  8    lag (volume,3) over (order by c_time) previous_3,
  9    lag (volume,4) over (order by c_time) previous_4,
 10    lag (volume,5) over (order by c_time) previous_5,
 11    lag (volume,6) over (order by c_time) previous_6,
 12    lag (volume,7) over (order by c_time) previous_7

13 from data_collector
14 order by c_time
15 )
16 where volume = previous_1

 17  and previous_1 = previous_2
 18  and previous_1 = previous_3
 19  and previous_1 = previous_4
 20  and previous_1 = previous_5
 21  and previous_1 = previous_6
 22  and previous_1 = previous_7;

C_TIM

Received on Sun Feb 23 2003 - 12:55:03 CST