一个mysql表的一列中相邻行之间的时间差

Question

我有一个包含100000行的表，具有以下结构：

+------+---------------------+-----------+
| id   | timestamp           | eventType |
+------+---------------------+-----------+
|   12 | 2015-07-01 16:45:47 |      3001 |
|  103 | 2015-07-10 19:30:14 |      3001 |
| 1174 | 2015-09-03 12:57:08 |      3001 |
+------+---------------------+-----------+

对于每一行，我想计算这一行和前一行的时间戳之间的天数。正如你所看到的，id是不连续的，这个表包含不同的事件，我只想比较一个特定事件的时间戳。

我知道，对于两个数据的比较，可以使用DATEDIFF，我会用一个查询来定义这两行，该查询根据特定的id来选择行。但由于我有1000多行，我正在寻找一种方法，以某种方式在整个表中循环。

不幸的是，我的sql知识有限，搜索没有发现一个例子，离我的问题足够近，我会继续在那里学习。如果有任何提示，我将不胜感激。

Answer 1

如果您运行的是MySQL 8.0，您可以只使用lag()。假设你想要以秒为单位的差异：

select t.*,
timestampdiff(
second, 
lag(timestamp) over(partition by eventtype order by id),
timestamp
) diff
from mytable t

在早期版本中，一种替代方案是相关子查询：

select t.*,
timestampdiff(
second, 
(select timestamp from mytable t1 where t1.eventtype = t.eventtype and t1.id < t.id order by t1.id desc limit 1),
timestamp
) diff
from mytable t