This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 10, 2010 5:25 PM by Aketi Jyuuzou RSS

SQL - Find Continuous Records

752564 Newbie
Currently Being Moderated
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
    riedelme Expert
    Currently Being Moderated
    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
    752564 Newbie
    Currently Being Moderated
    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
    60660 Journeyer
    Currently Being Moderated
    Looks like you want to try analytic functions such as LEAD/LAG.

    C.
  • 4. Re: SQL - Find Continuous Records
    752564 Newbie
    Currently Being Moderated
    That sounds interesting, could you be more specific. Some pseudocode would be very helpful...
  • 5. Re: SQL - Find Continuous Records
    21205 Oracle ACE Director
    Currently Being Moderated
    follow the link for a complete example and explanation:
    http://nuijten.blogspot.com/2009/06/analytic-function-finding-gaps.html
  • 6. Re: SQL - Find Continuous Records
    730428 Guru
    Currently Being Moderated
    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
    752564 Newbie
    Currently Being Moderated
    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
    Alessandro Rossi Journeyer
    Currently Being Moderated
    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
    60660 Journeyer
    Currently Being Moderated
    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
    666352 Expert
    Currently Being Moderated
    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
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    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
    60660 Journeyer
    Currently Being Moderated
    Nice!

    C.
  • 13. Re: SQL - Find Continuous Records
    752564 Newbie
    Currently Being Moderated
    Wow, very Nice!
  • 14. Re: SQL - Find Continuous Records
    750281 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points