Блог

У меня проблемы с вычислением времени между строками. Хотя я смог написать SQL, который дает правильное время.

 CREATE TABLE tab
    (`id` int,`station` varchar(10), `user` int, `create_at` datetime, `shift_end` datetime);
    
INSERT INTO tab
    (`id`, `create_at`, `user`, `station`, `shift_end`)
VALUES
    ( 1 , '2020-12-10 14:14:04',1,'S1', '2020-12-10 16:00:00'),
    ( 2 , '2020-12-10 14:14:09',1,'S2', '2020-12-10 16:00:00'),
    ( 3 , '2020-12-10 14:14:05',2,'S1', '2020-12-10 16:00:00'),
    ( 4 , '2020-12-10 14:14:15',2,'S2', '2020-12-10 16:00:00'),
    ( 5 , '2020-12-10 14:28:52',1,'S3', '2020-12-10 16:00:00');

SELECT
  id,
  create_at,
  user,
  station,
  shift_end,
  TIMESTAMPDIFF(SECOND,
    (SELECT MAX(create_at) FROM tab WHERE create_at < t.create_at and user = t.user),
    create_at
  ) AS x
FROM tab as t order by user, create_at
 

Но время отображается в следующей строке. И здесь у меня вопрос, возможно ли вообще получить такой результат?

 id  create_at           user   station  shift_end            x
1   2020-12-10 14:14:04 1      S1       2020-12-10 16:00:00  5
2   2020-12-10 14:14:09 1      S2       2020-12-10 16:00:00  883
5   2020-12-10 14:28:52 1      S3       2020-12-10 16:00:00  NULL
3   2020-12-10 14:14:05 2      S1       2020-12-10 16:00:00  10
4   2020-12-10 14:14:15 2      S2       2020-12-10 16:00:00  NULL
 

Есть ли что-то еще, возможно ли вычислить время до значения в столбце shift_date в последних строках для данного пользователя?

 id  create_at            user   station  shift_end               x
5   2020-12-10 14:28:52  1      S3       2020-12-10 16:00:00     5468 (time between create_at AND shift_end)
 

Используйте LEAD() функцию окна:

 SELECT
  id,
  create_at,
  user,
  station,
  shift_end,
  TIMESTAMPDIFF(
    SECOND,
    create_at,
    LEAD(create_at) OVER (PARTITION BY user ORDER BY create_at)
  ) AS x
FROM tab
ORDER BY user, create_at
 

Смотрите демонстрацию.
Результаты:

 > id | create_at           | user | station | shift_end           |    x
> -: | :------------------ | ---: | :------ | :------------------ | ---:
>  1 | 2020-12-10 14:14:04 |    1 | S1      | 2020-12-10 16:00:00 |    5
>  2 | 2020-12-10 14:14:09 |    1 | S2      | 2020-12-10 16:00:00 |  883
>  5 | 2020-12-10 14:28:52 |    1 | S3      | 2020-12-10 16:00:00 | null
>  3 | 2020-12-10 14:14:05 |    2 | S1      | 2020-12-10 16:00:00 |   10
>  4 | 2020-12-10 14:14:15 |    2 | S2      | 2020-12-10 16:00:00 | null