sqlite中的滚动平均值

在 SQLite 中计算滚动平均值（也称为移动平均值）可以通过窗口函数来实现。窗口函数在 SQLite 3.25.0 及更高版本中可用。以下是一个详细的示例，展示如何在 SQLite 中计算滚动平均值。

示例场景

假设你有一个包含日期和数值的表 data，你希望计算一个 3 天的滚动平均值。

创建示例表和数据

首先，创建一个示例表并插入一些数据：

CREATE TABLE data (
    date TEXT,
    value REAL
);

INSERT INTO data (date, value) VALUES
('2023-10-01', 10),
('2023-10-02', 20),
('2023-10-03', 30),
('2023-10-04', 40),
('2023-10-05', 50),
('2023-10-06', 60),
('2023-10-07', 70);

计算滚动平均值

使用窗口函数 AVG 和 OVER 子句来计算 3 天的滚动平均值：

SELECT
    date,
    value,
    AVG(value) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_avg
FROM
    data;

解释

AVG(value) OVER (...) 是一个窗口函数，用于计算平均值。
ORDER BY date 指定了窗口的排序顺序。
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 指定了窗口的范围，即当前行及其前两行。

结果

执行上述查询后，你将得到以下结果：

date        value       rolling_avg
----------  ----------  ------------
2023-10-01  10          10.0
2023-10-02  20          15.0
2023-10-03  30          20.0
2023-10-04  40          30.0
2023-10-05  50          40.0
2023-10-06  60          50.0
2023-10-07  70          60.0

解释结果

对于 2023-10-01，只有一行数据，所以滚动平均值是 10。
对于 2023-10-02，有两行数据（2023-10-01 和 2023-10-02），所以滚动平均值是 (10 + 20) / 2 = 15。
对于 2023-10-03，有三行数据（2023-10-01，2023-10-02 和 2023-10-03），所以滚动平均值是 (10 + 20 + 30) / 3 = 20。
对于 2023-10-04 及之后的行，滚动平均值是当前行及其前两行的平均值。

更改滚动窗口大小

如果你希望计算不同大小的滚动平均值，只需调整 ROWS BETWEEN ... 子句。例如，计算 5 天的滚动平均值：

SELECT
    date,
    value,
    AVG(value) OVER (
        ORDER BY date
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS rolling_avg
FROM
    data;

通过这种方式，你可以在 SQLite 中计算不同大小的滚动平均值。根据需要调整窗口函数的参数，以适应你的具体需求。