Moving average in SQLite

Question

I would like to compute a moving average over data in a SQLite table. I found several method in MySQL, but couldn't find an efficient one in SQLite.

In SQL, I think something like this should do it (however, I was not able to try it...) :

SELECT date, value, 
avg(value) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) as MovingAverageWindow7
FROM t ORDER BY date;

However, I see two drawbacks :

• This does not seems to work on sqlite
• If data are not continuous for few dates on preceding/following rows, it computes a moving average on a window which is wider than what I actually want since it is only based on the number of surrounding rows. Thus, a date condition should be added

Indeed, I would like it to compute the average of 'value' at each date, over +/-3 days (weekly moving average) or +/-15 days (monthly moving average)

Here is an example data set :

CREATE TABLE t ( date DATE, value INTEGER );

INSERT INTO t (date, value) VALUES ('2018-02-01', 8);
INSERT INTO t (date, value) VALUES ('2018-02-02', 2);
INSERT INTO t (date, value) VALUES ('2018-02-05', 5);
INSERT INTO t (date, value) VALUES ('2018-02-06', 4);
INSERT INTO t (date, value) VALUES ('2018-02-07', 1);
INSERT INTO t (date, value) VALUES ('2018-02-10', 6);
INSERT INTO t (date, value) VALUES ('2018-02-11', 0);
INSERT INTO t (date, value) VALUES ('2018-02-12', 2);
INSERT INTO t (date, value) VALUES ('2018-02-13', 1);
INSERT INTO t (date, value) VALUES ('2018-02-14', 3);
INSERT INTO t (date, value) VALUES ('2018-02-15', 11);
INSERT INTO t (date, value) VALUES ('2018-02-18', 4);
INSERT INTO t (date, value) VALUES ('2018-02-20', 1);
INSERT INTO t (date, value) VALUES ('2018-02-21', 5);
INSERT INTO t (date, value) VALUES ('2018-02-28', 10);
INSERT INTO t (date, value) VALUES ('2018-03-02', 6);
INSERT INTO t (date, value) VALUES ('2018-03-03', 7);
INSERT INTO t (date, value) VALUES ('2018-03-04', 3);
INSERT INTO t (date, value) VALUES ('2018-03-08', 5);
INSERT INTO t (date, value) VALUES ('2018-03-09', 6);
INSERT INTO t (date, value) VALUES ('2018-03-15', 1);
INSERT INTO t (date, value) VALUES ('2018-03-16', 3);
INSERT INTO t (date, value) VALUES ('2018-03-25', 5);
INSERT INTO t (date, value) VALUES ('2018-03-31', 1);

Answer 1

Window functions were added in version 3.25.0 (2018-09-15). With the RANGE frame type added in version 3.28.0 (2019-04-16), you can now do:

SELECT date, value, 
avg(value) OVER (
    ORDER BY CAST (strftime('%s', date) AS INT)
    RANGE BETWEEN 3 * 24 * 60 * 60 PRECEDING
        AND 3 * 24 * 60 * 60 FOLLOWING
) AS MovingAverageWindow7
FROM t ORDER BY date;