Forum Stats

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

Discussions

Categories

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:
· Share on TwitterShare on Facebook

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/]
    · Share on TwitterShare on Facebook
«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
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
  • cd_2
    cd_2 Member Posts: 5,021
    Looks like you want to try analytic functions such as LEAD/LAG.

    C.
    cd_2
    · Share on TwitterShare on Facebook
  • 752564
    752564 Member Posts: 6
    That sounds interesting, could you be more specific. Some pseudocode would be very helpful...
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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/]
    · Share on TwitterShare on Facebook
  • 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!
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
This discussion has been closed.