使用SQL获取MySQL中两行之间的增量时间

Question

我有一个查询

select 
event_time, argument 
from 
mysql.general_log 
where 
argument <> 'SHOW WARNINGS' 
order by 
event_time;

我想知道是否可以添加一列，说明每行之间的时间差是多少？

类似于获取SQL上的行之间的增量和两行之间的时间差-mysql(使用PHP(通过在mysql中仅使用SQL(不使用PHP(

Answer 1

您可以使用lag():

select event_time, argument,
timestampdiff(second, lag(event_time) over (order by event_time), event_time) as diff
from mysql.general_log
where argument <> 'SHOW WARNINGS'
order by event_time ;

在旧版本的MySQL中，您可以使用：

select gl.event_time, gl.argument,
timestampdiff(second,
(select gl2.event_time
from mysql.general_log gl2
where gl2.event_time < gl.event_time
order by gl2.event_time desc
limit 1
) as diff
from mysql.general_log gl
where gl.argument <> 'SHOW WARNINGS'
order by gl.event_time ;

Answer 2

标记为重复，但我将把答案放在这里，以防有人想对mysql.general_log 进行类似的分析

select argument, event_time, timestampdiff( MICROSECOND ,prevdatenew, event_time) as diff
from (
select argument, event_time, @prevDateNew as prevdatenew, 
@prevDateNew := event_time
from mysql.general_log
where argument <> 'SHOW WARNINGS'
order by event_time 
) t1
where argument not like 'SET auto%' and argument not like '/*%'
-- order by diff desc