Oracle: Update values in table with aggregated values from same table

Question

I am looking for a possibly better approach to this.

I have created a temp table in Oracle 11.2 that I'm using to pre calculate values that I will need in other selects instead of always generating them again with each select.

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;

I then first insert some fixed values for everything except AvgFilecount. AvgFilecount should contain the average for the Filecount for the 3 previous records (going by the date in DT). It doesn’t matter that the result will be converted to an int, I don’t need the decimal places

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

I thought about using a normal UPDATE statement as this should be faster than looping through the values. I should mention that there are no gaps in the DT field but obviously there is a first one where I won‘t find any previous records. If I would loop through, I could easily calculate AvgFilecount with (the record before previous record/2 + previous record)/3 which I cannot with UPDATE as I cannot guarantee the order of how they are executed. So I‘m fine with just taking the last 3 records (going by DT) and calcuting it from there.

What I thought would be an easy update is giving me headaches. I‘m mostly doing SQL Server where I would just join the 3 other records but it seems is a bit different in Oracle. I have found https://stackoverflow.com/a/2446834/4040068 and wanted to use the second approach in the answer.

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;

Which gives me an

ORA-01779: cannot modify a column which maps to a non key-preserved table 01779. 00000 - "cannot modify a column which maps to a non key-preserved table" *Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table. *Action: Modify the underlying base tables directly.

I thought this should work as both join conditions are in the primary key and thus unique. I am currently implementing the first approach in the above mentioned answer but it is getting quite big and it feels like there should be a better solution to this.

Other things I thought about trying:

using a nested subselect (nested because Oracle doesn’t know top(n) and I need to sort the subselect) to select the previous 3 records ordered by DT and then he outer select with rownum <=3 and then I could just use AVG(). However, I was told subselect can be quite slow and joins are better in Oracle performance wise. Dunno if that is really the case, haven‘t done any testing

Edit: My insert right now looks like this. I am already aggregating the Filecount for a day as there can be multiple records per DT per Customer per Something.

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;

score 2 · Accepted Answer · answered May 09 '19 at 09:40

You could use an analytic average with a window clause:

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

and then do the update part with a merge statement:

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

You haven't shown your original insert statement; it might be possible to add the analytic calculation to that, and avoid the separate update step.

Also, if you want the first two date values to be calculated as if the 'missing' extra days before them had zero counts, you could use sum and division instead of 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

It depends what you expect those last calculated values to be.

Sorry for the late answer. I added my insert in the post (at the end as edit). I guess I thought as I already had problems with the rest I split it up in two parts but that is totally not necessary. — bugybunny, May 13 '19 at 06:47

Oracle: Update values in table with aggregated values from same table

1 Answers1