在这张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)
在窗口中,函数不会改变任何东西。
1条答案
按热度按时间zbdgwd5y1#
这是从上一行的时间戳中减去下一行的时间戳。你可以通过2:
或者使用当前行的时间戳: