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

xbp102n0  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(414)

在这张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) 在窗口中,函数不会改变任何东西。

zbdgwd5y

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

相关问题