Re: Reduce To Ranges aggregate function

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 21 Oct 2006 05:53:18 -0700
Message-ID: <1161435198.442779.39530@f16g2000cwb.googlegroups.com>

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. Received on Sat Oct 21 2006 - 07:53:18 CDT