Hi there,
I need help on some mind boggling overlap problem, or perhaps non-overlap problem.
Having the sample data below, I need to put together subcodes in groups having non-overlapping
intervals [MIN_VAL;MAX_VAL[
I have this table:
SQL>create table t (product varchar2(1) not null
2 ,subcode number(1) not null, constraint subcode_chk check (subcode >= 0)
3 ,min_val number(2) not null, constraint min_val_chk check (min_val >= 0)
4 ,max_val number(2) not null, constraint max_val_chk check (max_val >= 0)
5 ,constraint t_pk primary key (product, subcode)
6 ,constraint t_val_chk check (min_val < max_val));
Table created.
SQL>
SQL>insert into t (product, subcode, min_val, max_val) values ('A', 0, 0, 99);
1 row created.
SQL>insert into t (product, subcode, min_val, max_val) values ('A', 1, 0, 99);
1 row created.
SQL>insert into t (product, subcode, min_val, max_val) values ('A', 2, 18, 67);
1 row created.
SQL>insert into t (product, subcode, min_val, max_val) values ('A', 3, 20, 65);
1 row created.
SQL>insert into t (product, subcode, min_val, max_val) values ('A', 4, 0, 99);
1 row created.
SQL>insert into t (product, subcode, min_val, max_val) values ('A', 5, 45, 60);
1 row created.
SQL>insert into t (product, subcode, min_val, max_val) values ('A', 6, 20, 40);
1 row created.
SQL>insert into t (product, subcode, min_val, max_val) values ('A', 7, 0, 99);
1 row created.
SQL>insert into t (product, subcode, min_val, max_val) values ('A', 8, 60, 65);
1 row created.
SQL>insert into t (product, subcode, min_val, max_val) values ('A', 9, 0, 99);
1 row created.
SQL>commit
2 /
Commit complete.
SQL>
SQL> select product, subcode, min_val, max_val
2 from t
3 where product = 'A'
4 order by product, subcode;
PRODUCT SUBCODE MIN_VAL MAX_VAL
------- ---------- ---------- ----------
A 0 0 99
A 1 0 99
A 2 18 67
A 3 20 65
A 4 0 99
A 5 45 60
A 6 20 40
A 7 0 99
A 8 60 65
A 9 0 99
10 rows selected.
SQL>
The records of interest are subcodes 5,6,8 since they in certain cases can be considered as one subcode.
It is OK, that MAX_VAL of one record = MIN_VAL of other record. The main thing is that the subcodes
within a group are mutual disclosing on MIN_VAL, MAX_VAL.
SQL> select product, subcode, min_val, max_val
2 from t
3 where product = 'A'
4 and subcode in (5,6,8)
5 order by min_val;
PRODUCT SUBCODE MIN_VAL MAX_VAL
------- ---------- ---------- ----------
A 6 20 40
A 5 45 60
A 8 60 65
SQL>
I have started out by trying to solve it using lag/lead analytical functions, but without luck.
Next, I've come up with this hierarchial query, but I don't quite understand it myself, and that bothers me!
SQL> select distinct -- This, distinct, bothers me!
2 product
3 ,subcode
4 ,min_val
5 ,max_val
6 -- ,connect_by_isleaf
7 -- ,connect_by_root subcode
8 from t
9 where connect_by_isleaf = 1 -- Why does this help me?
10 start with -- This, start with, seems "clumpsy"
11 min_val in ( select min_val
12 from t
13 group by product, subcode, min_val)
14 connect by nocycle -- This, nocycle, really bothers me!
15 min_val > prior min_val
16 and max_val <= prior max_val
17 and product = prior product
18 and subcode <> prior subcode
19 order by product
20 ,subcode
21 ,min_val
22 ,max_val;
PRODUCT SUBCODE MIN_VAL MAX_VAL
------- ---------- ---------- ----------
A 5 45 60
A 6 20 40
A 8 60 65
SQL>
Currently I'm struggling with just identifying the three subcodes. In the perfect world this would be better output
PRODUCT SUBCODE MIN_VAL MAX_VAL GROUP_FLAG
------- ---------- ---------- ---------- ----------
A 0 0 99
A 1 0 99
A 2 18 67
A 3 20 65
A 4 0 99
A 5 45 60 1
A 6 20 40 1
A 7 0 99
A 8 60 65 1
A 9 0 99
Or even better, if using herarchial query:
PRODUCT SUBCODE MIN_VAL MAX_VAL ROOT_SUBCODE
------- ---------- ---------- ---------- ------------
A 0 0 99 0
A 1 0 99 1
A 2 18 67 2
A 3 20 65 3
A 4 0 99 4
A 5 45 60 6
A 6 20 40 6
A 7 0 99 7
A 8 60 65 6
A 9 0 99 9
Any help and inspiration would be much appreciated. But please don't get offended if I don't follow up the next 12-14 hrs.
Regards
Peter
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
Edited by: Peter on May 19, 2009 12:52 PM
- Changed line 15 in hierarchial query