Mariadb：在WHERE子句中使用窗口函数LAG的结果

0wi1tuuw 于 2022-11-23 发布在其他

关注(0)|答案(2)|浏览(240)

我使用以下查询来获取两个时间戳之间的差异：

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子句中不允许使用窗口函数。
有什么建议怎么这么解决这个？

mariadb

来源：https://stackoverflow.com/questions/73579806/mariadb-use-result-of-window-function-lag-in-where-clause

2条答案

按热度按时间

hfsqlsce1#

您将需要首先计算子查询中的滞后时间，然后再次查询该滞后时间以使用它进行筛选。

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;

赞(0）回复(0）举报 2022-11-23

hjzp0vay2#

同时找到了一个解决方案：

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;

赞(0）回复(0）举报 2022-11-23

我来回答

Mariadb：在WHERE子句中使用窗口函数LAG的结果

2条答案

相关问题

热门标签

最新问答