1 2 Previous Next 16 Replies Latest reply on Feb 11, 2010 1:25 AM by Aketi Jyuuzou

# SQL - Find Continuous Records

I am looking for some SQL advice on finding continuous records in a table. The table in questions looks something like this:

ID LITH DEPTH

1-1 SAND 150
1-1 COAL 200
1-1 SAND 250
1-1 COAL 300
2-2 SAND 75
2-2 COAL 100
2-2 COAL 150
2-2 COAL 200
2-2 COAL 250
2-2 SAND 300
2-2 COAL 400
2-2 COAL 450

I am trying to locate the records marked in bold above and count the number of times they occur. In the example above I would hope to return:

id count
1-1 null
2-2 4
2-2 2

I know this is a problem that can be solved outside of the database, with excel for example. However, I would really appreciate any advice on how to solve this problem with SQL.
• ###### 1. Re: SQL - Find Continuous Records
user9010601 wrote:
I am looking for some SQL advice on finding continuous records in a table. The table in questions looks something like this:

ID LITH DEPTH

1-1 SAND 150
1-1 COAL 200
1-1 SAND 250
1-1 COAL 300
2-2 SAND 75
2-2 COAL 100
2-2 COAL 150
2-2 COAL 200
2-2 COAL 250
2-2 SAND 300
2-2 COAL 400
2-2 COAL 450

I am trying to locate the records marked in bold above and count the number of times they occur. In the example above I would hope to return:

id count
1-1 null
2-2 4
2-2 2

I know this is a problem that can be solved outside of the database, with excel for example. However, I would really appreciate any advice on how to solve this problem with SQL.
Can you explain a bit more what you want? What does 1-1 have to do with null? Or 2-2 with 4? or 2-2 with 2? The relationships are not clear
• ###### 2. Re: SQL - Find Continuous Records
In the example that I provided, the records with an id of 1-1 do not have any continuous records therefore the count of the continuous records would return nothing. The 2-2 record has two groups of continuous records, the first group has 4 continuous records and the second group has 2 continuous records.
• ###### 3. Re: SQL - Find Continuous Records
Looks like you want to try analytic functions such as LEAD/LAG.

C.
• ###### 4. Re: SQL - Find Continuous Records
That sounds interesting, could you be more specific. Some pseudocode would be very helpful...
• ###### 5. Re: SQL - Find Continuous Records
http://nuijten.blogspot.com/2009/06/analytic-function-finding-gaps.html
• ###### 6. Re: SQL - Find Continuous Records
The following gives the list of all consecutive (step 50) depth:
``````SQL> with tab as (
2  select '1-1' id, 'SAND' lith, 150 depth from dual union
3  select '1-1' id, 'COAL' lith,  200 from dual union
4  select '1-1' id, 'SAND' lith,  250 from dual union
5  select '1-1' id, 'COAL' lith,  300 from dual union
6  select '2-2' id, 'SAND' lith,  75 from dual union
7  select '2-2' id, 'COAL' lith,  100 from dual union
8  select '2-2' id, 'COAL' lith,  150 from dual union
9  select '2-2' id, 'COAL' lith,  200 from dual union
10  select '2-2' id, 'COAL' lith,  250 from dual union
11  select '2-2' id, 'SAND' lith,  300 from dual union
12  select '2-2' id, 'COAL' lith,  400 from dual union
13  select '2-2' id, 'COAL' lith,  450 from dual
14  )
15  select id, lith, depth, max(level)
16  from tab
17  where connect_by_isleaf=1
18  connect by prior id = id and prior lith = lith and prior depth=depth+50
19  group by id, lith, depth
20  order by id, depth;

ID  LITH      DEPTH MAX(LEVEL)
--- ---- ---------- ----------
1-1 SAND        150          1
1-1 COAL        200          1
1-1 SAND        250          1
1-1 COAL        300          1
2-2 SAND         75          1
2-2 COAL        100          4
2-2 SAND        300          1
2-2 COAL        400          2

