This content has been marked as final.
Show 16 replies

1. Re: SQL  Find Continuous Records
riedelme Feb 9, 2010 10:38 AM (in response to 752564)user9010601 wrote:
Can you explain a bit more what you want? What does 11 have to do with null? Or 22 with 4? or 22 with 2? The relationships are not clear
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
11 SAND 150
11 COAL 200
11 SAND 250
11 COAL 300
22 SAND 75
22 COAL 100
22 COAL 150
22 COAL 200
22 COAL 250
22 SAND 300
22 COAL 400
22 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
11 null
22 4
22 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. 
2. Re: SQL  Find Continuous Records
752564 Feb 9, 2010 10:42 AM (in response to riedelme)In the example that I provided, the records with an id of 11 do not have any continuous records therefore the count of the continuous records would return nothing. The 22 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 Feb 9, 2010 10:50 AM (in response to 752564)Looks like you want to try analytic functions such as LEAD/LAG.
C. 
4. Re: SQL  Find Continuous Records
752564 Feb 9, 2010 10:52 AM (in response to cd_2)That sounds interesting, could you be more specific. Some pseudocode would be very helpful... 
5. Re: SQL  Find Continuous Records
21205 Feb 9, 2010 10:54 AM (in response to 752564)follow the link for a complete example and explanation:
http://nuijten.blogspot.com/2009/06/analyticfunctionfindinggaps.html 
6. Re: SQL  Find Continuous Records
730428 Feb 9, 2010 11:02 AM (in response to 752564)The following gives the list of all consecutive (step 50) depth:
And the following hides the records without at least two following records:SQL> with tab as ( 2 select '11' id, 'SAND' lith, 150 depth from dual union 3 select '11' id, 'COAL' lith, 200 from dual union 4 select '11' id, 'SAND' lith, 250 from dual union 5 select '11' id, 'COAL' lith, 300 from dual union 6 select '22' id, 'SAND' lith, 75 from dual union 7 select '22' id, 'COAL' lith, 100 from dual union 8 select '22' id, 'COAL' lith, 150 from dual union 9 select '22' id, 'COAL' lith, 200 from dual union 10 select '22' id, 'COAL' lith, 250 from dual union 11 select '22' id, 'SAND' lith, 300 from dual union 12 select '22' id, 'COAL' lith, 400 from dual union 13 select '22' 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)     11 SAND 150 1 11 COAL 200 1 11 SAND 250 1 11 COAL 300 1 22 SAND 75 1 22 COAL 100 4 22 SAND 300 1 22 COAL 400 2 8 rows selected.
Do you really need the record with ID='11' and count=null?SQL> with tab as ( 2 select '11' id, 'SAND' lith, 150 depth from dual union 3 select '11' id, 'COAL' lith, 200 from dual union 4 select '11' id, 'SAND' lith, 250 from dual union 5 select '11' id, 'COAL' lith, 300 from dual union 6 select '22' id, 'SAND' lith, 75 from dual union 7 select '22' id, 'COAL' lith, 100 from dual union 8 select '22' id, 'COAL' lith, 150 from dual union 9 select '22' id, 'COAL' lith, 200 from dual union 10 select '22' id, 'COAL' lith, 250 from dual union 11 select '22' id, 'SAND' lith, 300 from dual union 12 select '22' id, 'COAL' lith, 400 from dual union 13 select '22' 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   22 4 22 2
Max
[My Italian Oracle bloghttp://oracleitalia.wordpress.com/2010/02/07/aggiornareunatabellaconlistruzionemerge/] 
7. Re: SQL  Find Continuous Records
752564 Feb 9, 2010 11:07 AM (in response to 730428)Massimo,
Thank you very much for the example!!
"Do you really need the record with ID='11' and count=null?" Answer: No, what you have will work perfect, thank you very much! 
8. Re: SQL  Find Continuous Records
Alessandro Rossi Feb 9, 2010 11:12 AM (in response to 752564)Try this
Bye AlessandroSQL> with tab as ( 2 select '11' as id,'SAND' as lith,150 as depth 3 from dual 4 union all 5 select '11' as id,'COAL' as lith,200 as depth 6 from dual 7 union all 8 select '11' as id,'SAND' as lith,250 as depth 9 from dual 10 union all 11 select '11' as id,'COAL' as lith,300 as depth 12 from dual 13 union all 14 select '22' as id,'SAND' as lith,75 as depth 15 from dual 16 union all 17 select '22' as id,'COAL' as lith,100 as depth 18 from dual 19 union all 20 select '22' as id,'COAL' as lith,150 as depth 21 from dual 22 union all 23 select '22' as id,'COAL' as lith,200 as depth 24 from dual 25 union all 26 select '22' as id,'COAL' as lith,250 as depth 27 from dual 28 union all 29 select '22' as id,'SAND' as lith,300 as depth 30 from dual 31 union all 32 select '22' as id,'COAL' as lith,400 as depth 33 from dual 34 union all 35 select '22' 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    11 22 COAL 2 22 COAL 4

9. Re: SQL  Find Continuous Records
cd_2 Feb 9, 2010 1:28 PM (in response to 752564)Borrowing from the examples of other cummunity members, this is what I'd done with "simple" analytic functions:
C.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 ;

10. Re: SQL  Find Continuous Records
666352 Feb 9, 2010 2:49 PM (in response to 752564)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 '11' ID, 'SAND' lith, 150 DEPTH FROM DUAL UNION ALL SELECT '11', 'COAL', 200 FROM DUAL UNION ALL SELECT '11', 'SAND', 250 FROM DUAL UNION ALL SELECT '11', 'COAL', 300 FROM DUAL UNION ALL SELECT '22', 'SAND', 75 FROM DUAL UNION ALL SELECT '22', 'COAL', 100 FROM DUAL UNION ALL SELECT '22', 'COAL', 150 FROM DUAL UNION ALL SELECT '22', 'COAL', 200 FROM DUAL UNION ALL SELECT '22', 'COAL', 250 FROM DUAL UNION ALL SELECT '22', 'SAND', 300 FROM DUAL UNION ALL SELECT '22', 'COAL', 400 FROM DUAL UNION ALL SELECT '22', '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   11 0 22 2 22 4 3 rows selected.

11. Re: SQL  Find Continuous Records
Aketi Jyuuzou Feb 10, 2010 8:02 PM (in response to 752564)I have used Tabibitosan method B)
Tabibitosan method tutorial by Aketi Jyuuzouwith tab as ( select '11' id,'SAND' lith,150 depth from dual union select '11' id,'COAL' lith,200 depth from dual union select '11' id,'SAND' lith,250 depth from dual union select '11' id,'COAL' lith,300 depth from dual union select '22' id,'SAND' lith, 75 depth from dual union select '22' id,'COAL' lith,100 depth from dual union select '22' id,'COAL' lith,150 depth from dual union select '22' id,'COAL' lith,200 depth from dual union select '22' id,'COAL' lith,250 depth from dual union select '22' id,'SAND' lith,300 depth from dual union select '22' id,'COAL' lith,400 depth from dual union select '22' 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(*)    22 COAL 2 22 COAL 4

12. Re: SQL  Find Continuous Records
cd_2 Feb 10, 2010 7:00 AM (in response to Aketi Jyuuzou)Nice!
C. 
13. Re: SQL  Find Continuous Records
752564 Feb 10, 2010 8:40 AM (in response to cd_2)Wow, very Nice! 
14. Re: SQL  Find Continuous Records
750281 Feb 10, 2010 10:09 AM (in response to 752564)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...