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

Похожие вопросы

Во-первых, я почти уверен, что где-то нужно ответить на что-то подобное, но я не смог его найти. Вот несколько похожих страниц, которые я не ищу:

MySQL — запрос UPDATE на основе запроса SELECT Это вычисляет разницу во времени между две строки, идентификаторы которых уже известны для обеих строк, и в моем случае я не знаю, каким будет идентификатор предыдущей строки. Кроме того, я использую DATETIME, когда моя таблица представляет собой простую временную метку Unix.

Вычислить разницу во времени между двумя отметками времени в mysql Кажется зависеть от функций, относящихся к определенному типу TIMESTAMP.

Разница между текущей и предыдущей отметкой времени Кажется, спрашивает правильную вещь, но его таблица не имеет реалистичных временных меток, и я не могу получить ни один из ответов на работу.

MySQL: как узнать разницу между двумя отметками времени в секундах Это не связано. Мое время уже указано во временных метках, поэтому я могу просто вычесть его.

Вычислить дельту (разность текущей и предыдущей строк) Группа mysql по определенному столбцу Это довольно близко, но это запрос SELECT, и я пытаюсь ОБНОВИТЬ таблицу, чтобы эта информация была в новом столбце, чтобы я мог использовать ее в последующих запросах. Используемый DATEDIFF, вероятно, можно легко преобразовать в простое вычитание. Я действительно не хочу создавать новую таблицу со всеми возможными значениями разницы в секундах.

Что я пытаюсь сделать

В Excel я мог брать строки, сортировать их по столбцу «метка времени», а затем устанавливать новый столбец (назовем его «дельта»), который равен одной метке времени минус предыдущая метка времени. Итак, я получу значение в секундах, которое представляет собой время, прошедшее между одной временной меткой и предыдущей. Если строка была, например, на 1 секунду позже предыдущей, значение было бы «1», или если бы она была на минуту позже, то было бы «60». Все метки времени являются метками времени Unix, поэтому с 1 января 1970 года прошло всего несколько секунд.

См. также:  почему номер телефона не хранится в целочисленном типе данных?

Добавить новый столбец в MySQL легко, но я не могу найти правильный запрос для его заполнения.

Вот пример таблицы с заполненным дельта-столбцом:

id timestamp delta
3 1623400800 NULL
2 1623444000 43200
56 1623444060 60

Ограничения решения

В идеале, поскольку строк много, я бы хотел что-то, что работало бы аналогично тому, что я делал бы в Excel, для повышения эффективности. То есть сортировка таблицы и заполнение дельты на основе данных отсортированной таблицы.

Конечно, если это невозможно, то запрос, который должен выполнять индивидуальный поиск для заполнения «дельты» для каждой строки, вероятно, пока приемлем. Мне просто придется запускать его много раз с частями данных.

Понравилась статья? Поделиться с друзьями:
IT Шеф
Комментарии: 1
  1. azoundria

    Вы бы просто использовали 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

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: