sql - 如何优化显示 2 个相邻行且值时间戳已关闭的 SQL 查询？

Question

我需要为 postgresql 创建一个查询，以显示在 100 毫秒内发出的请求。 postgresql表请求有请求id(request_id)和时间戳(create_stamp)。

下面的 Mu 查询有效但非常慢。

select
    b1.create_stamp as st1,
    b2.create_stamp as st2,
    b1.request_id as b1_id,
    b2.request_id as b2_id,
    Cast(date_part('milliseconds', b1.create_stamp) as Integer) as msi1,
    Cast(date_part('milliseconds', b2.create_stamp) as Integer) as msi2,
    (date_part('milliseconds', b1.create_stamp) - date_part('milliseconds', b2.create_stamp)) as diff
from
    request b1, request b2
where
    (b1.request_id - b2.request_id) = 1
    and (date_part('milliseconds', b1.create_stamp) - date_part('milliseconds', b2.create_stamp)) < 100
    and (date_part('milliseconds', b1.create_stamp) - date_part('milliseconds', b2.create_stamp)) > 0
order by b1.request_id;

Answer 1

您想使用 lag()/lead() 来检查上一个和下一个值。这应该比自连接快得多:

select r.*
from (select r.*,
             lag(create_stamp) over (order by request_id) as prevcs,
             lead(create_stamp) over (order by request_id) as nextcs
      from request r
     ) r
where (date_part('milliseconds', r.create_stamp) - date_part('milliseconds', prevcs)) < 100 and
       date_part('milliseconds', r.create_stamp) - date_part('milliseconds', prevcs)) > 0
      ) or
     (date_part('milliseconds', nextcs) - date_part('milliseconds', r.create_stamp)) < 100 and
       date_part('milliseconds', nextcs) - date_part('milliseconds', r.create_stamp)) > 0
      )

同时使用 lag() 和 lead() 的原因是要返回一对中的两行。如果第一个这样的行就足够了，那么您只需要一个函数(和一半的 where 子句)。

我注意到您的原始查询查看相邻的请求 ID。这是你真正想要的吗？或者您想通过 create_stamp 对行进行排序？如果是这样，请更改按函数划分的 order by 参数。