Разница во времени между последовательными строками на человека

У меня есть некоторые данные, которые (вообще говоря) состоят из следующих полей:

Person  TaskID   Start_time                      End_time
Alpha   1       'Wed, 18 Oct 2017 10:10:03 GMT' 'Wed. 18 Oct 2017 10:10:36 GMT'
Alpha   2       'Wed, 18 Oct 2017 10:11:16 GMT' 'Wed, 18 Oct 2017 10:11:28 GMT'
Beta    1       'Wed, 18 Oct 2017 10:12:03 GMT' 'Wed, 18 Oct 2017 10:12:49 GMT'
Alpha   3       'Wed, 18 Oct 2017 10:12:03 GMT' 'Wed, 18 Oct 2017 10:13:13 GMT'
Gamma   1       'Fri, 27 Oct 2017 22:57:12 GMT' 'Sat, 28 Oct 2017 02:00:54 GMT'
Beta    2       'Wed, 18 Oct 2017 10:13:40 GMT' 'Wed, 18 Oct 2017 10:14:03 GMT'

Для этих данных мой требуемый вывод выглядит примерно так:

Person  TaskID Time_between_attempts
Alpha   1      NULL      ['Wed, 18 Oct 2017 10:10:03 GMT' - NULL]
Alpha   2      0:00:40   ['Wed, 18 Oct 2017 10:11:16 GMT' -'Wed, 18 Oct 2017 10:10:36 GMT']
Beta    1      NULL      ['Wed, 18 Oct 2017 10:12:03 GMT' - NULL]
Alpha   3      0:00:35   ['Wed, 18 Oct 2017 10:12:03 GMT' -'Wed, 18 Oct 2017 10:11:28 GMT']
Gamma   1      NULL      ['Fri, 27 Oct 2017 22:57:12 GMT' - NULL]
Beta    2      0:00:51   ['Wed, 18 Oct 2017 10:13:40 GMT' -'Wed, 18 Oct 2017 10:12:49 GMT']

Мои требования следующие:

а. Для данного человека (Альфа, Бета или Гамма) первое вхождение переменной 'time_between_attempts' будет равно нулю/NULL - в примере я показал его как NULL.

б. Второй (и последующие) раз появление того же человека будет иметь не NULL или ненулевое значение «time_between_attempts». Эта переменная вычисляется путем получения разницы между временем окончания предыдущей задачи и временем начала следующей задачи.

В связи с этим у меня следующий вопрос:

  1. Как написать сценарий SQL, который поможет мне добиться желаемого результата?

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

'q:1392763916495:441',
'q:1392763916495:436'

Любые советы по этому поводу будут очень признательны.


mysql sql split date-difference strsplit
person Sandy    schedule 14.10.2019    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Использование метода self Join().

    SELECT a.person, 
            a.taskid, 
            TIMEDIFF (DATE_FORMAT(STR_TO_DATE(a.Start_time, '%a, %d %b %Y %H:%i:%s'), '%Y-%m-%d %H:%i:%s') ,DATE_FORMAT(STR_TO_DATE(b.End_time, '%a, %d %b %Y %H:%i:%s'), '%Y-%m-%d %H:%i:%s') ) as Time_between_attempts,
            a.Start_time,
            b.End_time

        FROM   test a 
            LEFT JOIN test b 
                    ON a.person = b.person 
                        AND a.taskid = b.taskid + 1 
        ORDER  BY 1, 2;

Но это будет игнорировать часовой пояс.

person Sathish G    schedule 14.10.2019
comment
Спасибо за публикацию решения, я немного обновил свой вопрос, не могли бы вы помочь мне в этом? - person Sandy; 14.10.2019
comment
Не могли бы вы уточнить, как работает этот код? Мне трудно понять часть соединения, и где/почему вы заказали 1,2? - person Sandy; 15.10.2019

arrow_upward
1
arrow_downward

Это отвечает на исходную версию вопроса.

Вы можете использовать lag() и timestampdiff() для расчета. Предполагая, что ваше значение является реальной датой/временем или отметкой времени, вы можете легко рассчитать значение в секундах:

select t.*,
       timestampdiff(start_time,
                     lag(end_time) over (partition by person_id order by start_time)
                     seconds
                    )
from t;

Если значения хранятся в виде строки, исправьте данные! А пока вы можете использовать str_to_date() в функции.

Чтобы получить это как значение времени:

select t.*,
       (time(0) +
        interval timestampdiff(start_time,
                               lag(end_time) over (partition by person_id order by start_time)
                               seconds
                              ) second
       )
from t;
person Gordon Linoff    schedule 14.10.2019
comment
@Gordon Гордон Я не могу выполнить этот запрос. Появляется сообщение об ошибке: Select не является допустимым вводом в этой позиции для этой версии сервера, ожидая: '(', WITH..... Я работаю в mysql workbench 8.0. - person Sandy; 15.10.2019
comment
@Садиаз. . . В этом ответе есть только один select, и он находится в начале запроса. Не должно быть проблем. - person Gordon Linoff; 15.10.2019
comment
@GordonLinoff, по какой-то причине код не работает. Я получаю следующее сообщение об ошибке: Код ошибки: 1064. У вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, на предмет правильного синтаксиса для использования рядом с «Start_Time, lag(End_Time) over (partition by userId order by Start_Time)» в строке 2. Фактический код: select timestampdiff(Start_Time, lag(End_Time ) over (раздел по userId по порядку Start_Time) секунды ) from int_stellar t; - person Sandy; 16.10.2019
comment
@Садиаз. . . Какую версию MySQL вы используете? Версия верстака значения не имеет. - person Gordon Linoff; 16.10.2019
comment
Это сервер MySQL 5.7. - person Sandy; 17.10.2019