Forum Stats

  • 3,839,017 Users
  • 2,262,438 Discussions
  • 7,900,836 Comments

Discussions

SQL - Find Continuous Records

752564
752564 Member Posts: 6
edited Feb 10, 2010 9:02PM in SQL & PL/SQL
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.
Tagged:

Best Answer

  • 730428
    730428 Member Posts: 2,087
    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/]
«1

Answers

  • riedelme
    riedelme Member Posts: 3,528
    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
    752564 Member Posts: 6
    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
    cd_2 Member Posts: 5,021
    Looks like you want to try analytic functions such as LEAD/LAG.

    C.
    cd_2
  • 752564
    752564 Member Posts: 6
    That sounds interesting, could you be more specific. Some pseudocode would be very helpful...
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    follow the link for a complete example and explanation:
    http://nuijten.blogspot.com/2009/06/analytic-function-finding-gaps.html
    21205
  • 730428
    730428 Member Posts: 2,087
    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/]
  • 752564
    752564 Member Posts: 6
    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
    Alessandro Rossi Member Posts: 1,057 Bronze Badge
    edited Feb 9, 2010 12:12PM
    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
    Alessandro Rossi
  • cd_2
    cd_2 Member Posts: 5,021
    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.
    cd_2
  • 666352
    666352 Member Posts: 1,442
    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.
    666352
This discussion has been closed.