difficult select- question

Andreas Mosmann

unread,

Oct 26, 2009, 8:08:52 AM10/26/09

to

Hi ng,

I hope, there is someone with a good idea concerning the following
question:

There is a sorted Table (or view) like the following:
F1|F2|F3|F4
AA|BB|10|20
AA|BB|20|30
AA|BB|40|50
AA|BB|50|60
AA|BB|60|70
AA|CC|70|80
AA|CC|80|90

As you can see there are some rows, that are connected by F3=prior F4,
but there are leaks too. I need a result as the following:

AA|BB|10|30|2 parts
AA|BB|40|70|3 parts
AA|CC|70|90|2 parts

I know it is easy to solve by programming.
I think it is possible to do this with inline view and hierarchical
query.
I can imagine a way by select min(F3), max(F4) ... where exists (enough
connecting data in the middle).

But what is the best (SQL-)way to solve this? (There actually are about
15000 lines, the estimated result probably should not have more than 100
lines)
I could do it by PL/SQL, but I am interested in SQL, if exists

Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

Shakespeare

unread,

Oct 26, 2009, 10:58:46 AM10/26/09

to

Andreas Mosmann schreef:

A question like this has been answered with a very simple, but beautiful
select by Michel Cadot in
http://groups.google.com/group/comp.databases.oracle.misc/msg/78a21ddefd6507e6

Shakespeare

Michel Cadot

unread,

Oct 26, 2009, 3:27:11 PM10/26/09

to

"Andreas Mosmann" <mos...@expires-31-10-2009.news-group.org> a �crit dans le message de news: 125655...@user.newsoffice.de...

SQL> select connect_by_root f1 f1, f2,
2 connect_by_root f3 f3, f4,
3 (f4-connect_by_root f3)/10 cnt1,
4 length(sys_connect_by_path(f2,'/'))
5 - length(replace(sys_connect_by_path(f2,'/'),'/','')) cnt2
6 from t
7 where connect_by_isleaf = 1
8 connect by prior f1 = f1 and prior f2 = f2 and prior f4 = f3
9 start with (f1,f2,f3) not in (select f1,f2,f4 from t)
10 /
F1 F2 F3 F4 CNT1 CNT2
-- -- ---------- ---------- ---------- ----------
AA BB 10 30 2 2
AA BB 40 70 3 3
AA CC 70 90 2 2

3 rows selected.

Regards
Michel

Shakespeare

unread,

Oct 26, 2009, 3:32:44 PM10/26/09

to

Michel Cadot schreef:

You see?

Shakespeare

Andreas Mosmann

unread,

Oct 26, 2009, 4:12:00 PM10/26/09

to

Many thanks to both of you, I never heard about connect_by_root.
This is what I looked for.

Andreas

Maxim Demenko

unread,

Oct 27, 2009, 3:39:27 AM10/27/09

to Andreas Mosmann

Another alternative could be use of analytical functions,
1) the classical interval calculation with *start of group* method
SQL> with t as (
2 select 'AA' F1,'BB' F2,10 F3,20 F4 from dual union all
3 select 'AA','BB',20,30 from dual union all
4 select 'AA','BB',40,50 from dual union all
5 select 'AA','BB',50,60 from dual union all
6 select 'AA','BB',60,70 from dual union all
7 select 'AA','BB',90,100 from dual union all
8 select 'AA','BB',100,110 from dual union all
9 select 'AA','BB',110,120 from dual union all
10 select 'AA','BB',120,130 from dual union all
11 select 'AA','CC',10,20 from dual union all
12 select 'AA','CC',20,30 from dual union all
13 select 'AA','CC',30,40 from dual union all
14 select 'AA','CC',50,60 from dual union all
15 select 'AA','CC',60,70 from dual union all
16 select 'AA','CC',70,80 from dual union all
17 select 'AA','CC',80,90 from dual union all
18 select 'AA','CC',90,100 from dual union all
19 select 'AA','CC',100,110 from dual union all
20 select 'AA','CC',170,180 from dual union all
21 select 'AA','CC',180,190 from dual
22 )
23 -- End test data
24 select f1,f2,min(f3) f3,max(f4) f4,count(*) cnt
25 from (
26 select f1,
27 f2,
28 f3,
29 f4,
30 sum(start_of_group) over(partition by f1, f2 order by f3)
group_no
31 from (select f1,
32 f2,
33 f3,
34 f4,
35 decode(lag(f4) over(partition by f1, f2 order by f3),
36 f3,
37 0,
38 1) start_of_group
39 from t)
40 )
41 group by f1,f2,group_no
42 order by f1,f2,f3
43 ;

F1 F2 F3 F4 CNT

-- -- ---------- ---------- ----------

AA BB 10 30 2

AA BB 40 70 3

