Re: finding ranges and gaps in the range

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 1 Mar 2006 00:28:54 +0100
Message-ID: <4404dcb8$0$27778$636a55ce@news.free.fr>

"Oxnard" <oxnardNO_SPAM_at_comcast.net> a écrit dans le message de news: JYqdndTcPZKFPp7ZnZ2dnUVZ_tudnZ2d_at_comcast.com...
| Oracle 9.2.0.6 in AIX
|
| I am trying to find ranges and where the range ends for each d_no:
| The increment of val is one
|
| My example is:
|
| select * from t1
|
| d_no val
| 1 20
| 1 21
| 1 22
| 1 23
| 1 25
| 1 1503
| 1 1504
| 3 502
| 3 503
| 3 504
| 3 600
|
| I am trying to come up with an SQL which would produce an output of
|
| d_no the_range
| 1 20-23
| 1 25
| 1 1503-1504
| 3 502-504
| 3 600
|
| I have tried using some of the analytic functions and got close with lead
| but not quite. I could do a cursor in
| PL/SQL but the table is so huge it takes way to long. In fact I did do this
| on a small table. It worked just fine.
| Also what I am showing as a table is really an in-line view of a couple of
| tables I have joined.
|
| Any ideas would really be helpful
|
| Thank you
|

SQL> select * from t1 order by d_no, val;

      D_NO VAL
---------- ----------

         1         20
         1         21
         1         22
         1         23
         1         25
         1       1503
         1       1504
         3        502
         3        503
         3        504
         3        600

11 rows selected.

SQL> with
  2 step1 as (

  3      select d_no, val,
  4             lag (d_no) over (order by d_no, val) prev_no,
  5             lag (val) over (order by d_no, val) prev_val,
  6             row_number() over (order by d_no, val) rn
  7      from t1

  8 ),
  9 step2 as (

 10      select d_no, val,
 11             case when prev_no is null or prev_no != d_no
 12                       or prev_val != val-1
 13                  then rn
 14             end rn
 15      from step1

 16 ),
 17 step3 as (

 18      select d_no, val,
 19             max(rn) over (order by d_no, val) group_nb
 20      from step2

 21 )
 22 select d_no,

 23         min(val)||decode(max(val),min(val),'','-'||max(val))
 24           the_range

 25 from step3
 26 group by d_no, group_nb
 27 order by d_no
 28 /

      D_NO THE_RANGE

---------- -----------------------------------------------------------
         1 20-23
         1 25
         1 1503-1504
         3 502-504
         3 600

5 rows selected.

Regards
Michel Cadot Received on Tue Feb 28 2006 - 17:28:54 CST