Reduce To Ranges aggregate function

Mike King

unread,

Oct 19, 2006, 1:18:25 PM10/19/06

to

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

Michel Cadot

unread,

Oct 19, 2006, 1:53:22 PM10/19/06

to

"Mike King" <ema...@excite.com> a écrit dans le message de news: 12jfcsj...@corp.supernews.com...

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

Frank van Bortel

unread,

Oct 19, 2006, 2:11:01 PM10/19/06

to

Michel Cadot schreef:

Love it! Beautiful!

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Mike King

unread,

Oct 19, 2006, 2:11:44 PM10/19/06

to

I agree with Frank. Wow! Thanks!

Michel Cadot

unread,

Oct 19, 2006, 2:29:09 PM10/19/06

to

"Mike King" <ema...@excite.com> a écrit dans le message de news: 12jfg0l...@corp.supernews.com...

One step further ;-)

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 step3 as (
14 select min(id)||decode(min(id),max(id),'','-'||max(id)) rg,
15 row_number () over (order by min(id)) rn
16 from step2
17 group by grp
18 )
19 select max(substr(sys_connect_by_path(rg,','),2)) "Reduced"
20 from step3
21 connect by prior rn = rn-1
22 start with rn-1 = 0
23 /
Reduced
--------------------
1-5,11-12,45

Regards
Michel Cadot

Frank van Bortel

unread,

Oct 19, 2006, 2:56:09 PM10/19/06

to

Michel Cadot schreef:
>
> One step further ;-)

You get a kick out of this, don't you?
I'm flabbergasted. (Nice word, huh?)

Michel Cadot

unread,

Oct 19, 2006, 3:09:37 PM10/19/06

to

"Frank van Bortel" <frank.va...@gmail.com> a écrit dans le message de news: eh8hc5$ccu$1...@news3.zwoll1.ov.home.nl...

Sure, I do.

| I'm flabbergasted. (Nice word, huh?)

Nice and hopefully I have my Harrap's at hand!

Regards
Michel Cadot

Charles Hooper

unread,

Oct 21, 2006, 8:53:18 AM10/21/06

to

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.

Charles Hooper

unread,

Oct 21, 2006, 9:09:17 AM10/21/06

to

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

Dieter Noeth

unread,

Oct 21, 2006, 12:19:35 PM10/21/06

to

Mike King wrote:

> ID
> ----
> 1
> 2
> 3
> 4
> 5
> 11
> 12
> 45
>
> Reduced form: 1-5,11-12,45

Consecutive values are easy to find using OLAP-functions:

select
min(id), max(id)
from
(
select
id,
id - row_number() over (order by id) grp
from t
) dt
group by grp

If id is not unique use dense_rank instead of row_number...

Dieter

Michel Cadot

unread,

Oct 21, 2006, 12:44:19 PM10/21/06

to

"Dieter Noeth" <dno...@gmx.de> a écrit dans le message de news: ehdhat$sc1$1...@online.de...

Neat solution!

Regards
Michel Cadot