SQLite中如何对窗口函数结果使用WHERE子句过滤？

首先要修正你原查询的一个关键问题：如果是多个设备的状态变更分析，必须加上PARTITION BY lightID，否则会把不同设备的状态混在一起比较，得到错误的结果。

针对无法用WHERE直接过滤窗口函数别名的问题，有两种实用解决方法：

方法一：使用子查询 #

把窗口函数的计算逻辑放在子查询中，外层查询再对已计算好的结果进行过滤：

SELECT date, lightID, statusChange
FROM (
    SELECT 
        date,
        lightID,
        (status - LAG(status, 1) OVER (PARTITION BY lightID ORDER BY date)) as statusChange
    FROM devices
) AS temp
-- 过滤掉状态未变更的记录，同时保留每个设备的初始状态记录（statusChange为NULL）
WHERE statusChange != 0 OR statusChange IS NOT NULL

方法二：使用CTE（公共表表达式） #

如果你的SQLite版本在3.33.0及以上，支持CTE的写法会更清晰易读：

WITH device_status_changes AS (
    SELECT 
        date,
        lightID,
        (status - LAG(status, 1) OVER (PARTITION BY lightID ORDER BY date)) as statusChange
    FROM devices
)
SELECT date, lightID, statusChange
FROM device_status_changes
WHERE statusChange != 0 OR statusChange IS NOT NULL

报错原因说明 #

SQL的执行顺序是FROM → WHERE → SELECT，当执行WHERE子句时，SELECT里定义的statusChange别名还未被计算生成，所以直接引用会触发报错。通过子查询或CTE提前完成窗口函数的计算，外层查询就能正常对结果进行过滤了。

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