sqlite3 update column with rollling average

Question

I want to update a column with the rolling average of the product of two other columns, all in the one table.

create table tb (code str, date str, cl float, vo float);
insert into tb values 
('BHP', '2020-01-03', 3.25, 1000),
('BHP', '2020-01-04', 3.50, 2000), 
('BHP', '2020-01-05', 3.55, 1000), 
('CSR', '2020-01-03', 5.55, 1500), 
('CSR', '2020-01-04', 5.60, 2000), 
('CSR', '2020-01-05', 5.55, 2000), 
('DDG', '2020-01-03', 10.20, 4000), 
('DDG', '2020-01-04', 10.25, 4500), 
('DDG', '2020-01-05', 10.30, 5000);

alter table tb add column dv float;
alter table tb add column avg_dv float;

update tb set dv = (select cl * vo);
select * from tb;

BHP|2020-01-03|3.25|1000.0|3250.0|
BHP|2020-01-04|3.5|2000.0|7000.0|
BHP|2020-01-05|3.55|1000.0|3550.0|
CSR|2020-01-03|5.55|1500.0|8325.0|
CSR|2020-01-04|5.6|2000.0|11200.0|
CSR|2020-01-05|5.55|2000.0|11100.0|
DDG|2020-01-03|10.2|4000.0|40800.0|
DDG|2020-01-04|10.25|4500.0|46125.0|
DDG|2020-01-05|10.3|5000.0|51500.0|

So far, so good. I can select a rolling average -

select code, date, avg(dv) 
over (partition by code order by date asc rows 1 preceding) 
from tb;
BHP|2020-01-03|3250.0
BHP|2020-01-04|5125.0
BHP|2020-01-05|5275.0
CSR|2020-01-03|8325.0
CSR|2020-01-04|9762.5
CSR|2020-01-05|11150.0
DDG|2020-01-03|40800.0
DDG|2020-01-04|43462.5
DDG|2020-01-05|48812.5

but when I try to put that selection into the avg_vo column of the table I don't get what I was expecting -

update tb set avg_dv = 
(select avg(dv) 
   over (partition by code order by date asc rows 1 preceding)
);
select * from tb;
BHP|2020-01-03|3.25|1000.0|3250.0|3250.0
BHP|2020-01-04|3.5|2000.0|7000.0|7000.0
BHP|2020-01-05|3.55|1000.0|3550.0|3550.0
CSR|2020-01-03|5.55|1500.0|8325.0|8325.0
CSR|2020-01-04|5.6|2000.0|11200.0|11200.0
CSR|2020-01-05|5.55|2000.0|11100.0|11100.0
DDG|2020-01-03|10.2|4000.0|40800.0|40800.0
DDG|2020-01-04|10.25|4500.0|46125.0|46125.0
DDG|2020-01-05|10.3|5000.0|51500.0|51500.0

The avg_dv column has been updated with just the dv values, not the rolling average.

I also tried to adapt the answer here as

update tb 
set tb.avg_dv = b.avg_dv 
from tb as a inner join 
(select code, date, avg(dv) 
over (partition by code order by date asc rows 1 preceding)) b 
    on a.code = b.code and a.date = b.date;

But that just gives me syntax error - Error: near ".": syntax error

In particular, the reference to "b" in the line set tb.avg_dv = b.avg_dv looks like garbage, but I don't know what to replace it with.

Can this be done in a single query?

forpas · Accepted Answer · 2020-12-15T08:21:30.317

The code that you tried to adapt in your case uses SQL Server syntax.
If your version of SQLite is 3.33.0+ the correct syntax is:

update tb 
set avg_dv = b.avg_dv 
from (
  select code, date, 
         avg(dv) over (partition by code order by date asc rows 1 preceding) avg_dv
  from tb
) b 
where tb.code = b.code and tb.date = b.date;

If you are using a previous version of SQLite that does not support the FROM clause in the UPDATE statement, then you can do it with a CTE:

with cte as (
  select code, date, 
         avg(dv) over (partition by code order by date asc rows 1 preceding) avg_dv
  from tb
)
update tb 
set avg_dv = (select c.avg_dv from cte c where c.code = tb.code and c.date = tb.date)

Also note that the update statement for the column dv can be written simply:

update tb set dv = cl * vo;

and if your version of SQLite is 3.31.0+, you could create it as generated column so that there is no need for updates:

alter table tb add column dv float generated always as(cl * vo);

sqlite3 update column with rollling average

1 Answers1