对使用超前/滞后窗口函数的混淆

xbp102n0 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(314)

在这张table上：

SELECT * FROM mytable WHERE id=53388829 LIMIT 10;
 id    |    lat     |    lon     | timestamp  | travelmode 
----------+------------+------------+------------+------------
 53388829 | 41.2371292 | -8.6711092 | 1459516801 |          0
 53388829 | 41.2371828 | -8.6710917 | 1459516806 |          0
 53388829 | 41.2371898 | -8.6710868 | 1459516811 |          0
 53388829 | 41.2370866 | -8.6711695 | 1459516816 |          0
 53388829 | 41.2370858 | -8.6711626 | 1459516821 |          0
 53388829 | 41.2370617 | -8.6711633 | 1459516826 |          0
 53388829 | 41.2371638 | -8.6709698 | 1459516831 |          0
 53388829 | 41.2371453 | -8.6711502 | 1459516836 |          0
 53388829 | 41.2370934 | -8.6711191 | 1459516841 |          0
 53388829 | 41.2370559 | -8.6711692 | 1459516846 |          0
(10 rows)

当我试着用 LEAD/LAG 窗口功能打开 timestamp 列，结果是我预期的两倍：

SELECT *, 
 LEAD(timestamp) OVER (ORDER BY timestamp) - (LAG(timestamp) OVER (ORDER BY timestamp)) as diff
FROM mytable s where id=53388829 LIMIT 10
;
    id    |    lat     |    lon     | timestamp  | travelmode | diff 
----------+------------+------------+------------+------------+------
 53388829 | 41.2371292 | -8.6711092 | 1459516801 |          0 |     
 53388829 | 41.2371828 | -8.6710917 | 1459516806 |          0 |   10
 53388829 | 41.2371898 | -8.6710868 | 1459516811 |          0 |   10
 53388829 | 41.2370866 | -8.6711695 | 1459516816 |          0 |   10
 53388829 | 41.2370858 | -8.6711626 | 1459516821 |          0 |   10
 53388829 | 41.2370617 | -8.6711633 | 1459516826 |          0 |   10
 53388829 | 41.2371638 | -8.6709698 | 1459516831 |          0 |   10
 53388829 | 41.2371453 | -8.6711502 | 1459516836 |          0 |   10
 53388829 | 41.2370934 | -8.6711191 | 1459516841 |          0 |   10
 53388829 | 41.2370559 | -8.6711692 | 1459516846 |          0 |   10
(10 rows)

注：使用 OVER (PARTITION BY id ORDER BY timestamp) 在窗口中，函数不会改变任何东西。

sql postgresql window-functions lag lead

来源：https://stackoverflow.com/questions/63059039/confusion-over-using-lead-lag-window-functions

1条答案

按热度按时间

zbdgwd5y1#

这是从上一行的时间戳中减去下一行的时间戳。你可以通过2:

SELECT s.*, 
       (LEAD(timestamp) OVER (ORDER BY timestamp) - (LAG(timestamp) OVER (ORDER BY timestamp)) / 2 as diff
FROM mytable s 
WHERE id = 53388829
LIMIT 10;

或者使用当前行的时间戳：

SELECT s.*, 
       (LEAD(timestamp) OVER (ORDER BY timestamp) - timestamp) as diff
FROM mytable s 
WHERE id = 53388829
LIMIT 10

赞(0）回复(0）举报 2021-07-24

我来回答

对使用超前/滞后窗口函数的混淆

1条答案

相关问题

热门标签

最新问答