MariaDB：如何在WHERE子句中使用窗口函数LAG的计算结果

因为SQL的执行顺序中，WHERE子句会在窗口函数执行前运行，所以无法直接在WHERE里引用窗口函数生成的diff_in_minutes字段。解决这个问题的核心是先让窗口函数完成计算，再在外层进行过滤，常用两种方式：

方法一：使用子查询 #

把包含窗口函数的查询作为子查询，在外层SELECT中对计算结果进行过滤：

SELECT *
FROM (
    SELECT tracker_id,
           TIMESTAMP,
           LAG(TIMESTAMP) OVER(ORDER BY TIMESTAMP DESC) AS prev_timestamp,
           TIMESTAMPDIFF(MINUTE, TIMESTAMP, LAG(TIMESTAMP) OVER(ORDER BY TIMESTAMP DESC)) AS diff_in_minutes
    FROM comm_telemetry
    WHERE tracker_id = "123456789"
) AS subquery
WHERE diff_in_minutes > 0
ORDER BY TIMESTAMP DESC;

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

如果你的数据库支持CTE（比如MySQL 8.0+、PostgreSQL、SQL Server等），可以用更清晰的CTE写法：

WITH telemetry_diff AS (
    SELECT tracker_id,
           TIMESTAMP,
           LAG(TIMESTAMP) OVER(ORDER BY TIMESTAMP DESC) AS prev_timestamp,
           TIMESTAMPDIFF(MINUTE, TIMESTAMP, LAG(TIMESTAMP) OVER(ORDER BY TIMESTAMP DESC)) AS diff_in_minutes
    FROM comm_telemetry
    WHERE tracker_id = "123456789"
)
SELECT *
FROM telemetry_diff
WHERE diff_in_minutes > 0
ORDER BY TIMESTAMP DESC;

注意：数据集中的第一行（最新的时间戳）因为没有前一个时间戳，diff_in_minutes会是NULL，过滤条件diff_in_minutes > 0会自动排除这行数据，正好符合需求。

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