时间戳

首页 > 解决方案 > Oracle：使用来自同一表的聚合值更新表中的值

oracle - Oracle：使用来自同一表的聚合值更新表中的值

问题描述

我正在寻找一种可能更好的方法来解决这个问题。

我在 Oracle 11.2 中创建了一个临时表，我用它来预先计算在其他选择中需要的值，而不是每次选择都再次生成它们。

create global temporary table temp_foo (
    DT                  timestamp(6), --only the date part will be used in this example but for later things I will need the time
    Something           varchar2(100), 
    Customer            varchar2(100), 
    MinDate             timestamp(6), 
    MaxDate             timestamp(6),
    Filecount           int, 
    Errorcount          int,
    AvgFilecount        int,
    constraint PK_foo primary key (DT, Customer)
) on commit preserve rows;

然后，我首先为除AvgFilecount. AvgFilecount应包含Filecount3 个先前记录的平均值（按 中的日期DT）。结果转成int没关系，我不需要小数位

DT           | Customer | Filecount | AvgFilecount
2019-04-30   | x        | 10        | avg(2+3+9)
2019-04-29   | x        | 2         | based on values before this
2019-04-28   | x        | 3         | based on values before this
2019-04-27   | x        | 9         | based on values before this

我考虑过使用普通的 UPDATE 语句，因为这应该比遍历值更快。我应该提到该领域没有空白，DT但显然有第一个我找不到任何以前的记录。如果我要循环，我可以很容易地计算出AvgFilecount我(the record before previous record/2 + previous record)/3不能用 UPDATE 计算出的，因为我不能保证它们的执行顺序。所以我可以只取最后 3 条记录（通过 DT）并从那里计算。

我认为会是一个简单的更新让我很头疼。我主要在做 SQL Server，我只会加入其他 3 条记录，但在 Oracle 中似乎有点不同。我找到了https://stackoverflow.com/a/2446834/4040068并想在答案中使用第二种方法。

update 
(select curr.DT, curr.temp_foo, curr.Filecount, curr.AvgFilecount as OLD, (coalesce(Minus1.Filecount, 0) + coalesce(Minus2.Filecount, 0) + coalesce(Minus3.Filecount, 0)) / 3 as NEW
 from temp_foo curr
 left join temp_foo Minus1 ON Minus1.Customer = curr.Customer and trunc(Minus1.DT) = trunc(curr.DT-1)
 left join temp_foo Minus2 ON Minus2.Customer = curr.Customer and trunc(Minus2.DT) = trunc(curr.DT-2)
 left join temp_foo Minus3 ON Minus3.Customer = curr.Customer and trunc(Minus3.DT) = curr.DT-3
 order by 1, 2
)
set OLD = NEW;

这给了我一个

ORA-01779: 无法修改映射到非键保留表 01779 的列。 00000 - “无法修改映射到非键保留表的列” *原因：尝试插入或更新连接视图，映射到非键保留表。*行动：直接修改基础基表。

我认为这应该可以工作，因为两个连接条件都在主键中，因此是唯一的。我目前正在实施上述答案中的第一种方法，但它变得相当大，感觉应该有更好的解决方案。

我想尝试的其他事情：

编辑：我的插入现在看起来像这样。我已经汇总了一天的 Filecount，因为每个 per 可以有多个DT记录。CustomerSomething

insert into temp_foo (DT, Something, Customer, Filecount)
select dates.DT, tbl1.Something, tbl1.Customer, coalesce(sum(tbl3.Filecount),0)
from table(Function_Returning_Daterange(NULL, NULL)) dates
cross join
    (SELECT Something, 
        Code, 
        Value
    FROM Table2 tbl2
    WHERE (Something = 'Value')) tbl1
left outer join Table3 tbl3
    on      tbl3.Customer = tbl1.Customer
    and     trunc(tbl3.MinDate) = trunc(dates.DT)
group by dates.DT, tbl1.Something, tbl1.Customer;

标签： oraclesql-update

解决方案

您可以使用带有窗口子句的分析平均值：

select dt, customer, filecount,
  avg(filecount) over (partition by customer order by dt
    rows between 3 preceding and 1 preceding) as avgfilecount
from tmp_foo
order by dt desc;

DT         CUSTOMER  FILECOUNT AVGFILECOUNT
---------- -------- ---------- ------------
2019-04-30 x                10   4.66666667
2019-04-29 x                 2            6
2019-04-28 x                 3            9
2019-04-27 x                 9

然后使用合并语句执行更新部分：

merge into tmp_foo t
using (
  select dt, customer,
    avg(filecount) over (partition by customer order by dt
      rows between 3 preceding and 1 preceding) as avgfilecount
  from tmp_foo
) s
on (s.dt = t.dt and s.customer = t.customer)
when matched then update set t.avgfilecount = s.avgfilecount;

4 rows merged.

select dt, customer, filecount, avgfilecount
from tmp_foo
order by dt desc;

DT         CUSTOMER  FILECOUNT AVGFILECOUNT
---------- -------- ---------- ------------
2019-04-30 x                10   4.66666667
2019-04-29 x                 2            6
2019-04-28 x                 3            9
2019-04-27 x                 9

您还没有显示原始插入语句；可以将分析计算添加到其中，并避免单独的更新步骤。

此外，如果您希望计算前两个日期值，就好像它们之前的“缺失”额外天数为零，您可以使用sum和除法而不是avg：

select dt, customer, filecount,
  sum(filecount) over (partition by customer order by dt
    rows between 3 preceding and 1 preceding)/3 as avgfilecount
from tmp_foo
order by dt desc;

DT         CUSTOMER  FILECOUNT AVGFILECOUNT
---------- -------- ---------- ------------
2019-04-30 x                10   4.66666667
2019-04-29 x                 2            4
2019-04-28 x                 3            3
2019-04-27 x                 9

这取决于您期望最后计算的值是什么。

推荐阅读