sqlite中的滚动平均值

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

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

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

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