Путаница при использовании оконных функций LEAD / LAG

На этой таблице:

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) в оконной функции ничего не меняет.


postgresql sql lag window-functions lead
person super_ask    schedule 23.07.2020    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Это вычитание отметки времени из следующей строки из отметки времени из предыдущей строки. Вы можете разделить на 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
person Gordon Linoff    schedule 23.07.2020
comment
Ах хорошо. Теперь я понимаю логику. - person super_ask; 23.07.2020