Adding column based on partition by – Oracle

Table:

Col1       Col2         Col3
43         1234         abc
42         1234         abc
41         1234         abc
35         1234         abc
34         5678         def

Objective is to create a new column Col4 as 1 or 0 based on following:

Within each partition by with Col2 and Col3,
either

if Col1-1 does not exists (or)
if Col1-1 (and) Col1-2 both values exists in table,
then Col4 is 1, else 0.

Output Table:

 Col1            Col2         Col3        Col4
 43              1234         abc         1
 42              1234         abc         0
 41              1234         abc         1
 35              1234         abc         1
 34              5678         def         1

Answer

If I am following the logic you describe:

select t.*,
       (case when lag(col1) over (partition by col2, col3 order by col1) <> col1 - 1
             then 1
             when lag(col1) over (partition by col2, col3 order by col1) is null
             then 1
             when lag(col1, 2) over (partition by col2, col3 order by col1) = col1 - 2
             then 1
             else 0
        end) as col4
from t;

Here is a db<>fiddle.

Advertisement

Answer