在PL/SQL中，如何根据下一行更新一行?

问题描述：

我正在使用Oracle PL/SQL.

I'm using Oracle PL/SQL.

我有一个带时间戳的表T，并且我想将A列的值设置为与上一行相同，如果它们是按B列和Timestamp排序的，则前提是时间戳不相同超过45秒.

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
    )

我尝试过:

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
    )

但是我得到了

错误(38,16):PL/SQL:ORA-00934:此处不允许使用组功能

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

指向RANK的第一个实例.

pointing at the first instance of RANK.

我做错了什么，我该怎么做呢?

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

答

尝试使用merge语句.不确定它是否完全符合您的要求，但它是否可以正常工作.不幸的是，insert子句是必需的)，但永远不应该被调用.

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)

在PL/SQL中，如何根据下一行更新一行?

相关推荐