SQL计算一行与下一行之间的时间

Question

我有一个表格点击

click_id int
created datetime

示例数据

1 2021-03-01 12:15:17
2 2021-03-01 12:15:21
3 2021-03-01 12:17:21
4 2021-03-01 13:17:59

我想选择所有点击，包括它和之后最快的点击之间的时间差(以秒为单位)

结果需要:

1 - 2021-03-01 12:15:17 - 4
2 - 2021-03-01 12:15:21 - 120
3 - 2021-03-01 12:17:21 - 3602
4 - 2021-03-01 13:17:23 - null

这可以用MySQL实现吗?

Answer 1

在MySQL 8+上，我们可以使用LEAD和TIMESTAMPDIFF:

SELECT
click_id,
created,
TIMESTAMPDIFF(SECOND, created, LEAD(created) OVER (ORDER BY created)) AS diff_sec
FROM yourTable
ORDER BY created;