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

问题描述：

我正在使用Oracle PL/SQL。在PL/SQL中，如何根据下一行更新一行？

我有一个时间戳表T，我想设置一个列A的值与上一行的相同，如果它们按列B和时间戳排序，假设时间戳不是相差超过45秒。

伪代码，它是这样的：

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：此处不允许使用组功能

指向RANK的第一个实例的

。

我做错了什么，以及我如何得到这个权利？

我正要建议使用'lag'或'lead'，但这可能无法工作......或者您可以尝试更新T set a =从Q中选择Q1.A（（从T中选择A，rownum r1） Q1离开外部连接（从T中选择A，rownum r2）Q1.r1 = Q2.r2-1）' – FrustratedWithFormsDesigner 2010-11-11 15:33:22

@FrustratedWithFormsDesigner - 你说得对，滞后和导致同样的问题。尽管如此，我根据您的其他建议编译了某些内容，非常感谢！如果你想把它复制到我接受的答案中，那就没问题。 – 2010-11-11 15:46:13

完成！（我起初发表评论，因为我从来没有尝试过这个更新，并不知道它会工作）;） – FrustratedWithFormsDesigner 2010-11-11 15:53:39

答

尝试使用合并语句。不确定它完全符合你的要求，但它应该可以工作。不幸的是插入条款是必要的），但不应该被称为。

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)

谢谢！我得到了一些基于此的东西来进行编译，而且它对我来说应该是有用的。 – 2010-11-11 21:08:53

我仍然认为你可能需要看看你的要求当你在45秒之内完成几次交易时会发生什么？例如，三行相隔40秒？ – 2010-11-11 22:28:09

答

你可以尝试这样的事：

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

我觉得这样会导致性能问题，不是吗？我正在处理数以万计的行。 – 2010-11-11 15:37:43

@ MOE37x3我不会假设任何东西......根据你所说的话，成千上万的行实际上并没有那么多。 – Fosco 2010-11-11 15:49:17

答

你可以尝试（可能需要一些调整，以得到它的权利，但这个想法是两个相同的有序的子查询由偏移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 
        ) 
       )

它看起来像rownum不考虑秩序。它只是按行访问的顺序进行。 http://www.dbforums.com/oracle/988716-rownum-order.html – 2010-11-11 16:00:54

@ MOE37x3：我知道，我的第一个没有订购。我在B和时间戳字段上添加了排序。那对你有用吗？ – FrustratedWithFormsDesigner 2010-11-11 16:06:27

根据我读过的内容，rownum在订购前会被应用，所以数字会在那里，行将按照指定的顺序排列，但数字可能会按照其他顺序排列。 – 2010-11-11 16:09:29

答

而另一种选择......并不完全做不想要的东西，因为它忽略了需求进行排序B，而它可能给你一些思考....不表定义和事情是有点难以掌握所需要的东西。

编辑：再次阅读问题时，它看起来像你的语法是错误的。组功能（领先/滞后/排名等）只能出现在选择列表或排序条款中。他们在连接之后进行评估，在那里，分组并且有条款。所以像下面显示的东西应该工作。

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)

感谢您解释我的代码中的错误。 – 2010-11-11 21:07:54

答

你可以做的是。

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

这要求您要更新的列为空，除非您要更新所有列。

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

相关推荐