在SQLite中使用LAG窗口函数时出现语法错误的求助

错误原因 #

你遇到的OperationalError: near "(": syntax error，大概率是因为使用的数据库版本不支持窗口函数（比如SQLite 3.25之前的版本没有窗口函数支持），或者LAG函数的调用格式不符合该数据库的语法规范。另外，就算窗口函数可用，单纯用LAG也无法处理连续的NULL值（比如2011-11-14和15的连续NULL，LAG只能取上一行的NULL，无法拿到更早的非空值）。

解决方案 #

根据不同数据库支持情况，提供以下几种可行方案：

方案1：使用累计窗口函数（支持窗口函数的数据库：MySQL 8.0+、PostgreSQL、SQLite 3.25+等） #

利用MAX()窗口函数自动忽略NULL的特性，按日期排序后，累计到当前行的最大非空值就是最近的前一行非空值，完美解决连续NULL填充：

SELECT dt,
       MAX(num) OVER (ORDER BY dt) AS filled_num
FROM test;

方案2：使用LAST_VALUE（支持IGNORE NULLS的数据库：PostgreSQL、SQLite 3.30+等） #

显式指定忽略NULL，取窗口内到当前行的最后一个非空值：

SELECT dt,
       LAST_VALUE(num IGNORE NULLS) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_num
FROM test;

方案3：递归CTE（兼容多数数据库，包括不支持窗口函数的版本） #

通过递归方式逐行填充，适合旧版数据库：

WITH RECURSIVE filled_test AS (
    -- 取最早的一行作为起始
    SELECT dt, num
    FROM test
    WHERE dt = (SELECT MIN(dt) FROM test)
    UNION ALL
    -- 逐行关联，用前一行已填充的值替换当前行的NULL
    SELECT t.dt, COALESCE(t.num, ft.num)
    FROM test t
    JOIN filled_test ft ON t.dt = (SELECT MIN(dt) FROM test WHERE dt > ft.dt)
)
SELECT * FROM filled_test ORDER BY dt;

方案4：子查询（兼容极低版本数据库，如旧版SQLite） #

通过子查询找到当前行之前最近的非空值：

SELECT 
    dt,
    (SELECT num FROM test t2 WHERE t2.dt <= t1.dt AND t2.num IS NOT NULL ORDER BY t2.dt DESC LIMIT 1) AS filled_num
FROM test t1;

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