# 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
>
>
>
>
>

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,
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

Original text of this message