Oracle self join starting with minimum value for each partition

Viewed 122

I have this table:

COL1    COL2    COL3
--------------------
   A       1    VAL1
   A       2    VAL2
   A       4    VAL3
   B       2    VAL4
   B       4    VAL5
   B       5    VAL6

And I would like to obtain this output:

COL1    COL2    COL3
--------------------
   A       1    VAL1
   A       2    VAL2
   A       3    NULL
   B       2    VAL4
   B       3    NULL
   B       4    VAL6

Logic:

with the smallest COL2 value for each partition of COL1, take the following 3 numbers and, if the combination COL1 and COL2 present in the first table, show COL3 and NULL otherwise.

5 Answers

Let's see. Here is the table

select * from t order by col1, col2;

COL1        COL2 COL3
----- ---------- -----
A              1 VAL1
A              2 VAL2
A              4 VAL3
B              2 VAL4
B              4 VAL5
B              5 VAL6

6 rows selected

and now let's try to apply the described logic

with offsets as
 (select level - 1 offset from dual connect by level <= 3),
smallest_col2 as
 (select col1, min(col2) min_col2 from t group by col1)
select sc2.col1, sc2.min_col2 + o.offset col2, t.col3
  from smallest_col2 sc2
 cross join offsets o
  left join t
    on t.col1 = sc2.col1
   and t.col2 = sc2.min_col2 + o.offset
 order by 1, 2;

COL1        COL2 COL3
----- ---------- -----
A              1 VAL1
A              2 VAL2
A              3 
B              2 VAL4
B              3 
B              4 VAL5

6 rows selected

Use a recursive CTE to get the COL2s from the min of each COL1 up to the next 2 and then a left join to the table:

WITH cte(COL1, COL2, max_col2) AS (
  SELECT COL1, MIN(COL2), MIN(COL2) + 2
  FROM tablename
  GROUP BY COL1
  UNION ALL
  SELECT COL1, COL2 + 1, max_col2
  FROM cte
  WHERE COL2 < max_col2
)
SELECT c.COL1, c.COL2, t.COL3
FROM cte c LEFT JOIN tablename t
ON t.COL1 = c.COL1 AND t.COL2 = c.COL2
ORDER BY c.COL1, c.COL2

See the demo.

Your question is a good example of what PARTITIONED OUTER JOIN was created for: DBFiddle

with top3 as (
  select *
  from (
    select
       col1, col2, col3
      ,min(col2)over(partition by col1) min_col2
      ,col2 - min(col2)over(partition by col1) + 1 as rn
    from t
  )
  where col2 < min_col2 + 3
)
select 
   top3.col1
  ,r3.n as col2
  ,top3.col3
from
   top3 
     partition by (col1)
     right join 
      (select level n from dual connect by level<=3) r3
       on r3.n=top3.rn;

As you can see, the first step is to get top3 and then just use partition by (col1) right join r3, where r3 is just generator of 3 rows.

Results:

COL1        COL2 COL3
----- ---------- ----
A              1 VAL1
A              2 VAL2
A              3
B              1 VAL4
B              2
B              3 VAL5

6 rows selected.

Note, this approach allows you to scan your table just once!

The partitioned outer join, already demonstrated in Sayan's answer, is probably the best approach for that part of the assignment (data densification).

For the first part, in Oracle 12.1 and higher you can use the match_recognize clause:

select col1, col2, col3
from   this_table
         match_recognize(
           partition by col1
           order     by col2
           measures  col2 - a.col2 + 1 as rn
           all rows  per match
           pattern   ( ^ a b* )
           define    b as col2 <= a.col2 + 2
         ) 
       partition by (col1)
       right outer join
       (select level as rn from dual connect by level <= 3)  using (rn)
;

Another solution with the "recursive WITH clause"

With rws_numbered (COL1, COL2, COL3, rn) as (
select COL1, COL2, COL3
  , row_number()over(order by col1, col3) rn
from Your_table
)
, cte ( COL1, COL2, COL3, rn ) as (
  select COL1, COL2, COL3, rn
  from rws_numbered
  where rn = 1
  union all
  select 
      t.COL1
    , case when t.col1 = c.col1 then c.col2 + 1 else t.col2 end COL2
    , t.COL3    
    , t.rn
  from rws_numbered t 
  join cte c 
    on c.rn + 1 = t.rn 
)
select COL1, COL2, case when exists (select null from Your_table t where t.COL1 = cte.COL1 and t.COL2 = cte.COL2) then COL3 else null end COL3
from cte
order by 1, 2
;

db<>fiddle

Related