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

Andreas Mosmann schreef:
select by Michel Cadot in
http://groups.google.com/group/comp....a21ddefd6507e6

Shakespeare

"Andreas Mosmann" <mosmann (AT) expires-31-10-2009 (DOT) news-group.org> a écrit dans le message de news: 1256558932.12 (AT) user (DOT) 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

Michel Cadot schreef:

Shakespeare

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

Andreas

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

Andreas Mosmann wrote:
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

Maxim Demenko schrieb am 27.10.2009 in <4AE6A3AF.4020506 (AT) gmail (DOT) com>:



- 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

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

Andreas Mosmann schrieb am 02.11.2009 in
<1257181408.57 (AT) user (DOT) newsoffice.de>:



Similar question, similar answer?

the situation before is

F1|F2|F3
AA|BB|10
AA|BB|20
AA|BB|40
AA|BB|50
AA|BB|60
AA|BB|70
AA|CC|70
AA|CC|80
AA|CC|90

and the result should be

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

Is there a similar way to get this? Your way is very fast.

Many thanks in advance
Andreas Mosmann

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

Andreas Mosmann wrote:
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

Maxim Demenko schrieb am 17.11.2009 in <4B024E96.2010503 (AT) gmail (DOT) com>:

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?

I am really excited

Andreas


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