Таблица MySQL с отметками времени UNIX — сортировка по отметке времени и вычисление разницы между одной строкой и предыдущей

Вы бы просто использовали lag():

select t.*,
       (timestamp - lag(timestamp) over (order by timestamp)) as delta
from t;

В более старых версиях MySQL вы можете сделать это довольно болезненно, используя переменные:

select t.*,
       (timestamp -
        (case when (@tempt := @prevt) = null    -- never happens
              then 0
              when (@prev := timestamp) = null  -- never happens
              then 0
              else @tempt
         end)
        ) as delta
from (select t.* from t order by timestamp) t cross join
     (select @prevt := -1) params;

Обратите внимание, что здесь используется выражение case для реализации последовательной логики обработки переменной. Это уловка, и вы можете понять, почему MySQL переключился на использование стандартных оконных функций для этого типа операций.

Вы можете попробовать создать индекс для (timestamp), чтобы повысить производительность.

РЕДАКТИРОВАТЬ:

Если у вас есть индекс на timestamp и отметки времени уникальны, вы можете:

update t join
       (select t.*,
               (timestamp - lag(timestamp) over (order by timestamp)) as delta
        from t
       ) tt
       using (timestamp)
    set t.delta = tt.delta;

Спасибо, Гордон! Это выглядит действительно полезным, и я поиграю с этим дальше. Есть идеи, как я могу превратить этот запрос SELECT в запрос UPDATE, который заполнит добавленный мной столбец дельты? (Я бы хотел сделать еще много вещей с дельтой, когда она у меня появится.) — person azoundria; 12.06.2021

присоединиться к запросу с исходной таблицей и установить delta = t1 delta — person azoundria; 12.06.2021

Спасибо nbk. Я использовал соединение несколько раз, но было бы очень полезно увидеть заданный вами запрос. Я поиграю и прочитаю еще немного, чтобы понять, смогу ли я тоже в этом разобраться. — person azoundria; 12.06.2021

Большое спасибо за запрос. В настоящий момент я получаю сообщение «У вас ошибка в синтаксисе SQL»; проверьте руководство, соответствующее вашей версии сервера MySQL, чтобы найти правильный синтаксис для использования рядом с ‘OVER (ORDER BY timestamp)) AS delta. Я думаю, что скобки могут быть немного неправильными, но не уверен. Мне удалось добраться до У вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, чтобы найти правильный синтаксис для использования рядом с ‘(ORDER BY timestamp) AS delta FROM, перемещая скобки вокруг, но не смог пройти мимо этого. Я буду продолжать играть с этим. Еще раз спасибо! — person azoundria; 12.06.2021

Моя версия не поддерживает функцию LAG, так как она была добавлена ​​только в версии 8 MySQL. (Среда общего хостинга, которую я использую, все еще находится в MySQL 5.) Здесь есть ссылка с некоторыми временными решениями, на которые я смотрю: stackoverflow.com/questions/11303532/ — person azoundria; 12.06.2021