SQLite中的移动平均值

Question

我想在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);

- Maxime

---

这些“窗口函数”(... OVER (...))可能无法正常工作，因为它们是在SQLite 3.25中引入的。我的版本（随Ubuntu 18.04一起提供）是3.22，所以对我也不起作用。 :-( - PerlDuck

Answer 1

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

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;

- Forty-Bot

Answer 2

我认为我实际上找到了一个解决方案：

SELECT date, value, 
  (SELECT AVG(value) FROM t t2 
   WHERE datetime(t1.date, '-3 days') <= datetime(t2.date) AND datetime(t1.date, '+3 days') >= datetime(t2.date)
   ) AS MAVG
FROM t t1
GROUP BY strftime('%Y-%m-%d', date); 

我不确定这是否是最有效的方法，但它似乎可以工作。

编辑： 对于包含20,000行的真实数据库，两个参数的每周移动平均大约需要1分钟来计算。

我看到有两个选择：

• 使用SQLite可以更有效地计算此项
• 从SQLite中提取数据后，在Python中计算移动平均值

- Maxime

Answer 3

一种方法是创建一个中间表，将每个日期映射到它所属的组。最初的回答为：

创建一个中间表，将每个日期映射到它所属的组。

CREATE TABLE groups (date DATE, daygroup DATE);
INSERT INTO groups 
  SELECT date, strftime('%Y-%m-%d', datetime(date, '-1 days')) AS daygroup
  FROM t;  
INSERT INTO groups 
  SELECT date, strftime('%Y-%m-%d', datetime(date, '-2 days')) AS daygroup
  FROM t;  
INSERT INTO groups 
  SELECT date, strftime('%Y-%m-%d', datetime(date, '-3 days')) AS daygroup
  FROM t;  
INSERT INTO groups 
  SELECT date, strftime('%Y-%m-%d', datetime(date, '+1 days')) AS daygroup
  FROM t;  
INSERT INTO groups 
  SELECT date, strftime('%Y-%m-%d', datetime(date, '+2 days')) AS daygroup
  FROM t;  
INSERT INTO groups 
  SELECT date, strftime('%Y-%m-%d', datetime(date, '+3 days')) AS daygroup
  FROM t;  
INSERT INTO groups 
  SELECT date, date AS daygroup FROM t;

You get for example,

SELECT * FROM groups WHERE date = '2018-02-05'

    date        daygroup
    2018-02-05  2018-02-04
    2018-02-05  2018-02-03
    2018-02-05  2018-02-02
    2018-02-05  2018-02-06
    2018-02-05  2018-02-07
    2018-02-05  2018-02-08
    2018-02-05  2018-02-05

将'2018-02-05'归入组别'2018-02-02'至'2018-02-08'，表示该日期属于该组。如果一个日期属于某个组，那么该日期的值将加入到该组的移动平均计算中。

有了这个方法，移动平均计算变得简单明了：

SELECT
  d.date, d.value, c.ma
FROM
  t AS d
INNER JOIN 
  (SELECT 
    b.daygroup,
    avg(a.value) AS ma
  FROM 
    t AS a 
  INNER JOIN
    groups AS b
  ON a.date = b.date
  GROUP BY b.daygroup) AS c
ON
  d.date = c.daygroup

请注意，中间表的行数是原始表的7倍，随着窗口的扩大而成比例增长。除非您有更大的表格，否则这应该是可以接受的。

我还尝试了20000行数据。在我的笔记本电脑上，插入查询花费了1.5秒，选择查询花费了0.5秒。

另外，以下查询是一个不需要中间表的替代方案。该查询将表格与自身合并，以允许3天的滞后，然后取平均值。

（注：ADDED, perhaps better.为标题，无需翻译）

SELECT
  t1.date, avg(t2.value) AS MVG
FROM 
  t AS t1
INNER JOIN
  t AS t2
ON
  datetime(t1.date, '-3 days') <= datetime(t2.date) 
  AND 
  datetime(t1.date, '+3 days') >= datetime(t2.date)
GROUP BY
  t1.date
;

- Kota Mori