Tabibitosan Method: Example 8


In the below example, 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.

with ex11 as (
select '1-1' id,'SAND' lith,150 t_depth from dual union
select '1-1' id,'COAL' lith,200 t_depth from dual union
select '1-1' id,'SAND' lith,250 t_depth from dual union
select '1-1' id,'COAL' lith,300 t_depth from dual union
select '2-2' id,'SAND' lith, 75 t_depth from dual union
select '2-2' id,'COAL' lith,100 t_depth from dual union
select '2-2' id,'COAL' lith,150 t_depth from dual union
select '2-2' id,'COAL' lith,200 t_depth from dual union
select '2-2' id,'COAL' lith,250 t_depth from dual union
select '2-2' id,'SAND' lith,300 t_depth from dual union
select '2-2' id,'COAL' lith,400 t_depth from dual union
select '2-2' id,'COAL' lith,450 t_depth from dual)
select * from ex11;

Table created.

SQL> select * from ex11 order by lith,id;

ID  LITH    T_DEPTH
--- ---- ----------
1-1 COAL        200
1-1 COAL        300
1-1 SAND        150
1-1 SAND        250
2-2 COAL        100
2-2 COAL        150
2-2 COAL        200
2-2 COAL        250
2-2 COAL        400
2-2 COAL        450
2-2 SAND         75
2-2 SAND        300

12 rows selected.

SQL> select
  2   id,
  3   lith,
  4   row_number() over (partition by id  order by id,t_depth) rn1,
  5   t_depth
  6  from ex11;

ID  LITH        RN1    T_DEPTH
--- ---- ---------- ----------
1-1 SAND          1        150
1-1 COAL          2        200
1-1 SAND          3        250
1-1 COAL          4        300
2-2 SAND          1         75
2-2 COAL          2        100
2-2 COAL          3        150
2-2 COAL          4        200
2-2 COAL          5        250
2-2 SAND          6        300
2-2 COAL          7        400
2-2 COAL          8        450

12 rows selected.

Expected output:

1-1 null
2-2 4
2-2 2


SQL> select
  2   id,
  3   lith,
  4   row_number() over (partition by id  order by id,t_depth) r1,
  5   row_number() over (partition by id,lith order by id,t_depth) r2,
  6   row_number() over (partition by id  order by id,t_depth)
  7    - row_number() over (partition by id,lith order by id,t_depth) r3,
  8   t_depth
  9  from ex11;

ID  LITH         R1         R2         R3    T_DEPTH
--- ---- ---------- ---------- ---------- ----------
1-1 SAND          1          1          0        150
1-1 COAL          2          1          1        200
1-1 SAND          3          2          1        250
1-1 COAL          4          2          2        300
2-2 SAND          1          1          0         75
2-2 COAL          2          1          1        100
2-2 COAL          3          2          1        150
2-2 COAL          4          3          1        200
2-2 COAL          5          4          1        250
2-2 SAND          6          2          4        300
2-2 COAL          7          5          2        400
2-2 COAL          8          6          2        450

12 rows selected.


SQL> select id,
  2         cnt
  3   from  
  4   (
  5   select  id,
  6     lith,
  7     count(*) cnt    
  8   from  (
  9     select
 10      id,
 11      lith,
 12      --row_number() over (partition by id  order by id,t_depth) r1,
 13      --row_number() over (partition by id,lith order by id,t_depth) r2,
 14      row_number() over (partition by id  order by id,t_depth)
 15       - row_number() over (partition by id,lith order by id,t_depth) r3
 16      --t_depth
 17     from ex11
 18     )
 19   group by id, lith,r3 );

ID         CNT
--- ----------
1-1          1
1-1          1
1-1          1
1-1          1
2-2          4
2-2          2
2-2          1
2-2          1

8 rows selected.

sort cnt in descending order to get something like below

ID         CNT
--- ----------
1-1          1    1
1-1          1    2
1-1          1    3
1-1          1    4
2-2          4    1
2-2          2    2
2-2          1    3
2-2          1    4

SQL> select id,
  2         cnt,
  3      row_number() over ( partition by id order by cnt desc) rn
  4   from  
  5   (
  6   select  id,
  7     lith,
  8     count(*) cnt    
  9   from  (
 10     select
 11      id,
 12      lith,
 13      --row_number() over (partition by id  order by id,t_depth) r1,
 14      --row_number() over (partition by id,lith order by id,t_depth) r2,
 15      row_number() over (partition by id  order by id,t_depth)
 16       - row_number() over (partition by id,lith order by id,t_depth) r3
 17      --t_depth
 18     from ex11
 19     )
 20   group by id, lith,r3 );

ID         CNT         RN
--- ---------- ----------
1-1          1          1
1-1          1          2
1-1          1          3
1-1          1          4
2-2          4          1
2-2          2          2
2-2          1          3
2-2          1          4

8 rows selected.

consider rn=1 for both id and cnt >1

SQL>   select id, cnt
  2    from (
  3      select id,
  4      cnt,
  5      row_number() over ( partition by id order by cnt desc) rn
  6      from  
  7      (
  8      select  id,
  9     lith,
 10     count(*) cnt    
 11      from  (
 12     select
 13      id,
 14      lith,
 15      --row_number() over (partition by id  order by id,t_depth) r1,
 16      --row_number() over (partition by id,lith order by id,t_depth) r2,
 17      row_number() over (partition by id  order by id,t_depth)
 18       - row_number() over (partition by id,lith order by id,t_depth) r3
 19      --t_depth
 20     from ex11
 21     )
 22      group by id, lith,r3 ))
 23    where rn=1 or cnt>1;

ID         CNT
--- ----------
1-1          1
2-2          4
2-2          2


SQL>   select id, nullif(cnt,1) cnt
  2    from (
  3      select id,
  4      cnt,
  5      row_number() over ( partition by id order by cnt desc) rn
  6      from  
  7      (
  8      select  id,
  9     lith,
 10     count(*) cnt    
 11      from  (
 12     select
 13      id,
 14      lith,
 15      --row_number() over (partition by id  order by id,t_depth) r1,
 16      --row_number() over (partition by id,lith order by id,t_depth) r2,
 17      row_number() over (partition by id  order by id,t_depth)
 18       - row_number() over (partition by id,lith order by id,t_depth) r3
 19      --t_depth
 20     from ex11
 21     )
 22      group by id, lith,r3 ))
 23    where rn=1 or cnt>1;

ID         CNT
--- ----------
1-1
2-2          4
2-2          2