In PL / SQL, how do you update a row based on the next row?

Question

I am using Oracle PL / SQL.

I have a timestamped table T and I want to set the row value for column A to the same as the previous row if they are sorted by columns B and Timestamp, provided that the timestamps do not differ by more than 45 seconds.

In pseudo code, it's something like:

UPDATE T t_curr
  SET A =
    (SELECT A
      FROM T t_prev
      INNER JOIN t_curr
        ON (t_prev is the row right before t_curr, when you sort by B and Timestamp)
          AND t_curr.Timestamp - t_prev.Timestamp < 45
    )

I tried this:

UPDATE T t_curr
  SET A =
    (SELECT A
      FROM T t_prev
      INNER JOIN t_curr
        ON RANK (t_curr)
          OVER (B, Timestamp)
          = 1 + RANK (t_prev)
          OVER (B, Timestmap)
          AND t_curr.Timestamp - t_prev.Timestamp < 45
    )

But I got:

Error (38.16): PL / SQL: ORA-00934: group function is not allowed here

pointing to the first instance of RANK.

What have I done wrong and how will I get it right?

Answer 1

. , , , . , ), .

merge into t a
using (
  select 
    A, 
    B, 
    timestamp, 
    lag(A) over (order by id, timestamp) as prior_A,
    lag(timestamp) over (order by B, timestamp) as prior_timestamp
  from t) b
on  (a.B = b.B)
when matched then 
  update set a.a = case when b.timestamp-b.prior_timestamp <= 45 
    then b.prior_A else b.A end
when not matched then insert (B) values (null)

Answer 2

- :

update x 
set x = y.A
from T x
join T y
where x.B = (select MAX(B) from T where B < y.B)
and x.Timestamp = (select MAX(Timestamp) from T where Timestamp < y.Timestamp)
and y.Timestamp - x.Timestamp < 45

Answer 3

... , , B, - .... , .

: , , . (// ..) order by. , , . - , , .

update T a
set A = (select 
  new_A
  from (
  select 
    B, 
    A, 
    timestamp, 
    first_value(A) 
      over (order by timestamp range between 45 preceding and current row) as new_A
  from mike_temp_1
) b where b.id = a.id)

Answer 4

You can try (it might take some tweaking to get everything right, but the idea is to have two identical ordered subqueries connected by offset rows)

update T set a = (select A1
                 from (
                       select S1.A A1, rownum r1
                       from (select * from T order by B, timestamp) S1
                       left outer join
                       select S2.A A2, rownum r2
                       from (select * from T order by B, timestamp) S2
                       on r1 = r2-1
                      )
                  )

Answer 5

What you can do is.

update t
set colToUpdate = nextValue
from  (
select A
      ,B
      ,C
      ,(LEAD(B, 1, null) over (order by A)) as nextValue
  FROM db.schema.table
  ) as t
    where colToUpdate is null

This requires that the column you want to update is null if you do not want to update all of them.