Как выбрать соседние строки для произвольной строки (в sql или postgresql)?

Я хочу выбрать несколько строк на основе определенных критериев, а затем взять одну запись из этого набора и 5 строк перед ней и после нее.

Теперь я могу сделать это численно, если в таблице есть первичный ключ (например, первичные ключи, число которых меньше 5, чем целевая строка строки и 5 больше, чем целевой ключ строки).

Итак, выберите строку с первичным ключом 7 и соседними строками:

select primary_key from table where primary_key > (7-5) order by primary_key limit 11;

2
3
4
5
6
-=7=-
8
9
10
11
12

Но если я сначала выберу только определенные строки, я потеряю этот числовой метод использования первичных ключей (и это предполагало, что у ключей не было никаких пробелов в их порядке), и нужен другой способ получить ближайший строк до и после определенной целевой строки.

Вывод первичного ключа такого выбора может выглядеть более случайным и, следовательно, менее восприимчивым к математическому размещению (поскольку некоторые результаты будут отфильтрованы, например, с помощью where active=1):

select primary_key from table where primary_key > (34-5) 
    order by primary_key where active=1 limit 11;

30
-=34=-
80
83
100
113
125
126
127
128
129

Обратите внимание, что из-за пробелов в первичных ключах, вызванных примером, где условие (например, becaseu есть много неактивных элементов), я больше не получаю ближайшие 5 выше и 5 ниже, вместо этого я получаю ближайший 1 ниже и ближайший 9 выше.

Ответ 1

Там много способов сделать это, если вы запускаете два запроса с языком программирования, но здесь один способ сделать это в одном запросе SQL:

(SELECT * FROM table WHERE id >= 34 AND active = 1 ORDER BY id ASC LIMIT 6)
UNION
(SELECT * FROM table WHERE id < 34 AND active = 1 ORDER BY id DESC LIMIT 5)
ORDER BY id ASC

Это вернет 5 строк выше, целевую строку и 5 строк ниже.

Ответ 2

Вот еще один способ сделать это с аналитическими функциями, ведущими и отстающими. Было бы неплохо, если бы мы могли использовать аналитические функции в предложении WHERE. Поэтому вместо этого вам нужно использовать подзапросы или CTE. Вот пример, который будет работать с базой данных образца pagila.

WITH base AS (
    SELECT lag(customer_id, 5) OVER (ORDER BY customer_id) lag, 
      lead(customer_id, 5) OVER (ORDER BY customer_id) lead, 
      c.*
    FROM customer c
    WHERE c.active = 1
    AND c.last_name LIKE 'B%'
) 
SELECT base.* FROM base 
JOIN (
  -- Select the center row, coalesce so it still works if there aren't 
  -- 5 rows in front or behind
  SELECT COALESCE(lag, 0) AS lag, COALESCE(lead, 99999) AS lead 
  FROM base WHERE customer_id = 280
) sub ON base.customer_id BETWEEN sub.lag AND sub.lead

Проблема с решением sgriffinusa заключается в том, что вы не знаете, какая строка row_number окажется в вашей центральной строке. Он предположил, что это будет строка 30.

Ответ 3

Для аналогичного запроса я использую аналитические функции без CTE. Что-то вроде:

select ..., LEAD(gm.id) OVER (ORDER BY Cit DESC) as leadId, LEAD(gm.id, 2) OVER (ORDER BY Cit DESC) as leadId2, LAG(gm.id) OVER (ORDER BY Cit DESC) as lagId, LAG(gm.id, 2) OVER (ORDER BY Cit DESC) as lagId2 ... where id = 25912 or leadId = 25912 or leadId2 = 25912 or lagId = 25912 or lagId2 = 25912

такой запрос работает быстрее для меня, чем CTE с присоединением (ответ от Scott Bailey). Но, конечно, менее элегантный

Ответ 4

Вы можете сделать это, используя row_number() (доступно с 8.4). Это может быть не правильный синтаксис (не знакомый с postgresql), но, надеюсь, идея будет проиллюстрирована:

SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY primary_key) AS r, *
      FROM table
      WHERE active=1) t
WHERE 25 < r and r < 35

Это создаст первый столбец, имеющий порядковые номера. Вы можете использовать это, чтобы идентифицировать одну строку и строки выше и ниже.

Ответ 5

Если вы хотите сделать это "реляционно чистым" способом, вы можете написать запрос, который отсортировал и пронумеровал строки. Как:

select (
  select count(*) from employees b
  where b.name < a.name
) as idx, name
from employees a
order by name

Затем используйте это как общее табличное выражение. Напишите выбор, который фильтрует его до интересующих вас строк, затем присоединяйте его к себе, используя критерий, что индекс правой копии таблицы не больше, чем k больше или меньше индекса строка слева. Проектируйте только строки справа. Как:

with numbered_emps as (
  select (
    select count(*)
    from employees b
    where b.name < a.name
  ) as idx, name
  from employees a
  order by name
)
select b.*
from numbered_emps a, numbered_emps b
where a.name like '% Smith' -- this is your main selection criterion
and ((b.idx - a.idx) between -5 and 5) -- this is your adjacency fuzzy-join criterion

Что может быть проще?

Я бы предположил, что решения на основе числа строк будут быстрее.