Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL - Find Continuous Records

752564Feb 9 2010 — edited Feb 10 2010
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.
This post has been answered by 730428 on Feb 9 2010
Jump to Answer

Comments

riedelme
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
752564
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.
cd_2
Looks like you want to try analytic functions such as LEAD/LAG.

C.
1 person found this helpful
752564
That sounds interesting, could you be more specific. Some pseudocode would be very helpful...
21205
follow the link for a complete example and explanation:
http://nuijten.blogspot.com/2009/06/analytic-function-finding-gaps.html
1 person found this helpful
730428
Answer
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/]
Marked as Answer by 752564 · Sep 27 2020
752564
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!
Alessandro Rossi
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
1 person found this helpful
cd_2
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.
1 person found this helpful
666352
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.
1 person found this helpful
Aketi Jyuuzou
I have used Tabibitosan method B-)
1007478 
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
cd_2
Nice!

C.
752564
Wow, very Nice!
750281
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...
752564
Gomzi,

I am not sure that I understand your question. In the example that I provided at the beginning of the thread, I set the text to bold to illustrate what I was looking for from the query, the text is not bold in the database table. If you could clarify your statement, I might be able to help you better.

Kyle
Aketi Jyuuzou
If we want rows whose count(*)=1,we can use below solution ;-)
select id,lith,nullif(cnt,1) as cnt
from (select id,lith,count(*) as cnt,
      Row_Number() over(partition by id order by count(*) desc) as rn
      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)
where cnt > 1 or rn = 1;

ID   LITH   CNT
---  ----  ----
1-1  COAL  null
2-2  COAL     4
2-2  COAL     2
1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 10 2010
Added on Feb 9 2010
#database, #sql
16 comments
12,636 views