Confusion over using LEAD/LAG window functions

Question

On this 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)

When I tried getting the difference using LEAD/LAG window function on timestamp column, the result is twice as I expect:

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)

Note: using OVER (PARTITION BY id ORDER BY timestamp) in the window function does change anything.

score 1 · Answer 1 · answered Jul 23 '20 at 16:37

This is subtracting the timestamp from the next row from the timestamp from the previous row. You can eivide by 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;

Or use the timestamp from the current row:

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

Confusion over using LEAD/LAG window functions

1 Answers1