SQLite – Rolling Average/Sum

I have a dataset as shown below, wondering how I can do a rolling average with its current record followed by next two records. Example: lets consider the first record whose total is 3 followed by 4 and 7 ,Now the rolling 3 day average for first record would be 4.6 and so on.

Date  Total
1      3
2      4
3      7
4      1
5      2
6      4

Expected output:

Date  Total 3day_rolling_Avg
1      3       4.6
2      4       4
3      7       3.3
4      1       2.3
5      2       null
6      4       null

PS: Having “null” value isn’t important. This is just a sample data where I need to look at more than 3 days(Ex: 30 days rolling)

Answer

I think that the simplest approach is a window avg(), with the poper window frame:

select 
    t.*,
    avg(total) 
        over(order by date rows between current row and 2 following) as "3d_rolling_avg"
from mytable t

If you want to return a null value when there is less than 2 leading rows, as show in your expected results, then you can use row_number() on top of it:

select 
    t.*,
    case when rank() over(order by date desc) <= 2
    then avg(total) 
             over(order by date rows between current row and 2 following)
    end as "3d_rolling_avg"
from mytable t