Re: difficult select- question
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 27 Oct 2009 08:39:27 +0100
Message-ID: <4AE6A3AF.4020506_at_gmail.com>
Andreas Mosmann wrote:
> 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
>
>
>
>
>
23 -- End test data
24 select f1,f2,min(f3) f3,max(f4) f4,count(*) cnt 25 from (
40 )
41 group by f1,f2,group_no
42 order by f1,f2,f3
43 ;
23 -- End test data
43 where g = 1
44 order by f1,f2,f3
45 ;
Date: Tue, 27 Oct 2009 08:39:27 +0100
Message-ID: <4AE6A3AF.4020506_at_gmail.com>
Andreas Mosmann wrote:
> 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
>
>
>
>
>
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 dual22 )
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 dual22 )
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 Received on Tue Oct 27 2009 - 02:39:27 CDT