Mariadb: Use result of window function LAG in WHERE clause-CodePudding

I am using the following query to get the difference between two timestamps:

SELECT tracker_id,
       TIMESTAMP,
       LAG(TIMESTAMP) OVER(ORDER BY TIMESTAMP DESC),
       TIMESTAMPDIFF(MINUTE,
                     TIMESTAMP,
                     LAG(TIMESTAMP) OVER(ORDER BY TIMESTAMP DESC)) AS diff_in_minutes
  FROM comm_telemetry
 WHERE comm_telemetry.tracker_id = "123456789"
 ORDER BY comm_telemetry.timestamp DESC;

I want to filter the result to only show when diff_in_minutes > 0. The problem is, that windows functions are not allowed in WHERE clauses.

Any suggestion how so solve this?

CodePudding user response：

You will need to first compute the lag in a subquery and then query that again to use it to filter.

WITH cte AS (
    SELECT tracker_id,
           TIMESTAMP,
           TIMESTAMPDIFF(MINUTE,
                         TIMESTAMP,
                         LAG(TIMESTAMP) OVER (ORDER BY TIMESTAMP DESC)) AS diff_in_minutes 
    FROM comm_telemetry 
    WHERE tracker_id = '123456789'
)

SELECT tracker_id, TIMESTAMP, diff_in_minutes
FROM cte
WHERE diff_in_minutes > 0
ORDER BY TIMESTAMP DESC;

CodePudding user response：

found a solution meanwhile:

WITH tbl_diff_in_minutes AS (SELECT
tracker_id,
`timestamp` as ts,
LAG( `timestamp` ) OVER ( ORDER BY `timestamp` DESC ) prev_ts,
TIMESTAMPDIFF(
    MINUTE,
    `timestamp`,
LAG( `timestamp` ) OVER ( ORDER BY `timestamp` DESC )) AS        diff_in_minutes 
FROM
comm_telemetry 
WHERE
comm_telemetry.tracker_id = "123456789" 
ORDER BY
comm_telemetry.`timestamp` DESC) 

SELECT tracker_id, ts, prev_ts, diff_in_minutes FROM tbl_diff_in_minutes WHERE diff_in_minutes > 0;