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

问题:

I'm using Oracle PL/SQL.

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

In pseudocode, 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 at the first instance of RANK.

What did I do wrong, and how do I get this right?

回答1:

Try using a merge statement. Not sure it quite does what you want but it should work. Unfortunately the insert clause is necessary) but shouldn't ever be called.

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)

回答2:

Can you try something like this:

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

回答3:

And another option... doesn't quite do what do want because it ignores the requirement to sort on B but it might give you something to think about.... Without table definitions and things it was a little hard to get a handle on exactly what was required.

Edit: on reading the question again, it looks like your syntax is wrong. Group functions (lead/lag/rank etc) can only appear in the select list or the order by clause. They are evaluated after the joins, where, group by and having clauses. So something like what is shown below should work.

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)

回答4:

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, unless you want to update all of them.

回答5:

You could try (might need some tweaking to get it right, but the idea is two identical ordered subqueries joined by offset rownumbers)

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
                      )
                  )