我想在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上似乎不起作用
- 如果前/后几行的数据不连续,则会计算一个移动平均值,其窗口比我实际想要的更宽,因为它仅基于周围行数。 因此,应添加日期条件。
事实上,我希望它能在每个日期上计算'value'的平均值,取+/-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);
... OVER (...)
)可能无法正常工作,因为它们是在SQLite 3.25中引入的。我的版本(随Ubuntu 18.04一起提供)是3.22,所以对我也不起作用。 :-( - PerlDuck