SQLite中基于日期范围的移动平均实现方案咨询

刚好我之前也碰到过一模一样的问题：MySQL里的方法套到SQLite上不管用，而且基于行数的窗口函数在日期不连续时完全不符合需求。下面给你两个实用的方案，分别适配不同版本的SQLite，都是基于日期范围（而非行数）来计算移动平均的。

先准备测试数据 #

首先咱们把你给的示例数据集放出来，方便测试：

CREATE TABLE t ( date DATE, value INTEGER );
INSERT INTO t (date, value) VALUES 
('2018-02-01', 8),
('2018-02-02', 2),
('2018-02-05', 5),
('2018-02-06', 4),
('2018-02-07', 1),
('2018-02-10', 6),
('2018-02-11', 0),
('2018-02-12', 2),
('2018-02-13', 1),
('2018-02-14', 3),
('2018-02-15', 11),
('2018-02-18', 4),
('2018-02-20', 1),
('2018-02-21', 5),
('2018-02-28', 10),
('2018-03-02', 6),
('2018-03-03', 7),
('2018-03-04', 3),
('2018-03-08', 5),
('2018-03-09', 6),
('2018-03-15', 1),
('2018-03-16', 3),
('2018-03-25', 5),
('2018-03-31', 1);

方案一：关联子查询（兼容所有SQLite版本） #

如果你的SQLite版本比较旧（低于3.25.0，不支持窗口函数），或者需要最大兼容性，用这个方法最稳妥。它的思路是对每一行，查询表中日期在当前日期±N天范围内的所有记录，计算它们的平均值。

周度移动平均（±3天） #

SELECT
    main.date,
    main.value,
    (SELECT AVG(sub.value)
     FROM t sub
     WHERE sub.date BETWEEN DATE(main.date, '-3 days') AND DATE(main.date, '+3 days')) AS MovingAverageWindow7
FROM t main
ORDER BY main.date;

月度移动平均（±15天） #

只需要把日期偏移量改成15天就行：

SELECT
    main.date,
    main.value,
    (SELECT AVG(sub.value)
     FROM t sub
     WHERE sub.date BETWEEN DATE(main.date, '-15 days') AND DATE(main.date, '+15 days')) AS MovingAverageWindow31
FROM t main
ORDER BY main.date;

优缺点：

• 优点：兼容性拉满，任何SQLite版本都能跑；逻辑简单易懂。
• 缺点：如果表的数据量很大，性能会稍差——因为每一行都要执行一次子查询，时间复杂度是O(n²)。

方案二：窗口函数（SQLite 3.25.0及以上） #

SQLite从3.25.0版本开始支持窗口函数，但直接用日期类型没法用RANGE范围（SQLite的RANGE只支持数值类型）。不过我们可以把日期转换成julianday数值（julianday是连续的数值，每增加1代表一天），这样就能用RANGE来指定日期范围了。

周度移动平均（±3天） #

SELECT
    date,
    value,
    AVG(value) OVER (
        ORDER BY julianday(date)
        RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) AS MovingAverageWindow7
FROM t
ORDER BY date;

月度移动平均（±15天） #

同样，把范围改成15即可：

SELECT
    date,
    value,
    AVG(value) OVER (
        ORDER BY julianday(date)
        RANGE BETWEEN 15 PRECEDING AND 15 FOLLOWING
    ) AS MovingAverageWindow31
FROM t
ORDER BY date;

优缺点：

• 优点：性能极佳，大数据量下比子查询快很多，窗口函数只需要扫描表一次，时间复杂度是O(n log n)。
• 缺点：需要SQLite版本≥3.25.0（2018年9月发布的版本，现在大部分环境都满足，但如果是老系统要注意）。

为什么你原来的语句不行？ #

你之前写的基于ROWS的窗口函数有两个问题：

1. 版本兼容性：如果你的SQLite版本低于3.25.0，根本不支持窗口函数，自然运行失败；
2. 逻辑不符合需求：ROWS BETWEEN是基于行数的，不管日期是否连续。比如2018-02-02之后隔了两天才到2018-02-05，用ROWS 3 PRECEDING会包含前面3行（甚至可能是一周前的数据），完全偏离了你要的“±3天”范围。

内容的提问来源于stack exchange，提问作者Maxime