Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.9K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
SQL - Find Continuous Records

752564
Member Posts: 6
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.
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.
Best 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/]
Answers
-
user9010601 wrote: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
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. -
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.
-
Looks like you want to try analytic functions such as LEAD/LAG.
C. -
That sounds interesting, could you be more specific. Some pseudocode would be very helpful...
-
follow the link for a complete example and explanation:
http://nuijten.blogspot.com/2009/06/analytic-function-finding-gaps.html -
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/] -
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! -
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 -
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. -
Or with 10g
Regards Salimselect 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.
This discussion has been closed.