sql - 尝试优化特定的 SQL 查询

Question

我在 PostgreSQL 中使用这个 SQL 查询:

select *
from (select  row_number()
      over (order by oid1, oid2, oid3)
      as row_number, *
      from snmpProperties)
as toto
where toto.row_number=(select row_number
                       from (select  row_number()
                             over (order by oid1, oid2, oid3)
                             as row_number, *
                             from snmpProperties)
                       as titi
                       where titi.oid1='4'
                             AND titi.oid2='1'
                             AND titi.oid3='')+1;

但它使用相同的 SELECT 两次:

select  row_number()
    over (order by oid1, oid2, oid3)
    as row_number, *
    from snmpProperties

是否可以分解我一次使用此选择的请求？

Answer 1

select      *

from       (select      lag ((oid1,oid2,oid3)) over (order by oid1, oid2, oid3) = ('4','1','')  as is_requested_row
                       ,s.*

            from        snmpProperties as s
            ) as s

where       is_requested_row
;

加法方案，只是为了演示也可以根据row_number

select      *

from       (select      min (case when (oid1,oid2,oid3) = ('4','1','') then rn end) over ()   base_rn
                       ,s.*

            from       (select      row_number() over (order by oid1, oid2, oid3) as rn, s.*
                        from        snmpProperties as s
                        ) as s
            ) as s

where       rn = base_rn + 1
;

说明

1)使用 LAG 窗口函数，每条记录查看其前一条记录的 (oid1,oid2,oid3) 向量(当排序为 oid1,oid2,oid3 时)并将其与 ('4','1 ','').当比较为真时，表示我们站在请求的记录上，即oid1='4', oid2='1' 和oid3=''的记录后面的记录。

2)使用 row_number 窗口函数，我们将行号赋予按 oid1、oid2、oid3 排序的记录。使用 min 窗口函数，我们将 oid1、oid2 和 oid3 值为“4”、“1”和“”的行的 row_number 放入集合中的每一行中。我们取其 row_number 等于 row_number + 1 的行