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.
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?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 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. 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.
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