1 2 Previous Next 16 Replies Latest reply: Feb 10, 2010 7:25 PM by Aketi Jyuuzou RSS

    SQL - Find Continuous Records

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

              C.
              • 4. Re: SQL - Find Continuous Records
                752564
                That sounds interesting, could you be more specific. Some pseudocode would be very helpful...
                • 5. Re: SQL - Find Continuous Records
                  21205
                  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
                    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
                      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
                        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
                          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.
                          • 10. Re: SQL - Find Continuous Records
                            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.
                            • 11. Re: SQL - Find Continuous Records
                              Aketi Jyuuzou
                              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
                                cd_2
                                Nice!

                                C.
                                • 13. Re: SQL - Find Continuous Records
                                  752564
                                  Wow, very Nice!
                                  • 14. Re: SQL - Find Continuous Records
                                    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...
                                    1 2 Previous Next