8 rows selected.``````
And the following hides the records without at least two following records:
``````SQL> with tab as (
2  select '1-1' id, 'SAND' lith, 150 depth from dual union
3  select '1-1' id, 'COAL' lith,  200 from dual union
4  select '1-1' id, 'SAND' lith,  250 from dual union
5  select '1-1' id, 'COAL' lith,  300 from dual union
6  select '2-2' id, 'SAND' lith,  75 from dual union
7  select '2-2' id, 'COAL' lith,  100 from dual union
8  select '2-2' id, 'COAL' lith,  150 from dual union
9  select '2-2' id, 'COAL' lith,  200 from dual union
10  select '2-2' id, 'COAL' lith,  250 from dual union
11  select '2-2' id, 'SAND' lith,  300 from dual union
12  select '2-2' id, 'COAL' lith,  400 from dual union
13  select '2-2' id, 'COAL' lith,  450 from dual
14  )
15  select id, count from (
16  select id, lith, depth, max(level) count
17  from tab
18  where connect_by_isleaf=1
19  connect by prior id = id and prior lith = lith and prior depth=depth+50
20  group by id, lith, depth
21  order by id, depth
22  )
23  where count>1;

ID       COUNT
--- ----------
2-2          4
2-2          2``````
Do you really need the record with ID='1-1' and count=null?

Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/02/07/aggiornare-una-tabella-con-listruzione-merge/]
• ###### 7. Re: SQL - Find Continuous Records
Massimo,

Thank you very much for the example!!

"Do you really need the record with ID='1-1' and count=null?" Answer: No, what you have will work perfect, thank you very much!
• ###### 8. Re: SQL - Find Continuous Records
Try this
``````SQL> with tab as (
2       select '1-1' as id,'SAND' as lith,150 as depth
3       from dual
4       union all
5       select '1-1' as id,'COAL' as lith,200 as depth
6       from dual
7       union all
8       select '1-1' as id,'SAND' as lith,250 as depth
9       from dual
10       union all
11       select '1-1' as id,'COAL' as lith,300 as depth
12       from dual
13       union all
14       select '2-2' as id,'SAND' as lith,75 as depth
15       from dual
16       union all
17       select '2-2' as id,'COAL' as lith,100 as depth
18       from dual
19       union all
20       select '2-2' as id,'COAL' as lith,150 as depth
21       from dual
22       union all
23       select '2-2' as id,'COAL' as lith,200 as depth
24       from dual
25       union all
26       select '2-2' as id,'COAL' as lith,250 as depth
27       from dual
28       union all
29       select '2-2' as id,'SAND' as lith,300 as depth
30       from dual
31       union all
32       select '2-2' as id,'COAL' as lith,400 as depth
33       from dual
34       union all
35       select '2-2' as id,'COAL' as lith,450 as depth
36       from dual
37  )
38  select a.id,b.lith,b.cnt
39  from (
40            select distinct id
41            from tab
42       ) a partition by (a.id)
43       left outer join (
44                 select id,LITH,count(*) as cnt
45                 from (
46                           select id,lith,cont
47                           from tab
48                           model
49                                partition by (id)
50                                dimension by (row_number() over (partition by ID order by DEPTH) idx)
51                                measures (LITH, 0 as cont)
52                                rules automatic order (
53                                     cont[any]order by idx = decode(lith[cv()],lith[cv()-1],cont[cv()-1],presentv(cont[cv()-1],cont[cv()-1]+1,0))
54                                )
55                      )
56                 group by id,LITH,cont
57                 having count(*) > 1
58            ) b on (a.id=b.id)
59  order by a.id
60  /

