SQLite - 计算移动平均值

Question

我有一个在pysqlite中使用的SQLite表：

create table t
(
    id integer primary key not null,
    time datetime not null,
    price decimal(5,2)
)

我该如何使用SQL语句从这些数据中计算出窗口大小为X秒的移动平均值?

- microo8

Answer 1

据我理解，您想要的不是过去N个项目的平均值，而是过去x秒钟内的平均值，是这样吗？

好的，这将为您提供记录在过去720秒内的所有价格清单：

>>> cur.execute("SELECT price FROM t WHERE datetime(time) > datetime('now','-720 seconds')").fetchall()

当然，你可以将其提供给AVG-SQL函数，以获得该窗口中的平均价格。

>>> cur.execute("SELECT AVG(price) FROM t WHERE datetime(time) > datetime('now','-720 seconds')").fetchall()

您还可以使用其他时间单位，甚至将它们链接起来使用。例如，要获取过去一个半小时的平均价格，请执行以下操作：

>>> cur.execute("SELECT AVG(price) FROM t WHERE datetime(time) > datetime('now','-30 minutes','-1 hour')").fetchall()

编辑：SQLite日期时间参考可以在这里找到。

- ch3ka

Answer 2

移动平均线是指在时间i时，窗口大小为x的平均值：

(x[i] + x[i+1] + ... + x[i+x-1]) / x

为了计算它，您需要创建一个大小为x的LIFO堆栈（可以将其实现为队列），并计算其总和。然后，您可以通过添加新值并从旧总和中减去旧值来更新总和；您可以从数据库获取新值，并通过弹出堆栈的第一个元素来获取旧值。

这样讲清楚了吗？

- Katriel

---

嗯，好的，但我想要一个能够实现这个功能的SQL查询语句。 - microo8

@microo8 据我所知，目前没有这样的东西。以上内容相当简单明了，不是吗？ - Katriel

如果你非常想做的话，可能可以在SQL语句中完成，但这并不是SQL设计的目的。请先检索数据，然后在你喜欢的编程语言中执行数学运算。 - Li-aung Yip

是的，我认为你可以把一些东西拼凑在一起。高效的方法只需要通过数据进行一次操作，所以你需要在 SQL 中编写它，这很愚蠢。只需读取数据并在那里完成即可。 - Katriel

你可以像ch3ka建议的那样计算单个窗口的平均值。但是，正如katrielalex和Li-aung Yip所说，对整个表格进行滑动平均计算对于单个SQL语句来说过于复杂。我建议使用队列来实现。 - Igor F.