마리아답:WHERE 절에서 창 함수 LAG 결과 사용

마리아답:WHERE 절에서 창 함수 LAG 결과 사용

두 타임스탬프의 차이를 알아보기 위해 다음 쿼리를 사용하고 있습니다.

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;

diff_in_minutes >0 의 경우에만 표시되도록 결과를 필터링 합니다.문제는 WHERE 절에서 Windows 함수가 허용되지 않는다는 것입니다.

어떻게 이걸 해결할 수 있을까요?

먼저 하위 쿼리에서 지연을 계산한 후 다시 쿼리하여 필터링에 사용해야 합니다.

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;

그 사이에 해결 방법을 찾았습니다.

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;

언급URL : https://stackoverflow.com/questions/73579806/mariadb-use-result-of-window-function-lag-in-where-clause