sql - 查询特定 id 时按顺序查找上一行/下一行

Question

我有一个表格如(简化到极致以使其更清晰)

create table mytable (
  id integer not null,
  owner text not null,
  order_field_1 integer not null,
  order_field_2 integer not null
)

每次从数据库中获取一行时，我都会尝试获取下一个和上一个元素的 ID，以允许导航。行不是按 id 排序，而是按 ORDER BY order_field_1 DESC, order_field_2 DESC。

当获取所有者的最后条目时，我可以毫无问题地使用窗口和超前/滞后找到我想要的内容

SELECT
  id,
  owner,
  lag(id) over w AS previous_id,
  lead(id) over w AS next_id
FROM
  mytable
WHERE
  owner = 'someuser'
WINDOW w AS (
  ORDER BY order_field_1 DESC, 
  order_field_2 DESC
)
ORDER BY 
  order_field_1 DESC, 
  order_field_2 DESC
LIMIT
  5

这是凭内存写的，但这是它的要点，而且效果很好。

我的问题是当我想使用所有者和 ID 获取特定行时，但我仍然想找到上一个和下一个 ID，我不能再使用窗口函数，因为 where 只返回一行，并且我当前执行子查询以获取两个导航 id 的解决方案在性能方面不是很好

例如(我只放上一个id，因为下一个id是一样的)

SELECT
  m1.id,
  m1.owner,
  (
    SELECT 
      m2.id 
    FROM 
      mytable m2 
    WHERE 
      m2.owner = m1.owner 
      AND m2.id != m1.id 
      AND (
        m2.order_field_1 < m1.order_field_1 
        OR (
          m2.order_field_1 = m1.order_field_1 
          AND m2.order_field_2 <= m1.order_field_2
        ) 
      ORDER BY 
        m2.order_field_1 DESC, 
        m2.order_field_2 DESC
      LIMIT
        1
  ) AS previous_id
FROM
  mytable m1
WHERE
  owner = 'someuser'
  AND id = 12345

所以我选择了我的行，然后从同一用户中选择了第一行，具有不同的 ID，即具有较低的 order_field_1 或相同但较低的 order_field_2。

这不是很有效，而且我的表现很差，我想知道是否有人对我如何改进它有任何想法？

示例数据集:

id |    owner | order_field_1 | order_field_2
 1 | someuser |             4 |             2
 2 | someuser |             2 |             8
 3 | someuser |             4 |             3
 4 | someuser |             3 |             2
 5 | someuser |             4 |             6
 6 | someuser |             4 |             5

已订购:

id |    owner | order_field_1 | order_field_2
 5 | someuser |             4 |             6
 6 | someuser |             4 |             5
 3 | someuser |             4 |             3
 1 | someuser |             4 |             2
 4 | someuser |             3 |             2
 2 | someuser |             2 |             8

如果我选择 owner = 'someuser' 和 id = 3，previous_id 应该是 1，next_id 应该是 6。

如果我选择 owner = 'someuser' 和 id = 1，previous_id 应该是 4，next_id 应该是 3。

在此先感谢您的帮助

Answer 1

使用窗口函数和 CTE

WHERE owner = 'someuser' 便宜很多已经在 CTE 中:

WITH t AS (
    SELECT id
          ,owner
          ,lag(id)  over w AS previous_id
          ,lead(id) over w AS next_id
    FROM   mytable
    WHERE  owner = 'someuser'
    WINDOW w AS (ORDER BY order_field_1 DESC, order_field_2 DESC)
    )
SELECT *
FROM   t
WHERE  id = 3

此外，由于您只选择了一行，因此不需要 ORDER BY在决赛中SELECT .

__

带有子查询的老派

它相当难看，但如果每个 owner 有 很多 行，它可能会更快.你必须测试......

SELECT id
     , owner
     ,(SELECT id
       FROM   tbl p
       WHERE  p.owner = t.owner -- same owner
       AND    p.id   <> t.id    -- different id
       AND    p.order_field_1 <= t.order_field_1
       AND    p.order_field_2 <= t.order_field_2
       ORDER  BY order_field_1 DESC
               , order_field_2 DESC
       LIMIT  1) AS previous_id

     ,(SELECT id
       FROM   tbl n
       WHERE  n.owner = t.owner
       AND    n.id   <> t.id
       AND    n.order_field_1 >= t.order_field_1
       AND    n.order_field_2 >= t.order_field_2
       ORDER  BY order_field_1
               , order_field_2
       LIMIT  1) AS next_id
FROM   tbl t
WHERE  owner = 'someuser'
AND    id = 3

这个也适用于旧版本的 PostgreSQL。
当然，性能的关键是适当的索引。