SQL how to change columns value in next 2 records

Question

I have a table which contains two columns, one named "ID", and the other is "FLG". I need to add anther column "FLG1" that based on column "FLG".

The process logic is that if one record is "Y" in this table, then the next 2 records should be both "N" in "FLG1", if it is "N", then just keep it in "FLG1".

For example:

For record which ID=2 and FLG=Y,then FLG1 of this record should be "Y", but for the next 2 records（ID=3,4), FLG1 should be "N", though their FLG is "Y".

The expected result is as below:

I have tried many ways for days but failed and I don't want a stored procedure, I just want SQL query scripts for the implementation.

Here is the scripts for the data:

 select 1 as ID, 'N' as FLG from dual union all
 select 2 as ID, 'Y' as FLG from dual union all
 select 3 as ID, 'Y' as FLG from dual union all
 select 4 as ID, 'Y' as FLG from dual union all
 select 5 as ID, 'Y' as FLG from dual union all
 select 6 as ID, 'Y' as FLG from dual union all
 select 7 as ID, 'Y' as FLG from dual union all
 select 8 as ID, 'Y' as FLG from dual union all
 select 9 as ID, 'N' as FLG from dual union all
 select 10 as ID, 'Y' as FLG from dual union all
 select 11 as ID, 'N' as FLG from dual union all
 select 12 as ID, 'Y' as FLG from dual union all
 select 13 as ID, 'N' as FLG from dual union all
 select 14 as ID, 'N' as FLG from dual union all
 select 15 as ID, 'N' as FLG from dual union all
 select 16 as ID, 'Y' as FLG from dual union all
 select 17 as ID, 'N' as FLG from dual union all
 select 18 as ID, 'N' as FLG from dual union all
 select 19 as ID, 'N' as FLG from dual union all
 select 20 as ID, 'N' as FLG from dual union all
 select 21 as ID, 'N' as FLG from dual union all
 select 22 as ID, 'Y' as FLG from dual union all
 select 23 as ID, 'Y' as FLG from dual

Answer 1

In my misinterpretation of the question, this is a pretty simple gaps-and-islands problem. See the edit below for an improved answer. I would suggest using the difference of row numbers to define the islands. The definition of the flag is then just checking the row number on each group of 'Y' values:

select id, flg,
       (case when flg = 'Y' and
                  mod(row_number() over (partition by flg, seqnum - seqnum_2 order by id), 3) = 1
             then 'Y'
             else 'N'
        end) as flg1
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by flg order by id) as seqnum_2
      from t
     ) t
order by id;

Here is a db<>fiddle.

If you want to update the flag, I would recommend using merge.

Note: I would also expect this to be faster (perhaps much faster) than a recursive CTE approach.

EDIT:

Alex makes a really good point. I think this requires a recursive CTE. If you have a large amount of data, it might be possible to optimize it by splitting the data into groups where you have multiple 'N's in a row. Your question doesn't mention data size.

I would approach this as:

with tt as (
       select t.*, row_number() over (order by id) as seqnum
       from t
     ),
     cte (seqnum, id, flg, flg1, counter) as (
      select seqnum, id, flg, flg,
             (case when flg = 'Y' then 1 else 0 end)
      from tt
      where seqnum = 1
      union all
      select tt.seqnum, tt.id, tt.flg,
             (case when cte.counter in (1, 2) then 'N'
                   when tt.flg = 'Y' then 'Y'
                   else 'N'
              end),
             (case when cte.counter in (1, 2) then cte.counter + 1
                   when tt.flg = 'Y' then 1
                   else 0
              end)
      from cte join
            tt
            on tt.seqnum = cte.seqnum + 1
     )
select *
from cte;

Basically, this walks through the data and finds the first 'Y'. At that point, it sets a counter to 1. In the next two rows, the counter is incremented, regardless of the value of the flag. Then it goes back to looking for a 'Y' to repeat the process.

Amusingly, this seems like a pretty simple operation to implement using a Turing machine. Usually, it is not obvious how to implement such things.

Interestingly, if you put all the flags in a string, regular expressions solve the problem very simply:

select flgs,
       substr(regexp_replace(flgs, 'Y(..|.$|$)', 'YNN'), 1, length(flgs)) as flg1s
from (select listagg(flg, '') within group (order by id) as flgs
      from t
     ) t;

Answer 2

Use `LAG` to see the previous two rows. Then use `CASE WHEN` to decide what to show.

select 
  id,
  flg,
  case
    when lag(flg) over(order by id) = 'Y' or lag(flg, 2) over(order by id) = 'Y' then 'N'
    else flg
  end as flg1
from mytable
order by id;

Your description is wrong. You want to iterate through your rows for which you'd use a recursive query in SQL.

Number your rows first, because there can always be gaps in a table's IDs. Then use a recursive query to loop through the rows. I think this is the straight-forward to approach this.

with numbered as (select t.*, row_number() over (order by id) as rn from mytable t)
, cte (id, flg, flg1, prev_flg1, rn) as
(
  select id, flg, flg, null, rn from numbered where rn = 1
  union all
  select
    t.id,
    t.flg,
    case
      when cte.flg1 = 'Y' or cte.prev_flg1 = 'Y' then 'N'
      else t.flg
    end,
    cte.flg1,
    t.rn
  from cte 
  join numbered t on t.rn = cte.rn + 1
)
select id, flg, flg1
from cte
order by id;

Answer 3

You need to iterate over the rows sequentially and calculate whether to show the row before you can calculate whether to show row following it; this means you need a recursive (or hierarchical) query.

You need to split the rows up into triplets of sequential rows such that each triplet starts with a FLG = 'Y' row and then all rows that are not in such a triplet or are in the second or third rows of the triplet will have a FLG1 value of N.

Like this:

WITH find_triplets ( id, flg, flg_count ) AS (
  SELECT id,
         flg,
         DECODE( flg, 'Y', 1, 0 )
  FROM   table_name
  WHERE  id = 1
UNION ALL
  SELECT t.id,
         t.flg,
         CASE f.flg_count
         WHEN 0
         THEN DECODE( t.flg, 'Y', 1, 0 )
         ELSE MOD( f.flg_count + 1, 3 )
         END
  FROM   find_triplets f
         INNER JOIN table_name t
         ON ( t.id = f.id + 1 )
)
SELECT id,
       flg,
       CASE
       WHEN flg = 'Y' AND flg_count = 1
       THEN 'Y'
       ELSE 'N'
       END as flg1
FROM   find_triplets
ORDER BY id

Which, for your sample data, outputs:

ID | FLG | FLG1
-: | :-- | :---
 1 | N   | N   
 2 | Y   | Y   
 3 | Y   | N   
 4 | Y   | N   
 5 | Y   | Y   
 6 | Y   | N   
 7 | Y   | N   
 8 | Y   | Y   
 9 | N   | N   
10 | Y   | N   
11 | N   | N   
12 | Y   | Y   
13 | N   | N   
14 | N   | N   
15 | N   | N   
16 | Y   | Y   
17 | N   | N   
18 | N   | N   
19 | N   | N   
20 | N   | N   
21 | N   | N   
22 | Y   | Y   
23 | Y   | N   

db<>fiddle here