这可以通过使用窗口函数lag()来获得当前行的前一行，然后使用条件where code = 9 and lag_code = 8进行减法以获得8和9之间的经过时间来实现：

with cte as (
  select *, 
            lag(timestamp) over (partition by deviceid order by timestamp) as lag_timestamp,
            lag(code) over (partition by deviceid order by timestamp) as lag_code
  from mytable
  where code between 8 and 9
)
select deviceId, timestamp - lag_timestamp as elapsed_time
from cte
where code = 9 and lag_code = 8

结果：
| 设备ID|经过时间|
| - -----|- -----|
| 设备1| 2|
| 设备1| 1|
| 设备2|十个|
| 设备2|三十|
Demo here

使用这个简单的查询来生成您需要的结果（和test it on fiddle）：

select a.deviceId, (b.timestamp - a.timestamp) as elapsed_time
from logs a join logs b
on b.deviceid = a.deviceid
and b.id = (select min(id)
            from logs b
            where b.deviceid = a.deviceid
            and b.id > a.id
            and code = 9)
where a.code = 8;

并且它给出：

deviceid | elapsed_time 
----------+--------------
 device1  |            2
 device1  |            1
 device2  |           10
 device2  |           30