ID  LITH        CNT
--- ---- ----------
1-1
2-2 COAL          2
2-2 COAL          4``````
Bye Alessandro
• ###### 9. Re: SQL - Find Continuous Records
Borrowing from the examples of other cummunity members, this is what I'd done with "simple" analytic functions:
``````SELECT id
, COUNT(*)
FROM (
SELECT t.*
, SUM(t.flag_switch) OVER (ORDER BY t.rn) grp_switch
FROM (SELECT t.*
, DECODE(LAG(t.lith) OVER (PARTITION BY t.id ORDER BY t.rn), t.lith, 0, 1) flag_switch
FROM (SELECT t.*
, ROWNUM rn
FROM tab t
) t
) t
)
GROUP BY id
, grp_switch
HAVING COUNT(*) > 1
;``````
C.
• ###### 10. Re: SQL - Find Continuous Records
Or with 10g
Regards Salim
``````select  id , nb
from (select  distinct id, sum(equal)over(partition by id ,grp) nb, sum(equal)over(partition by id) cpt_diff
from( SELECT ID, grp, equal
FROM t
model
partition by ( id)
dimension by (rownum rn)
measures(lith,lead(lith)over(partition by id order by rownum) next_ele ,
lag(lith)over(partition by id order by rownum) prev_ele,0 diff,1 grp, 0 iden,0 equal)ignore nav
(diff[any]    = case when lith[cv()]=lith[cv()-1]  then 0
else 1 end,
equal[any]    = case when lith[cv()]=next_ele[cv()] or lith[cv()]=prev_ele[cv()]   then 1
else 0 end,
grp[any]  = sum(diff)over( partition by id  order by cv(rn)) )))
where nb !=0 or cpt_diff=0``````
``````WITH t AS
(SELECT '1-1' ID, 'SAND' lith, 150 DEPTH
FROM DUAL
UNION ALL
SELECT '1-1', 'COAL', 200
FROM DUAL
UNION ALL
SELECT '1-1', 'SAND', 250
FROM DUAL
UNION ALL
SELECT '1-1', 'COAL', 300
FROM DUAL
UNION ALL
SELECT '2-2', 'SAND', 75
FROM DUAL
UNION ALL
SELECT '2-2', 'COAL', 100
FROM DUAL
UNION ALL
SELECT '2-2', 'COAL', 150
FROM DUAL
UNION ALL
SELECT '2-2', 'COAL', 200
FROM DUAL
UNION ALL
SELECT '2-2', 'COAL', 250
FROM DUAL
UNION ALL
SELECT '2-2', 'SAND', 300
FROM DUAL
UNION ALL
SELECT '2-2', 'COAL', 400
FROM DUAL
UNION ALL
SELECT '2-2', 'COAL', 450
FROM DUAL)
select  id , nb
from (select  distinct id, sum(equal)over(partition by id ,grp) nb, sum(equal)over(partition by id) cpt_diff
from( SELECT ID, grp, equal
FROM t
model
partition by ( id)
dimension by (rownum rn)
measures(lith,lead(lith)over(partition by id order by rownum) next_ele ,
lag(lith)over(partition by id order by rownum) prev_ele,0 diff,1 grp, 0 iden,0 equal)ignore nav
(diff[any]    = case when lith[cv()]=lith[cv()-1]  then 0
else 1 end,
equal[any]    = case when lith[cv()]=next_ele[cv()] or lith[cv()]=prev_ele[cv()]   then 1
else 0 end,
grp[any]  = sum(diff)over( partition by id  order by cv(rn)) )))
where nb !=0 or cpt_diff=0

ID          NB
--- ----------
1-1          0
2-2          2
2-2          4

3 rows selected.``````
• ###### 11. Re: SQL - Find Continuous Records
I have used Tabibitosan method B-)
Tabibitosan method tutorial by Aketi Jyuuzou
``````with tab as (
select '1-1' id,'SAND' lith,150 depth from dual union
select '1-1' id,'COAL' lith,200 depth from dual union
select '1-1' id,'SAND' lith,250 depth from dual union
select '1-1' id,'COAL' lith,300 depth from dual union
select '2-2' id,'SAND' lith, 75 depth from dual union
select '2-2' id,'COAL' lith,100 depth from dual union
select '2-2' id,'COAL' lith,150 depth from dual union
select '2-2' id,'COAL' lith,200 depth from dual union
select '2-2' id,'COAL' lith,250 depth from dual union
select '2-2' id,'SAND' lith,300 depth from dual union
select '2-2' id,'COAL' lith,400 depth from dual union
select '2-2' id,'COAL' lith,450 depth from dual)
select id,lith,count(*)
from (select id,lith,
Row_Number() over(partition by id      order by depth)
-Row_Number() over(partition by id,lith order by depth) dis
from tab)
group by id,lith,dis
having count(*) > 1;

ID   LITH   COUNT(*)
---  ----  ---------
2-2  COAL          2
2-2  COAL          4``````
• ###### 12. Re: SQL - Find Continuous Records
Nice!

C.
• ###### 13. Re: SQL - Find Continuous Records
Wow, very Nice!
• ###### 14. Re: SQL - Find Continuous Records
hi kyle,
could u tell me the bold item in table , is it outside the database ?? what exactly it is?? how u put data in bold letter in table in oracle database??

thnx...
1 2 Previous Next