SQLite查询本地运行正常但Codility测试编辑器报错如何解决

报错原因 #

SQLite 3.15.0版本才新增多列组合的行值IN查询语法，你线上Codility环境用的3.11.0版本不支持WHERE (c2.event_type, c2.time) IN (子查询)这类写法，这是触发括号附近语法报错的核心原因。

修复方案 #

把行值IN的过滤逻辑替换为兼容低版本SQLite的关联匹配写法即可，修改后的完整代码如下：

WITH cte1 AS
(
    SELECT *, CASE WHEN e2.event_type = e2.prev THEN 0
                 WHEN e2.event_type = e2.next THEN 0
                 ELSE 1 END AS grp
    FROM (SELECT *, LAG(e1.event_type) OVER(ORDER BY time) AS prev , LEAD(e1.event_type) OVER(ORDER BY time) AS next FROM events e1) e2
)
,cte2 AS
(
    SELECT cte1.event_type, cte1.time, cte1.grp, cte1.value - LAG(cte1.value) OVER(ORDER BY cte1.event_type, cte1.time) AS value
    FROM cte1
    WHERE cte1.grp = 0
    ORDER BY cte1.event_type, cte1.time
)
,cte3 AS (
    SELECT event_type, MAX(time) AS max_time
    FROM cte2
    GROUP BY event_type
)
SELECT c2.event_type, c2.value
FROM cte2 c2
INNER JOIN cte3 c3 ON c2.event_type = c3.event_type AND c2.time = c3.max_time
GROUP BY c2.event_type
ORDER BY c2.event_type, c2.time

补充说明：上述代码同时把原窗口函数里的ORDER BY (SELECT 1)替换成了ORDER BY time，部分低版本SQLite对窗口排序的常量表达式支持不完善，用实际的时间字段排序兼容性更好，也更符合业务逻辑。修改后逻辑和你原代码完全一致，可在SQLite 3.11.0版本正常运行。

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