SQLite 中的移动平均线

我想计算 SQLite 表中数据的移动平均值。我在MySQL中找到了几种方法，但在SQLite中找不到有效的方法。

在 SQL 中，我认为类似的事情应该可以做到（但是，我无法尝试......）：

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

但是，我看到两个缺点：

• 这似乎不适用于 sqlite
• 如果前/后行上的几个日期的数据不连续，它会计算窗口上的移动平均值，该窗口比我实际想要的更宽，因为它仅基于周围行的数量。因此，应添加日期条件

事实上，我希望它计算每个日期的“价值”平均值，超过 +/-3 天（每周移动平均值）或 +/-15 天（每月移动平均值）

这是一个示例数据集：

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);

3.25.0版本（2018-09-15）添加了窗口函数。使用版本 3.28.0 (2019-04-16) 中添加的 RANGE 帧类型，您现在可以执行以下操作：

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;