SQL How to update previous and next row value without using lead and lag?-CodePudding

I am trying to flag some rows based on a value in a a column, but I also need to put same flag for previous and next row as well based on the current row value.

so below is my table

-- create a table
CREATE TABLE table1 (
  id INTEGER PRIMARY KEY,
  time INTEGER,
  event varchar NOT NULL
  );
-- insert some values
INSERT INTO table1 VALUES (1, '1', 'r');
INSERT INTO table1 VALUES (2, '2', 'r');
INSERT INTO table1 VALUES (3, '3', 's');
INSERT INTO table1 VALUES (4, '4', 'r');
INSERT INTO table1 VALUES (5, '5', 'r');
INSERT INTO table1 VALUES (6, '6', 'r');
INSERT INTO table1 VALUES (7, '7', 's');
INSERT INTO table1 VALUES (8, '8', 'r');
INSERT INTO table1 VALUES (9, '9', 'r');
INSERT INTO table1 VALUES (10, '10', 's');

I want to add a column flag that contains 0 for event='s' and also for it's previous and next row as well. but cannot use lead or lag or temp table due to system constraints.

so my final output looks like this

 ----------- -------- ------ 
| timestamp | events | flag |
 ----------- -------- ------ 
|         1 | r      |    1 |
|         2 | r      |    0 |
|         3 | s      |    0 |
|         4 | r      |    0 |
|         5 | r      |    1 |
|         6 | r      |    1 |
|         7 | r      |    0 |
|         8 | s      |    0 |
|         9 | r      |    0 |
|        10 | r      |    0 |
|        11 | s      |    0 |
 ----------- -------- ------

what I have tried so far is following

SELECT a.time, a.event, 0 as flag
FROM table1 AS a
JOIN table1 AS b
ON b.event = 's' AND abs(a.id - b.id) <= 1

I get all the rows which I need to flag as 0 but missing out on 1

TimeStamp is ordered time but for ease of solving converted it to integer.

CodePudding user response：

Another alternative. To address the possibility of gaps, I use row_number to generate a gap-less sequence and then use a self-join to avoid LEAD and LAG.

with cte as (select *, ROW_NUMBER() over (order by time) as rno from table1
)
select main.*, 
    case when main.event = 's' then 0 
    when main.event <> 's' and after.event = 's' then 0 
    when main.event <> 's' and prior.event = 's' then 0 
    else 1 end as [flag], 
    prior.rno as [r-1], prior.id as [prior id], prior.event as [prior event],
    after.rno as [r 1], after.id as [after id], after.event as [after event]
from cte as main 
left join cte as prior on main.rno = prior.rno   1
left join cte as after on main.rno = after.rno - 1
order by main.rno;

fiddle to demonstrate - containing some extra rows with gaps to illustrate. It is not clear what logic is most appropriate for choosing the prior/next rows so I used the "time" column.

CodePudding user response：

Try the following:

With CTE As
(
  Select id, time, event,
  Case
      When event='r' then -10 else id
  End as f
  From table1
)
Select id, time, event,
Case 
    when id in 
    (select f from cte where f<>-10
    union all
    select f 1 from cte where f<>-10
    union all
    select f-1 from cte where f<>-10) then 0 else 1
End As flag
From CTE

Where the -10 in When event='r' then -10 else id is any integer value not existed in the id column even if it has been added by 1.

See a demo from db<>fiddle.