Re: Reduce To Ranges aggregate function

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 21 Oct 2006 06:09:17 -0700
Message-ID: <1161436156.976905.198250@e3g2000cwe.googlegroups.com>

Charles Hooper wrote:
> Michel Cadot wrote:
> > "Mike King" <emailMK_at_excite.com> a écrit dans le message de news: 12jfcsjsafvmtc1_at_corp.supernews.com...
> > | Has someone ready created an aggregate function that reduces a set of
> > | numbers to a reduced form. I'm writing some middleware software that needs
> > | to know the IDs of some tuples but the query could return tens or thousands
> > | of tuples. I'm thinking if there were an aggregate function that would
> > | reduce the network traffic.
> > |
> > | select reduced_to_ranges(id)
> > | from some_large_table
> > | where some_field = some_value
> > |
> > | ID
> > | ----
> > | 1
> > | 2
> > | 3
> > | 4
> > | 5
> > | 11
> > | 12
> > | 45
> > |
> > | Reduced form: 1-5,11-12,45
> > |
> > |
> >
> > SQL> select id from t order by id;
> > ID
> > ----------
> > 1
> > 2
> > 3
> > 4
> > 5
> > 11
> > 12
> > 45
> >
> > 8 rows selected.
> >
> > SQL> col Range format a20
> > SQL> with
> > 2 step1 as (
> > 3 select id,
> > 4 case
> > 5 when nvl(lag(id) over (order by id),-1) != id-1 then id
> > 6 end grp
> > 7 from t),
> > 8 step2 as (
> > 9 select id,
> > 10 max(grp) over (order by id) grp
> > 11 from step1
> > 12 )
> > 13 select min(id)||decode(min(id),max(id),'','-'||max(id)) "Range"
> > 14 from step2
> > 15 group by grp
> > 16 /
> > Range
> > --------------------
> > 1-5
> > 11-12
> > 45
> >
> > 3 rows selected.
> >
> > Regards
> > Michel Cadot

> I guess that I have a bit of difficulty understanding what the OP was
> wanting. That is an interesting solution that you posted Michel. I am
> not sure that I would have tried to use analytical functions to solve
> the problem, but your example is clever.

> If we can make the assumption that the OP was looking for a way to
> group the numbers 1-10, 11-20, 21-30, 31-40, 41-50, a much simplier
> approach is as follows:

> CREATE TABLE T (ID NUMBER(22));
> INSERT INTO T VALUES (1);
> INSERT INTO T VALUES (2);
> INSERT INTO T VALUES (3);
> INSERT INTO T VALUES (4);
> INSERT INTO T VALUES (5);
> INSERT INTO T VALUES (11);
> INSERT INTO T VALUES (12);
> INSERT INTO T VALUES (45);

> SELECT
> MIN(ID)||DECODE(MAX(ID),MIN(ID),'','-'||MAX(ID)) RANGE
> FROM
> T
> GROUP BY
> TRUNC((ID-1)/10)
> ORDER BY
> TRUNC((ID-1)/10);

> RANGE
> ======
> 1-5
> 11-12
> 45

> SELECT

> MAX(DECODE(ROWNUM,1,RANGE,''))||MAX(DECODE(ROWNUM,2,','||RANGE,''))||MAX(DECODE(ROWNUM,3,','||RANGE,''))||MAX(DECODE(ROWNUM,4,','||RANGE,''))||MAX(DECODE(ROWNUM,5,','||RANGE,''))||MAX(DECODE(ROWNUM,6,','||RANGE,''))
> REDUCED_FORM
> FROM
> (SELECT
> MIN(ID)||DECODE(MAX(ID),MIN(ID),'','-'||MAX(ID)) RANGE
> FROM
> T
> GROUP BY
> TRUNC((ID-1)/10)
> ORDER BY
> TRUNC((ID-1)/10)) T;

> REDUCED_FORM
> ============
> 1-5,11-12,45

> Looking at the OP's suggested SQL statement:
> > | select reduced_to_ranges(id)
> > | from some_large_table
> > | where some_field = some_value

> SELECT
> ID,
> TRUNC((ID-1)/10) RANGE
> FROM
> T;

>


> 	ID	RANGE

> ========== ==========

> 	 1	    0

> 	 2	    0

> 	 3	    0

> 	 4	    0

> 	 5	    0

> 	11	    1

> 	12	    1

> 	45	    4

>

> INSERT INTO T VALUES (100);

> SELECT
> TRUNC((ID-1)/10) RANGE
> FROM
> T
> WHERE
> ID=100;

> RANGE
> ==========
> 9

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Borrowing Michel's excellent suggestion to use SYS_CONNECT_BY_PATH, since we do not know how many groups of numbers will be retrieved, my suggestion SQL statement then looks like this: SELECT
  MAX(SUBSTR(SYS_CONNECT_BY_PATH(RANGE,','),2)) REDUCED_FORM FROM
  (SELECT
    RANGE,
    ROWNUM POSITION
  FROM
    (SELECT
      MIN(ID)||DECODE(MAX(ID),MIN(ID),'','-'||MAX(ID)) RANGE     FROM
      T
    GROUP BY
      TRUNC((ID-1)/10)
    ORDER BY
      TRUNC((ID-1)/10)))
CONNECT BY PRIOR
  POSITION=POSITION-1
START WITH
  POSITION=1; REDUCED_FORM

1-5,11-12,45,100

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Oct 21 2006 - 08:09:17 CDT