sql - 选择锯齿模式中的最大值(局部最大值)

Question

我在 postgres 中有一个名为“ts”的表，如下所示:

dev -- numeric device id
ts -- Unix epoch timestamp
key -- string (only interested in records where key is 'u')
val -- int representing uptime in ms

我正在将进程正常运行时间记录到该表中，大约每 30 秒记录一次。结果是一个具有不断增加的 val 的表，它会在进程重新启动时定期重置为 0，从而创建类似锯齿的模式。请注意，记录的实际值可能不是 0，因为记录可能不会在重新启动时立即发生。

我想做的是通过定期剔除不代表预重置最大正常运行时间的值来稍微清理表格。多个设备混合在同一个表中，因此最大正常运行时间必须按设备分组。

例子:

dev ts      key  val
1   100000  'u'  50      -- boring
1   130100  'u'  30050   -- delete this
1   160100  'u'  60050   -- >> keep this one
1   190200  'u'  100     -- this record dies
1   220200  'u'  30100   -- >> keep this one too
1   250200  'u'  300    

我想要一个查询来挑选出除了我上面标记的记录之外的所有记录，这些记录是无趣的并且可以删除。

剔除将在批处理过程中定期运行。

Answer 1

如果只想保持局部最大值，可以使用lead()和lag():

select t.*
from (select t.*,
             lead(val) over (partition by dev order by ts) as next_val,
             lag(val) over (partition by dev order by ts) as prev_val
      from t
      where key = 'u'
     ) t
where val > prev_val and val > next_val;