# Re: difficult select- question

From: Maxim Demenko <mdemenko_at_gmail.com>

Date: Tue, 17 Nov 2009 08:19:50 +0100

Message-ID: <4B024E96.2010503_at_gmail.com>

Andreas Mosmann wrote:

Date: Tue, 17 Nov 2009 08:19:50 +0100

Message-ID: <4B024E96.2010503_at_gmail.com>

Andreas Mosmann wrote:

*> Andreas Mosmann schrieb am 02.11.2009 in**> <1257181408.57_at_user.newsoffice.de>:**>*>> Maxim Demenko schrieb am 27.10.2009 in <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 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 >> 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

*>**> 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**>*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 Received on Tue Nov 17 2009 - 01:19:50 CST