AA BB 90 130 4
AA CC 10 40 3
AA CC 50 110 6
AA CC 170 190 2

2) Slightly different approach with analytics

SQL> with t as (
2 select 'AA' F1,'BB' F2,10 F3,20 F4 from dual union all
3 select 'AA','BB',20,30 from dual union all
4 select 'AA','BB',40,50 from dual union all
5 select 'AA','BB',50,60 from dual union all
6 select 'AA','BB',60,70 from dual union all
7 select 'AA','BB',90,100 from dual union all
8 select 'AA','BB',100,110 from dual union all
9 select 'AA','BB',110,120 from dual union all
10 select 'AA','BB',120,130 from dual union all
11 select 'AA','CC',10,20 from dual union all
12 select 'AA','CC',20,30 from dual union all
13 select 'AA','CC',30,40 from dual union all
14 select 'AA','CC',50,60 from dual union all
15 select 'AA','CC',60,70 from dual union all
16 select 'AA','CC',70,80 from dual union all
17 select 'AA','CC',80,90 from dual union all
18 select 'AA','CC',90,100 from dual union all
19 select 'AA','CC',100,110 from dual union all
20 select 'AA','CC',170,180 from dual union all
21 select 'AA','CC',180,190 from dual
22 )
23 -- End test data
24 select f1,
25 f2,
26 f3,
27 nvl(lead(prev_f4) over(partition by f1, f2 order by f3),
max_f4) f4,
28 nvl(lead(prev_cnt_f4)
29 over(partition by f1, f2 order by f3) - prev_cnt_f4,
30 cnt_f4 - prev_cnt_f4) cnt
31 from (select t.*,
32 count(*) over(partition by f1, f2) cnt_f4,
33 count(*) over(partition by f1, f2 order by f3
34 rows between unbounded preceding and 1 preceding)
prev_cnt_f4,
35 max(f4) over(partition by f1, f2) max_f4,
36 max(f4) over(partition by f1, f2 order by f3
37 rows between unbounded preceding and 1 preceding)
prev_f4,
38 decode(lag(f4) over(partition by f1, f2 order by f3),
39 f3,
40 0,
41 1) g
42 from t) t1
43 where g = 1
44 order by f1,f2,f3
45 ;

F1 F2 F3 F4 CNT

-- -- ---------- ---------- ----------

AA BB 10 30 2

AA BB 40 70 3

AA BB 90 130 4
AA CC 10 40 3
AA CC 50 110 6
AA CC 170 190 2

Best regards

Maxim

Andreas Mosmann

unread,

Nov 2, 2009, 12:03:28 PM11/2/09

to

> Best regards

> Maxim
I did not understand everything by now, but it works fine.
- Innermost select seems to give first row of a sub-group defined by
connection between F3->F4 the number 1, all the others get number 0,
- second step is a numbering by sub-groups, each sub-group gets the same
number
- third stage is to find out the min/max and count of each sub-group

I never heard about this functions, but they work very well and super
fast.

Many thanks
Andreas

Andreas Mosmann

unread,

Nov 16, 2009, 4:35:20 PM11/16/09

to

Andreas Mosmann schrieb am 02.11.2009 in
<125718...@user.newsoffice.de>:

>> Best regards

> Many thanks
> Andreas

Maxim Demenko

unread,

Nov 17, 2009, 2:19:50 AM11/17/09

to Andreas Mosmann

Could you explain a bit the logic behind your desired results?
In particular, it's unclear for me, based on what should the line

AA|BB|20|30

be generated

Best regards

Maxim

Andreas Mosmann

unread,

Nov 17, 2009, 11:48:49 AM11/17/09

to

No problem. We talk about streets, that are handled as a net of nodes
and edges. The position of a point object is described as a combination
of both nodes and the relative position to the first node.
If I f.e. have an Edge AA->BB with the length 100 and there are Points
by 10, 30 and 40 I at the end need something like that

N1|N2|POS
AA|BB|10
AA|BB|30
AA|BB|40

should become
N1|N2|FROM|TO
AA|BB| 10 |30
AA|BB| 30 |40

I also have to add
AA|BB| 0 | 10
AA|BB| 40 |100
but I am afraid not understanding the answer if I you fill out these
lines too.
In the table I of course have more than one pair of nodes.
If I would do this by algorithm it would be easy. It is easy to find a
query like
SELECT
N1,
N2,
POS as FROM,
(SELECT MIN(POS) FROM STREETS I WHERE I.N1=O.N1 and I.N2=O.N2 and
I.POS>O.POS) as TO
FROM
STREETS O

(I should no use from as a column name, I know)
But this query of course is very slow.

Is there a place I can read about handling the type of query you used?

> AA|BB|20|30

> be generated

> Best regards

> Maxim
Many thanks again,
I am really excited

Andreas