supose we have the next table: table example
and what i need is:
frst iteration: calculate the moving average 5 days before the last day including the last day = (2+1+2+3+4)/5 = 2.4 and "save" this result, that result will be a prediction for the next day.
scnd iteration: calculate the moving average 5 days before the last, day where the last day basal cell is the value calculated in the previous iteration. (1+2+3+4+2.4)/5 = 2.48
..
and so on.. the recursion will stop for a concrete future day for example: 2022-12-9
deseable output for future day: 2022-12-9
| date_ | art_id | basal_sell |
| ------------| -----------|------------|
| 2022-12-01 | 1 | 2 |
| 2022-12-02 | 1 | 1 |
| 2022-12-03 | 1 | 2 |
| 2022-12-04 | 1 | 3 |
| 2022-12-05 | 1 | 4 |
| 2022-12-06 | 1 | 2.4 |
| 2022-12-07 | 1 | 2.48 |
| 2022-12-08 | 1 | 2.776 |
| 2022-12-09 | 1 | 2.9312 |
this is the partial problem, in the real problem will be a bunch of arts_ids but i think the idea for this parcial problem will be the solution for the big problem (with some little changes).
- what i think:
I thought a recursive cte where in the recursive part of the cte i have a union that will be union the temporary table with the new row that i calculated.
Something like:
with MiCte as (
select *
from sells
union all
(
select * from MiCte
)
union
(
select dateadd(day, 1, date_), art_id, basal_sell
from(
select top 1 c.date_, c.art_id,
AVG(c.basal_sell) OVER (partition by c.art_id
ORDER BY c.date_
rows BETWEEN 4 PRECEDING AND current row) basal_sell
from MiCte c
order by c.date_ desc
) as tmp
)
) select * from MiCte
Obviously if I contemplate having more than one art_id I have to take this into account when making top 1 (which I still couldn't think of how to solve).
- the example table:
CREATE TABLE sells
(date_ DATETIME,
art_id int,
basal_sell int)
;
INSERT INTO sells
(date_, art_id , basal_sell)
VALUES ('2022-12-1', 1, 2),
('2022-12-2', 1, 1),
('2022-12-3', 1, 2),
('2022-12-4', 1, 3),
('2022-12-5', 1, 4);
